ALLBASE/SQL Reference Manual (36216-90216)

Chapter 5 175
Concurrency Control through Locks and Isolation Levels
Defining Isolation Levels between Transactions
may have been modified by another transaction. At the CS level, share locks on data
(whether at the row or page level) are released as soon as the associated rows are no longer
in the tuple buffer. Exclusive locks are held until the transaction ends with a COMMIT WORK
or ROLLBACK WORK statement. The following describes what using CS means:
• No other transactions can modify the row on which the transaction has a cursor
positioned.
• A shared lock is kept on the row or page that the cursor is currently pointing to. When
the cursor is advanced to the next page of data and nothing has been updated on the
previous page, the lock on that previous page is released.
• If an update is done on a data page, the exclusive lock on that page is retained until the
transaction ends with a COMMIT WORK or ROLLBACK WORK statement.
Use the CS isolation level for transactions in which you need to scan through large portions
of a database to locate rows that need to be updated immediately. CS lets you do this
without preventing other transactions from updating data pages that you have already
passed by without updating. CS guarantees that a row of data will not be changed between
the time you issue the FETCH statement and the time you issue an UPDATE WHERE CURRENT
in the same transaction.
NOTE When you use CS for a query that involves a sort operation, such as an ORDER
BY, DISTINCT, GROUP BY, or UNION, or when a sort/merge join is used to
join tables for the query, the sort may use a temporary table for the query
result. In such cases, your cursor actually points to rows in this temporary
table, not to rows in the tuple buffer. Therefore, when sorting is involved, the
locks held on data pages or rows are released before you manipulate the
cursor. In other words, no locks are held at the cursor position for sorted scans
at the CS isolation level. If it is important to retain locks in this situation, use
the RR isolation level.
If you are updating a row based on the information in a sorted query result,
use a simple SELECT statement to verify the continued existence of the data
before doing the update operation. In this case, it is good practice to include
the TID as part of the original SELECT, and then to use the TID in the WHERE
clause of the SELECT that verifies the data.
Read Committed (RC)
With Read Committed, you are sure of reading consistent data with a high degree of
concurrency. However, you are not guaranteed the ability to
reread the data your cursor
points to, because other transactions can modify that data as soon as it has been read into
your application's tuple buffer. Also, you cannot read rows or pages from the data buffer
that have been modified by another transaction unless that other transaction has issued a
COMMIT WORK statement. At the RC level, share locks on data are released as soon as the
data has been read into your buffer. Exclusive locks are held until the transaction ends
with a COMMIT WORK or ROLLBACK WORK statement.
The following describes what using RC means: