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.