Writing an SQL Program › Concurrency Control and Isolation Levels

Concurrency Control and Isolation Levels

Concurrency Control

CA IDMS manages concurrent access to the same set of data with a system of locks. The degree of concurrent access allowed by a database transaction is determined by the isolation level of the transaction and the ready mode of the areas it accesses.

Locks

CA IDMS provides two types of lock:

• A retrieval lock prevents updates but allows retrieval of data by another database transaction
• An update lock prevents both updates and retrieval of data by another database transaction

Isolation Levels and Locking

CA IDMS supports two isolation levels. The following descriptions explain how the system performs locking under each isolation level assume the least restrictive ready mode for areas accessed by the database transaction:

• Cursor stability—Under cursor stability, the DBMS places a retrieval lock on the row on which an updateable cursor is positioned until the cursor position changes. It places a retrieval lock on the row accessed by a SELECT statement that accesses only one row (a single-row select) until the SQL transaction accesses another row from the same table. It releases update locks when the transaction either terminates or issues a COMMIT CONTINUE.
• Transient read—Under transient read, the DBMS:
  • Places no locks on rows accessed by the transaction
  • Allows the transaction to retrieve locked rows
  • Prevents the transaction from performing updates

Concurrency Under Cursor Stability

Cursor stability provides the greatest possible concurrency while guaranteeing the integrity of data read by the transaction. Under cursor stability:

• The row on which an updateable cursor is positioned cannot be updated by another database transaction before the cursor position changes.
• A single row retrieved by a SELECT statement cannot be updated by another database transaction until the original transaction accesses another row of the table.

Cursor stability does not prevent other database sessions that are sharing the same transaction from updating a session's current cursor position or its most-recently retrieved row of a single row select.

Cursor stability is the CA IDMS default. It is appropriate for high-volume transaction environments.

Concurrency Under Transient Read

Transient read provides no protection from the effects of concurrent database transactions. It allows a database transaction to read data that has not been committed and allows concurrent database transactions to update the data.

Transient read is appropriate when the transaction is retrieval only and does not require the data to be consistent and entirely accurate.

Specifying the Isolation Level

You can specify the default isolation level with the DEFAULT ISOLATION parameter of the CREATE ACCESS MODULE statement.

Note: For more information about how to create the access module, see Creating the Access Module.

A program can override the default isolation level for the access module by issuing a SET TRANSACTION statement. The specification on this statement remains in effect until the end of the transaction.

Note: For more information about the SET TRANSACTION statement, see the CA IDMS SQL Reference Guide.

Area Ready Mode

You can control concurrent access at the area level using the READY parameter of the CREATE ACCESS MODULE statement. This parameter allows you to specify what type of retrieval or update lock the DBMS sets on an area that the program accesses. The type of lock, in combination with the PRECLAIM or INCREMENTAL option, determines how long the DBMS holds the lock for the transaction.

Note: For more information about the READY parameter of the CREATE ACCESS MODULE statement, see the CA IDMS SQL Reference Guide.

Repeatability

If you specify a ready mode of protected retrieval or protected update, the DBMS will prevent concurrent update access in the specified areas for the duration of a database transaction. This gives the transaction running under cursor stability the ability to repeat a read of the specified area or areas without changes to the data by other transactions.

Note: For more information about the lock management system, see the CA IDMS Database Administration Guide.