ALLBASE/SQL Reference Manual (36216-90216)

174 Chapter5
Concurrency Control through Locks and Isolation Levels
Defining Isolation Levels between Transactions
Defining Isolation Levels between Transactions
Isolation level is the degree to which a transaction is separated from all other concurrent
transactions. Four levels are possible, shown here in order from most to least restrictive:
• Repeatable read (RR)--the default
• Cursor stability (CS)
• Read committed (RC)
• Read uncommitted (RU)
In general, you should choose the least restrictive possible isolation level for your needs in
order to achieve the most concurrency. You select an isolation level in the BEGIN WORK
statement, as in the following example:
isql=> BEGIN WORK CS;
An isolation level can also be specified with either the SET TRANSACTION or SET SESSION
statement.
Repeatable Read (RR)
By default, transactions have the Repeatable Read (RR) isolation level, which means
that within the transaction, you can access the same data as often as you wish with the
certainty that it has not been modified by other transactions. In other words, other
transactions are not allowed to modify any data pages that have been read by your
transaction until you issue a COMMIT WORK or ROLLBACK WORK statement. This is the most
restrictive level, allowing the least concurrency.
All the examples of transactions shown so far use the RR (repeatable read) isolation level.
At the RR level, all locks are held until the transaction ends with a COMMIT WORK or
ROLLBACK WORK statement. This option causes each data row or page read to be locked with
a share lock, which forces any other user trying to update the data on the same row or page
to wait until the current transaction completes. However, other transactions may read the
data on the same row or page. For PUBLICROW tables, if you update a row during a
transaction, the row receives an exclusive lock, which forces other transactions to wait for
both reading or writing that row until your transaction ends. For PUBLIC tables, if you
update a data page during a transaction, the page receives an exclusive lock, which forces
other transactions to wait for both reading or writing until your transaction ends.
Repeatable Read should be used if you must read the same data more than once in the
current transaction with assurance of seeing the same data on successive reads.
Cursor Stability (CS)
The Cursor Stability (CS) isolation level guarantees the stability of the data your cursor
points to. However, this isolation level permits other transactions to modify rows of data
you have already read, provided you have not updated them and provided they are not still
in the tuple buffer. CS also permits other transactions to update rows in the active set
which your transaction has not yet read into the tuple buffer. With cursor stability, if you
move your cursor and then try to reread data you read earlier in the transaction, that data