0

I think Deadlock will happen when T1 lock tabA and T2 lock tabB. Then querying T1 to tabB (locked by T2) and T2 querying to tabA (locked by T1). So T1 and T2 will wait and wait till end of life!

But in my example: Running T2 after executing T1, T2 will wait till T1 is committed. Then running T3 (maybe T1's lock had been released by some special reasons that I don't know because T2 executed complete). So, why deadlock happen at T3? I can't understand. T3 should wait till T2 is committed (like T2 waiting T1 is committed) or T1's lock is not released yet or another reason...?

drop table TabA;
create table TabA (a int, b int);
insert into TabA values (1,11);
insert into TabA values (2,22);
insert into TabA values (3,333);
/
select * from TabA;
/
--T1. Run T1 first.
begin tran t1
insert into TabA values (5,55);
update TabA set b = 44 where a=4;
select * from TabA where a=4;
waitfor delay '00:00:10';

--T2. Run T2 after T1 (run continuously, it must after cooldowning 10s).
--T2 is still waiting T1 is committed.
begin tran t2
insert into TabA values (4,444);
update TabA set b = 111 where a=1;  

--T3. Run T3 after T1 is completed. 
--Deadlock is detected at there and T2 is executed complete.
select * from TabA where a=1;

I use 2 MS-SQL 2008 R2 client (test with my friend using 2014 is okay). T1 and T3 is belong to client #1 and T2 is belong to client #2:

Client #1:
--T1 
--T3
Client #2:
--T2

Ordinarily query by: T1>T2>T3.
//I will find and try with another PC using MS-SQL 2008 R2 later.

0
4

Simplified version

drop table TabA;
create table TabA ( a int
,                   b int );
insert into TabA
values ( 1, 11 ),
    ( 2, 22 ),
    ( 3, 333 );
select * from TabA;

--T1. Run T1 first.
begin tran t1
insert into TabA values (5, 55 );

waitfor delay '00:00:10';
--Run T2 in the other session within 10 sec. 

-- After delay expires deadlock is detected 
select * from TabA where a = 2;

commit tran t1;

Second script

--T2. 
begin tran t2
update TabA set b = 111 where a=1;
commit tran t2;

Looking at deadlock graph (MS SQL 2008 Profiler), you'll see

1) T1 has inserted (5,555) row and holds X lock on this RID untill commit.

2) T2 tries to get U lock on all rows, check predicate a=1 and update the rows where the predicate evaluates to true.
2.1. It successfully updates one a=1 row and holds X lock on this RID untill commit.
2.2. When T2 tries to get U lock on (5,555) row it can't do that due to T1's lock. So T2 waits for T1 to commit.

3) After delay T1 tries to get S lock on all rows when executing SELECT * FROM TabA ... and naturally it can't obtain the lock on the row held by T2 (see 2.1). T1 must wait T2 which waits T1. Deadlock.

Now add PK to TabA

create table TabA ( a int PRIMARY KEY,
                   b int );

Sql sever is now deadly sure it needn't check any more rows for a=1 at step(2.1). T2 commits immediately and no dead lock detected.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.