Previous Topic: Default Ready Mode Using Navigational DMLNext Topic: Physical Area Locks

Lock Management › Readying Areas › Ready Modes and SQL Access

Ready Modes and SQL Access

Factors Affecting SQL Lock Management

When accessing data using SQL, the way in which an area is readied depends on several factors:

Transaction State

A transaction initiated using SQL has one of two states:

State

Description

READ ONLY

Data can be read, but not updated; updates to temporary tables are allowed

READ WRITE

Data can be both read and updated using DML and DDL statements

Default is READ WRITE

Unless otherwise specified, the transaction state is READ WRITE. You can override the default when you define an access module or by issuing a SET TRANSACTION statement at runtime.

Isolation Level

A transaction initiated using SQL also has one of two isolation levels:

Isolation Level

Description

CURSOR STABILITY

Guarantees read integrity. Read integrity ensures that:

  • All data accessed by the transaction is in a committed state
  • The most-recently accessed row of an updatable cursor is protected from update by other transactions while it remains current

TRANSIENT READ

Does not guarantee read integrity. For this reason, a transaction executing under transient read is not allowed to update the database. If the isolation level of a transaction is transient read, the transaction state is automatically READ ONLY.

Default is CURSOR STABILITY

Unless otherwise specified, the isolation level of a transaction is CURSOR STABILITY. You can override the default when you define an access module or by issuing a SET TRANSACTION statement at runtime.

Requested Ready Modes

You can specify within the access module definition the modes in which CA IDMS/DB is to ready the areas accessed by non-dynamic SQL statements embedded in an application. Otherwise, CA IDMS/DB determines the ready mode at runtime. It also determines the ready mode at runtime for dynamic SQL statements.

Runtime Ready Modes

The ready mode in which an area is accessed at runtime depends on the requested ready mode, the transaction state, the isolation level, and the area's availability:

Transaction State

Isolation Level

Area Ready Mode

READ ONLY

TRANSIENT READ

Transient retrieval mode; no row locks are placed.

READ ONLY

CURSOR STABILITY

Retrieval modes only.

If update modes were specified on the CREATE or ALTER ACCESS MODULE statement, CA IDMS/DB changes them to shared retrieval. If no ready option was specified, the default is shared retrieval.

READ WRITE

CURSOR STABILITY

All areas are accessed using the mode specified on the CREATE ACCESS MODULE.

If no mode was specified, the default is:

  • Shared update in local mode and under the central version, if the area status is update
  • Shared retrieval under the central version, if the area status is retrieval

Under central version, if an area is being readied in a retrieval mode and the status of the area is transient retrieval, CA IDMS/DB changes the ready mode to transient retrieval.