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

 

 

 

1 comment

xxk9m71k1k said...

The reason for this is that the net on line casino trade is so competitive that they typically compete with a number of} dozen others for the same players. So, each on line casino tries to outdo the other by offering extra generous welcome bonuses, however typically with extra stringent 메리트카지노 policies tied to them. Deposit bonuses, reload bonuses, cashback presents and extra. Shazam Casino is offering new Australian players the prospect to say sixty five free spins as part of of} their mouthwatering no deposit bonus.

Newer Older