[ACCEPTED]-Repeatable Read - am I understanding this right?-isolation-level
REPEATABLE READ prevents SELECTs from lifting shared locks they 9 placed until the end of the transaction.
With 8 transaction 1 as READ COMMITTED, you can update a row in transaction 7 2 after you selected it in transaction 1.
With 6 transaction 1 as REPEATABLE READ, you cannot update a row in transaction 5 2 after you selected it in transaction 1.
The 4 scenarios:
READ COMMITTED
1 SELECT -- places a shared lock and immediately lifts it.
2 UPDATE -- places an exclusive lock. Succeeds.
1 SELECT -- tries to place a shared lock but it conflicts with the exclusive lock placed by 2. Locks.
REPEATABLE READ
1 SELECT -- places a shared lock and keeps it
2 UPDATE -- tries to places an exclusive lock but it's not compatible with the shared lock. Locks
1 SELECT -- the lock is already placed. Succeeds.
Update:
As for you question: in SQL Server, SELECTs will 3 not lock each other even with REPEATABLE READ, since shared 2 locks they place are compatible with each 1 other:
CREATE TABLE t_lock (id INT NOT NULL PRIMARY KEY, value INT NOT NULL)
INSERT
INTO t_lock
VALUES (1, 1)
-- Session 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
DECLARE @id INT
DECLARE cr_lock CURSOR DYNAMIC
FOR
SELECT id
FROM t_lock
OPEN cr_lock
FETCH cr_lock
id
--
1
-- Session 2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
DECLARE @id INT
DECLARE cr_lock CURSOR DYNAMIC
FOR
SELECT id
FROM t_lock
OPEN cr_lock
FETCH cr_lock
id
--
1
-- Session 1
DEALLOCATE cr_lock
COMMIT
-- Session 2
DEALLOCATE cr_lock
COMMIT
Correct.
A full description from MSDN:
Specifies 22 that statements cannot read data that 21 has been modified but not yet committed 20 by other transactions and that no other 19 transactions can modify data that has 18 been read by the current transaction until 17 the current transaction completes.
Shared 16 locks are placed on all data read by each 15 statement in the transaction and are held 14 until the transaction completes. This 13 prevents other transactions from modifying 12 any rows that have been read by the current 11 transaction. Other transactions can insert 10 new rows that match the search conditions 9 of statements issued by the current transaction. If 8 the current transaction then retries the 7 statement it will retrieve the new rows, which results 6 in phantom reads. Because shared locks 5 are held to the end of a transaction instead 4 of being released at the end of each statement, concurrency 3 is lower than the default READ COMMITTED 2 isolation level. Use this option only 1 when necessary.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.