Menu
Trying to completely understand SQL Server Isolation Levels - notably REPEATABLE READ.
I have a sproc that starts a transaction and puts a cursor around some data (boo hiss). This can be a fair chunk of data, so can take a while to do.
It will then COMMIT or ROLLBACK.
During this time, before the transaction has been closed, if someone calls a method which causes some of those affected rows to be READ, my understanding is that this method will stall until the first method is complete. They will then be served up the data (as long as a time-out doesn't occur first)
I think I'm right, but question is - am I?!
977
Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged, and available to read.
The REPEATABLE READ transaction will still see the same data, while the READ COMMITTED transaction will see the changed row count. REPEATABLE READ is really important for reporting because it is the only way to get a consistent view of the data set even while it is being modified.
The repeatable read isolation level provides a guarantee that data will not change for the life of the transaction once it has been read for the first time. There are a couple of subtleties contained in that definition. First, it allows data to change after the transaction starts but before the data is first accessed.
In a Repeatable Read isolation level, new rows can be inserted into the dataset. In a Serializable isolation level, all the rows are locked for the duration of the transaction, no insert, update or delete is allowed.
REPEATABLE READ prevents SELECTs from lifting shared locks they placed until the end of the transaction.
With transaction 1 as READ COMMITTED, you can update a row in transaction 2 after you selected it in transaction 1.
With transaction 1 as REPEATABLE READ, you cannot update a row in transaction 2 after you selected it in transaction 1.
The scenarios:
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.
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 not lock each other even with REPEATABLE READ, since shared locks they place are compatible with each 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
