The repeatable read transaction isolation level provides the highest level of isolation between transactions because it acquires a share or exclusive scan range intent lock before beginning a scan (all rows are accessed with the scan operation). This lock is released when the transaction ends, guaranteeing that other transactions cannot update, delete or insert rows within the scan range until the transaction ends. If another transaction attempts to do so, it waits until the transaction has ended, or one of the transactions is aborted if an exclusive control interlock occurs. As the name implies, a repeatable read transaction is therefore guaranteed to reread the exact same set of rows if it reopens a cursor or re-executes a SELECT INTO statement (any changes made by the transaction itself would of course be visible).
Although repeatable read isolation provides a convenient way to isolate transactions, it does so at the cost of possible lower throughput and more exclusive control interlocks, as described in the following:
Because more rows remain locked for a longer period of time, repeatable read isolation may lower total throughput (transactions wait longer for locks to be released).
Repeatable read may cause more exclusive control interlocks, especially if concurrent transactions are not using repeatable read. For example, if cursor stability transaction CS updates row R1, and then repeatable read transaction RR acquires a scan range intent lock that includes row R1, CS waits if it attempts to read a row in RR's scan range with exclusive control. While CS is waiting, unless row R1 has already been read by RR's scan, RR eventually attempts to read row R1 with a row-level share lock. But because CS is waiting on RR, neither transaction can continue. So, the deadlock condition is resolved by abnormally terminating RR, which releases its locks and allows CS to continue. In this case, if transaction CS is changed to repeatable read isolation, it acquires an exclusive scan range intent lock before updating row R1. Transaction RR then waits when it attempts to acquire its scan range intent lock.
A deadlock can still occur if concurrent repeatable read transactions acquire multiple scan range intent locks. The same conditions exist as with row-level locking of cursor stability transactions, except that with repeatable read a larger number of rows may be locked with the scan ranges, and these locks are held for a longer period of time. This is especially true when the first column of the scan index is not restricted, or multiple indexes are merged. In these cases, the scan range is the entire table.
If deadlock avoidance is critical, it can be avoided if all concurrent transactions execute LOCK TABLE statements in the same sequence before executing any other statements in a transaction. If the transaction might insert, update or delete rows of a table, the lock must be exclusive, and this causes all other transactions attempting to execute a LOCK TABLE statement for the table, or tables, to wait. Because the LOCK TABLE statements are executed in the same sequence, perhaps by table name, no deadlock can occur.
|
Copyright © 2014 CA.
All rights reserved.
|
|