Locking Mechanisms

To guarantee the database consistency and to avoid any shared data problems in a multi-user environment, the model for locking is strongly connected 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 to lock the object when it is changed on the client side, because the transaction applying the changes to the database may occur a considerable time later.

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 still is 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 alternate behavior is unacceptable in interactive applications, but can be used in server processes.

Contents

• Implementation of OBJVERSION
• Algorithm

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 IFS/Design as a common model property. This property will affect the server code in how symbol OBJVERSION is defined in the top of the APY-file. There are many different ways of implementing objversion, of which two most common ones (supported by IFS/Design) are described below:

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

The solution with time stamps or sequences is quite easy to understand. Just add an extra "private" column in the base table and map that column to the column OBJVERSION in the view definition. To activate the objversion in the methods Insert___ and Update___, this column ROWVERSION should be set to SYSDATE (timestamp) or to 1 and rowversion+1 (sequences).

It is important to state that the implementation of the lock method and the OBJVERSION are private for the specific logical unit and may therefore vary for different logical units within a component.

Algorithm

The result of the SELECT FOR UPDATE discussed above is shown below:

SELECT 1
FROM demo_customer_tab
WHERE &OBJID = objid_
AND &OBJVERSION = objversion_
FOR UPDATE NOWAIT;

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 must be determined by another fetch from the database, with the condition objid only. After this statement, the procedure return to the call stack, and the application continues. If any problem occurred, an application error is raised to let the client know about the locking-problems. For details, please see the section for public interfaces to system service Error_SYS.

Example: Development_Project_API.Lock___

PROCEDURE Lock___ (
   objid_ IN VARCHAR2,
   objversion_ IN VARCHAR2 )
IS
   row_changed EXCEPTION;
   row_deleted EXCEPTION;
   row_locked EXCEPTION;
   PRAGMA exception_init(row_locked, -0054);
   dummy_ NUMBER;
   CURSOR lock_control IS
      SELECT 1
      FROM development_project_tab
      WHERE &OBJID = objid_
      AND &OBJVERSION = objversion_
      FOR UPDATE NOWAIT;
   CURSOR exist_control IS
      SELECT 1
      FROM development_project_tab
      WHERE &OBJID = objid_;
BEGIN
   OPEN lock_control;
   FETCH lock_control INTO dummy_;
   IF (lock_control%FOUND) THEN
       CLOSE lock_control;
       RETURN;
   END IF;
   CLOSE lock_control;
   OPEN exist_control;
   FETCH exist_control INTO dummy_;
   IF (exist_control%FOUND) THEN
      CLOSE exist_control;
      RAISE row_changed;
   ELSE
      CLOSE exist_control;
      RAISE row_deleted;
   END IF;
EXCEPTION
   WHEN row_locked THEN
      Error_SYS.Record_Locked(lu_name_);
   WHEN row_changed THEN
      Error_SYS.Record_Modified(lu_name_);
   WHEN row_deleted THEN
      Error_SYS.Record_Removed(lu_name_);
END Lock___;