ALLBASE/SQL Reference Manual
> Chapter 5 Concurrency Control through Locks and Isolation LevelsDefining Isolation Levels between Transactions |
||||||||||||||||||||||||||
|
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 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:
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:
Read Uncommitted (RU)The Read Uncommitted (RU) isolation level lets you read anything that is in the data buffer, whether or not it has been committed, in addition to pages read in from disk. For example, someone else's transaction might perform an update on a page, which you can then read; then the other transaction issues a ROLLBACK WORK statement which cancels the update. Your transaction has thus seen transitory data which was not committed to the database. At the RU level, no share locks are obtained on user data. Exclusive locks obtained during updates are held until the transaction ends with a COMMIT WORK or ROLLBACK WORK statement. The following describes what using RU means:
|