Stimulating DEADLOCK in SQL Server

Here in this article we are trying to stimulate Deadlock situation, we are trying to create two table table1 and table2 for the same purpose

create table table1

(id int identity(1,1),

col2 int)

create table table2

(id int identity(1,1),

col2 int)

Command(s) completed successfully.

Now lets insert few records in both the table.

insert into table1

select 10 union all

select 20 union all

select 30

insert into table2

select 100 union all

select 200 union all

select 300

No w open a session by opening a new query window in SSMS, I will call it session1 and try to execute the below code, here we opened a transaction then updating table1 col2 where id =1

begin tran

       update table1

       set col2 = col2 + 10

       where id = 2

Now open another query window (second session) in SSMS and execute the below statement, here we are trying to update both the table the second table will wait for first transaction to unlock table1 for its update. And it goes on execution for infinite time.

begin tran

       update table2

       set col2 = col2 + 100

       where id = 2

       update table1

       set col2=col2 + 10

       where id = 2

commit tran

Now again come back to session1 window and execute the below code

update table2

set col2 = col2 + 100

where id = 2

now as you run this above statement on session1, SQL Server sense deadlock since the above code wait for table2 to get unlocked by session2 and same way session2 waits for session1 for table1 to unlock and DEADLOCK occurs, in this case SQL server chosen session1 as victim and abort session1 with a message

Msg 1205, Level 13, State 45, Line 7

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

And session 2 will complete automatically.

There are no nights for SQL Developers, if so please give me address  J

Post Reference: Vikram Aristocratic Elfin Share