If a database user wants to update table rows, the rows concerned must be locked for other users for consistency reasons. Only in exceptional cases, the concerned rows need not be locked for reading. ADABAS can set read or write locks. Read-locked table rows (SELECT) can be read but not altered by other users. Write-locked table rows (INSERT, UPDATE, DELETE), on the other hand, can be neither read nor updated by other users. Locks are normally released at the end of a transaction (COMMIT WORK, ROLLBACK WORK).
Explicit and Implicit Locks
Write and read locks can be set for tables as well as for table rows. When locks are to be set implicitly, ADABAS places single row locks in most cases. When the number of single row locks held by one user on one table exceeds a given value, ADABAS tries to change these locks into a lock on the entire table. The threshold value from which a table lock is requested is indirectly determined by means of an installation parameter. (For more details, see the CONTROL manual.)
The Three ADABAS Lock Modes:
SHARE defines a read lock for the specified objects.
EXCLUSIVE defines a write lock for the specified objects.
OPTIMISTIC defines an optimistic lock for the row.
Setting these locks enables the controlled and protected execution of evaluations or modifications of data in an application program.
For example, if a data object is only read-locked by a user, it may be read but not modified by any other user.
On the other hand, if a data object is write-locked by a user, no other user can read or modify this data object.
If an optimistic lock is set for a row, any other user can read and even modify this row. But when the owner of the optimistic lock attempts to modify the row thus locked, a check is made. If the row was modified by another user between the setting of the lock and the attempt to modify the row, the modification of the lock holder is rejected with an error code. To change the row, the lock holder has to read it again and to set a lock, if necessary. If no modifications were made by other users in the meantime, the modifications of the optimistic lock holder are performed. Thus it can be ensured that between reading and modifying the row, no modification was made that could be lost by the new modification. If a read- or write-lock is set for a row optimistically locked by the same user, then this row is not locked optimistically any more. Then no check is made when the user tries to change the row.
By default, read locks are set implicitly by the system. A non-default lock mode can be determined via the isolation level when opening a database session.
INSERT, UPDATE, or DELETE statements write-lock the data objects concerned in any isolation level. Therefore the following description of the different isolation levels only concerns the behavior for SELECT statements.
Isolation Level
A SELECT statement sets a read lock for a row of a table. With the next read operation in this table, this lock is released and replaced by the read lock for the newly read row. This means only one read lock is held for a table row per table.
This is very helpful in interactive mode, since an interactive user does not need to take care of the setting of locks. In this case, the isolation level 1 is concerned which in SQLMODE ADABAS can also be denoted as isolation level 10. Except for the SQLMODE ANSI, this isolation level is also used when no isolation level has been specified in the connect statement.
The statement COMMIT WORK terminates the transaction and releases all the locks implicitly requested.
In OLTP application programs, it is better to request locks explicitly. It is therefore recommended to use the isolation level 0 and optimistic locking for OLTP application programs. For this purpose, the isolation level must be set to 0 in the CONNECT statement. Subsequently, the LOCK statement can be used to lock any rows (via the key) or tables (via the name). Optimistic locks can only be used for rows.
The isolation level 0 differs from all the other isolation levels by not implicitly setting the read locks when reading; these must be requested explicitly.
If the isolation level is set to 15 in a CONNECT statement, one read lock is set to the read table row per table for a SELECT statement. With the next read operation in the same table, this lock is released and replaced by the lock on the newly read row. This corresponds to isolation level 1. Moreover, the complete table is read-locked and only released at the end of the statement when a statement without a key specification is processed. If the result table is not internally generated for a SELECT statement, the lock is only released for the implicit or explicit CLOSE.
Isolation level 15 is only valid for SQLMODE ADABAS.
For isolation level 2, which in SQLMODE ADABAS can also be denoted as isolation level 20, processing a statement without a key specification read-locks the complete table which is only released at the end of the statement.When table rows are read, they are read-locked row by row so that various table rows are read-locked during a SELECT. The locks set to individual table rows remain valid up to the end of the transaction. If the result table is not internally generated for a SELECT statement, the lock is only released for the implicit or explicit CLOSE.
Isolation level 3, which in SQLMODE ADABAS can also be denoted as isolation level 30, has the effect that the complete table is read-locked for a statement without a key specification. This lock is kept up to the end of the transaction. This means that repeating the same SELECT statement within the same transaction always yields the same result. This isolation level is used in SQLMODE ANSI, if no isolation level has been specified in the CONNECT.
Changing the Isolation Level
In SQLMODE ADABAS, the isolation level chosen for the CONNECT statement can be changed for single SELECT statements. This can be done by means of the specification 'WITH LOCK ISOLATION LEVEL x', where x stands for one of the isolation levels described above. The specified level x can be used for the SELECT statement concerned, regardless of the isolation level selected in the CONNECT statement. The following SQL statements use the isolation level specified for the CONNECT again.
Multi-User Mode
Although selecting the isolation level and using an appropriate transaction size the critical fields where collisions might occur, it can happen that a data object is requested by several users. A user who attempts to lock an object already locked is put in wait state by default, until the object is available again or the maximum waiting time has been exceeded (WAIT option). For the description of the maximum waiting time, see Section Timeouts.
If the option NOWAIT is specified in the LOCK statement or in the option WITH LOCK of a SELECT statement, the release of the locks is not waited for, but a message is returned. If no collision occurred, the desired lock is set.
Thus, waiting times are avoided if a user can execute other work. The option NOWAIT can, of course, be used in batch mode if the sequence of work steps is of no importance. The option, however, requires a somewhat more complicated program logic.
See also: