[SQL]DeadLock practice

2014-06-27

3 Sessions, 2 Tables, 2 Transactions for Updating, 3 "SELECT" statements

SessionTable NamesT-SQL
52Sale.Category  and Sale.CarBEGIN TRAN, SELECT
53Sale.Car and Sale.CategoryBEGIN TRAN, SELECT
54Sale.CategorySELECT

The major sessions are 52 and 53. 54 is just show the another deadblock too.

 

Step 1:

In session 52 I want to update the table "Sale.Category"

BEGIN TRAN
    update Sale.Category
    set Name ='ITTXXXXx'
    WHERE CategoryID =1
-- ROll Back/ COMMIT TRAN

 

Step 2:

In Session 53, I want to update the table "Sale.Car"

BEGIN TRAN
    UPdate Sale.Car
    SET Title = 'Microsoft madexxxxx'
    WHERE CarID = 1
-- Rollback/ COMMIT TRAN

 

Step 3:

All "TRANs", I didn't "ROLLBACK" and "COMMIT" YET

 

Step 4:

Then go back session 52, I am updating "Sale.Category", but also I am doing another request

select * from Sale.Car

Then go back session 53, I am updating "Sale.Car", but also I am doing another request

SELECT * from Sale.Category

Then go to session 54 (optional)

select * from Sale.Category

 

Step 5:

Turn on trace flag 1222

DBCC TRACEON(1222)

GO

 

Step 6:

Open SQL Profiler, click some options, then run

  • Deadlock Graph (you will know it's an good idea)
  • Lock:Deadlock
  • Lock:Deadlock Chain

 

Step 7:

To see the results

See 3 options are showed up, "DeadLock chain", "DeadLock" and "Graph"

as I mentioned before, why you needed to click "Graph" option, because once you've choiced, it will show you the graph

==========================================================================

The victim is choiced by SQL itself, it usually the last session that was blocked by others session.

But the deadlock is still encountering, so SQL server will choice the next victim in order to let the system run continuously(??)..

 

Haha, see the Session 52 was the next "Victim".

 

 

 

Newer Older