Read Committed vs Repeatable Read Example

Question

I'm trying to execute the following two queries in SQL Server Management Studio (in separate query windows). I run them in the same order I typed them here.

When isolation level is set to READ COMMITTED they execute ok, but when it's set to REPEATABLE READS the transactions are dead locked.

Can you please help me to understand what is dead locked here?

First:

begin tran declare @a int, @b int set @a = (select col1 from Test where id = 1) set @b = (select col1 from Test where id = 2) waitfor delay '00:00:10' update Test set col1 = @a + @b where id = 1 update Test set col1 = @a - @b where id = 2 commit

Second:

begin tran update Test set col1 = -1 where id = 1 commit

UPD Answer is laready given but folowing the advice I'm inserting the deadlock graph

Have you tried creating a deadlock graph in profiler?

SQLChao
– SQLChao

2016-08-03 15:45:14 +00:00
Commented Aug 3, 2016 at 15:45 — SQLChao
– SQLChao, Commented Aug 3, 2016 at 15:45

Henk Kok · Accepted Answer · 2016-08-03 16:27:37Z

In both cases the selects use a shared lock and the updates an exclusive lock.

In READ COMMITTED mode, the shared lock is released immediately after the select finishes.

In REPEATABLE READS mode, the shared locks for the selects are held untill the end of the transaction, to ensure that no other sessions can change the data that was read. A new read within the same transaction is garantueed to yield the same results, unless the data was changed in the current session/transaction

Originally I thought, that you executed "First" in both sessions. Then the explanation would be trivial: both sessions acquire and get a shared lock, which then blocks the exclusive lock required for the updates.

The situation with a second session doing only an update is a little more complex. An update staement will first acquire an update lock (UPDLOCK) for selecting the rows that must be updated, which is probably similar to a shared lock, but at least not blocked by a shared lock. Next, when the data is actually updated, it tries to convert the update lock to an exclusive lock, which fails, because the first session is still holding the shared lock. Now both sessions block each other.

Collectives™ on Stack Overflow

Read Committed vs Repeatable Read Example

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related