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.