Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
419 views
in Technique[技术] by (71.8m points)

sql server - Repeatable Read - am I understanding this right?

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?!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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:

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 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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...