Locking in SESAM/SQL transactions

Locking ensures the consistency of processed data.

CALL DML	A transaction exclusively reads all the records if the associated logical file was opened with open mode “X” and the additional parameter “readnolock” has not been specified. Shared reading is only possible if the associated logical file was opened with open mode “R”.
SQL	Query operations are conducted in shared mode; update operations are conducted in exclusive mode.

This excludes the possibility of corruption of the data.

Modifications in connection with CALL DML

SESAM/SQL allows the following modifications to locking in connection with data retrieval in CALL DML (see the “CALL-DM Applications” manual):

• Reads are carried out without locking (this can lead to a non-repeatable read).

• The exclusive lock is ignored (this can lead to a dirty read).

Modifications in connection with SQL

The locking method and modifications to it are mapped to the isolation level in SQL. SQL users explicitly or implicitly specify an isolation level for each transaction.

• implicitly at conversion time or by means of the configuration file

• explicitly by means of the SET TRANSACTION statement or the pragma ISOLATION LEVEL.

This also determines the degree of transaction concurrency (see section “SQL transaction”).

Wait states in connection with transactions

If a transaction attempts to access a row that has been locked by another transaction, the transaction cannot execute the statement: The transaction is placed in a wait state. At the same time, the transaction that caused the row to be locked is registered in the table of active transactions.

Table of active transactions
Transaction	is locked by transaction
A
B	A
C	B
D	A

Table 45: Transactions in a wait state

The waiting transaction can only continue with processing once the transaction that locked the row has been committed or rolled back. The SESAM/SQL DBH monitors waiting transactions for this reason and reactivates them once the transaction that locked the row has completed.

The lower-level DBH-Option TRANSACTION-SECURITY can be used to modify the behavior of the DBH when dealing with locking transactions and the escalation of transaction locks. During normal operation it is possible to modify these parameters with the administration command MODIFY-TRANSACTION-SECURITY (see the “Database Operation” manual).

Two situations can arise in which automatic reactivation is impossible or is deferred for an unnecessarily long period: deadlock and longlock.

Deadlocks

A deadlock occurs when two or more transactions lock each other. If this occurs, neither transaction can be completed.

The administration statement SET-SESSION-DIAGNOSIS enables you to have additional information about the users and the objects concerned output to SYSLST when a deadlock occurs, see the “Database Operation” manual.

The following table shows three deadlocked transactions. The number of I/O operations required by different types of access varies: reading a data block involves a single I/O operation; writing a data block involves two I/O operations.

Table of active transactions
Transaction	is locked by transaction	I/O operations
A	C	100
B	A	20
C	B	60
D		5

Table 46: Three deadlocked transactions

The SESAM/SQL DBH automatically clears deadlocks.

Whenever a transaction is locked, the SESAM/SQL DBH checks whether the lock causes a deadlock.

If it does, the SESAM/SQL DBH rolls back the deadlocked transaction that has carried out the least I/O operations to date (in table 46, transaction B). The unlocked transactions can continue processing. In table 47, this applies to transaction C.

Once the deadlock has been cleared, the situation is as follows:

Table of active transactions
Transaction	is locked by transaction	I/O operations
A	C	100
C		60
D		5

Table 47: Transaction table after the deadlock is cleared

The transaction that has carried out the smallest numbers of I/O operations is always selected as the transaction to be rolled back.

Longlocks

The SESAM/SQL DBH detects transactions that have suspended processing for long periods of time and are locking resources required by other transactions. The SESAM/SQL DBH automatically rolls back transactions of this kind. System administrators specify a rollback criterion that defines how long inactive transactions are allowed to lock other transactions (the LOCK-TIME parameter in the TRANSACTION-SECURITY DBH option or the MODIFY-TRANSACTION-SECURITY administration command, see the “Database Operation” manual).