 |
» |
|
|
|
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
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: 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: 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: You can retrieve only rows that have
been committed by some transaction or modified by your own transaction. Other transactions can write on the page on which the transaction has a
cursor positioned, because locks are released as soon as data is
read. If an update is done on a page, the lock is retained
until the transaction ends with a COMMIT WORK or ROLLBACK WORK statement.
Use the RC isolation level for improved concurrency, especially
in transactions which include a long duration of time between fetches.
When you must update following a FETCH statement using the RC isolation level,
use the REFETCH statement first, which obtains and holds locks on the
current page, thus letting you verify the continued existence of
the data you are interested in. 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: The transaction does not obtain any
locks on user data when reading, and therefore may read uncommitted
data. The transaction does not have to wait on locks on
user data, so deadlocks are considerably reduced. However, transactions
may still have to wait for system catalog locks to be released. If an update is done on a page, the transaction
obtains an exclusive lock, which is retained until the transaction
ends with a COMMIT WORK or ROLLBACK WORK statement.
RU is ideal for reporting and similar applications
where the reading of uncommitted data is not of major importance.
If you must update following a FETCH statement using the RU isolation level, use the REFETCH statement first, which obtains and holds the appropriate locks,
letting you verify that you are not updating a row based on uncommitted
data. |
