Dead lock not terminated

  • Dear All

    I have read that sql takes care of dead locks on its own. But when i run following query through SSMS in 2 differnt query sessions it keeps on going

    USE AdventureWorks

    BEGIN TRAN

    UPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 25

    WAITFOR DELAY '0:0:10'

    SELECT * FROM Person.Address WHERE AddressID = 20

    Am i doing something wrong. I am using sql server 2008 R2

    Regards

  • That wont cause a deadlock, that will cause blocking.

    You need to do things in a different order, eg 1 query update t1 then t2, other query update t2 then t1

    CREATE TABLE t1 (i int);

    CREATE TABLE t2 (i int);

    INSERT t1 SELECT 1;

    INSERT t2 SELECT 9;

    /* in one window enter: */

    BEGIN TRAN

    UPDATE t1 SET i = 11 WHERE i = 1

    WAITFOR DELAY '00:00:20'

    UPDATE t2 SET i = 99 WHERE i = 9

    COMMIT

    /* in a second window (another transaction) enter: */

    BEGIN TRAN

    UPDATE t2 SET i = 99 WHERE i = 9

    WAITFOR DELAY '00:00:20'

    UPDATE t1 SET i = 11 WHERE i = 1

    COMMIT

  • Thanks for u quick reply.

    I would like to know how do i find if system is blocking? Is there a query by which i can find out that blocking is happning?

  • select * from sys.dm_exec_requests where blocking_session <> 0

  • now when tried to test the command given by you, I ran the same quires mentione by me in my first post and it come out on its own with following message

    (1 row(s) affected)

    Msg 1205, Level 13, State 51, Line 5

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

    Surprising. Why it did not happen when i tried first few times?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply