Skip to content

Locking

To guarantee the database consistency and to avoid any shared data problems in a multi-user environment, the model for locking is intrinsically linked to the Oracle server.

To prevent inactive clients from locking parts of the database, the lock method should be activated as close as possible to the UPDATE- or DELETE-statements. The problem in the client is that it is not interesting in locking the object when it is changed on the client side, because the transaction applying the changes to the database may occur at a considerably later time.

The solution is to implement the LOCK-statement (SELECT FOR UPDATE) by using objversion in the WHERE-clause of the SELECT-statement; to check if the record that is changed is still untouched in the database. This method guarantees that locking is made only if the record in the database is exactly the same that has been fetched to the client. Otherwise an exception will be raised. The SQL-statement will look like below and is placed in the implementation method Lock___.

Note: Using the NOWAIT keyword will result in an immediate answer from the database, giving an error message if the row could not be locked. It is possible to change the default behavior and omit the NOWAIT. This would cause a process that tries to lock an already locked row to "freeze" and wait until the previous lock is released. This alternative behavior is unacceptable in interactive applications, but it can be used in server processes.

Implementation of OBJVERSION

The code delivered by the server templates is independent of how to implement the physical object version for a logical unit. The implementation is decided in the model. The default behavior is a timestamp solution, but this could be changed by code generation properties. There are many different ways of implementing objversion, of which two most common ones are described below:

  • Timestamp solution based on sysdate in the database table. Physically this means a DATE column.
  • Number started from 1 used as an ordinary counter. Physically this means a NUMBER column.

The table column is called ROWVERSION and the view column is called OBJVERSION.

Algorithm

The SQL statement that is used for locking is shown below:

SELECT *  
      INTO  rec_  
      FROM  batch_queue_tab  
      WHERE rowid = objid_  
      AND    to_char(rowversion,'YYYYMMDDHH24MISS') = objversion_  
      FOR UPDATE NOWAIT;
 The table below shows the various scenarios and results of the locking SQL statement.
Result Action
The object is locked without any problem continue execution
The object is already locked Msg: Locked by another user
The object is removed from the database Msg: Deleted by another user
The object is changed in some of its attributes Msg: Changed by another user

If the object is missing, a problem will occur, due to the fact that we do not know whether the object is deleted or the attributes are changed since the record was fetched to the client. This should be determined by another fetch command from the database, with the condition objid only. After this statement, the procedure returns to the call stack, and the application continues. If any problem has occurred, then an application error is raised to let the client know about the locking-problems. For further details, please see the section for public interfaces to system service Error_SYS.

Example: Fnd_User_API.Lock_By_Id__