IMAGE/SQL: Issues and answers concerning SQL tables

Software

Papers & Training

Java

HP Partners

News & Events

Register for more info

5.3 TurboIMAGE locking: mode 1 user with an SQL predicate

• Question: What happens when a MODE 1 user READS from a TurboIMAGE data set using an SQL predicate?

  Answer: The isolation level of the transaction and the data set type affect the locks that are acquired:

  • If the isolation level is RU (Read Uncommitted), no locks are acquired.
  • If the isolation level is anything except for RU, the data set type determines whether data set locking or predicate locking is performed.
    • If the SELECT statement references a detail data set, only the rows that qualify for the predicate are locked.
    • If the SELECT statement references a master data set, the entire data set is locked.

• Question: What happens when a MODE 1 user WRITES to a TurboIMAGE data set using an SQL predicate?

  Answer: The data set type determines whether data set locking or predicate locking is performed.

  • If the SQL statement references a detail data set, only the rows that qualify for the predicate are locked.
  • If the SQL statement references a master data set, the entire data set is locked.

Reading from a data set with a predicate only selects a subset of rows

Key points

• A MODE 1 user has the capability to both read and write TurboIMAGE data.

• As described previously, the isolation level of the transaction affects whether or not locks are obtained:

  • If the isolation level is RU (Read Uncommitted), locks are not acquired when the transaction reads data. RU is the weakest isolation level. An RU transaction can read rows that another transaction is actively modifying, even though the other transaction may eventually decide to issue a ROLLBACK WORK and cancel its changes.

  • If the isolation level is RR (Repeatable Read), rows that are read cannot be modified by another transaction until the RR transaction ends. RR is the strongest isolation level. An RR transaction can read a row (that it has already read once) for a second (or third, etc.) time, and the information returned for the second read will be identical to the information that was returned for the first read. Hence, reads are repeatable.

    ALLBASE/SQL has five different types of locks - the most important are S (share) and X (exclusive). S locks improve the concurrency of RR transactions. When data is locked with an S lock, no other transaction may modify it but other transactions are allowed to read it. An X lock prevents access by any other user.

    Today, TurboIMAGE only has one type of lock, which is equivalent to an X lock in ALLBASE/SQL. The only way for TurboIMAGE to guarantee that the data read by an RR transaction will not change is to lock it exclusively. Unfortunately, this negatively affects the concurrency of other RR transactions, especially those who simply need to read the data.

  • The CS (Cursor Stability) and RC (Read Committed) isolation levels are not really implemented in TurboIMAGE. Today, they simply act like RR.

  • SQL isolation levels are described in more detail later in this chapter.

• When the SELECT statement references a detail data set, only the rows that qualify for the predicate are locked.

• When the SELECT statement references a master data set, the entire data set is locked (even though only a subset of rows are returned).

Writing to a data set with a predicate only selects a subset of rows

Key points

• When the UPDATE or DELETE statement references a detail data set, only the rows that qualify for the predicate are locked.

• When the UPDATE or DELETE statement references a master data set, the entire data set is locked (even though only a subset of rows are modified).

Predicate level locking is also used for an insert

Key points

• When the INSERT statement references a detail data set, only the newly inserted rows are locked.

• When the INSERT statement references a master data set, the entire data set is locked.

Page last updated on November 29, 1995