[SQL]DeadLock practice

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

Session	Table Names	T-SQL
52	Sale.Category  and Sale.Car	BEGIN TRAN, SELECT
53	Sale.Car and Sale.Category	BEGIN TRAN, SELECT
54	Sale.Category	SELECT

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".