Post reply

How can I simulate a deadlock ??

  • March 22, 2012 at 2:40 am

    #258939

    How can I simulate a deadlock ?? using advantureworks database ?

  • March 22, 2012 at 2:54 am

    #1462557

    Window 1:

    Begin tran

    Update Person.Persons

    Waitfor delay 10 seconds

    Select from Products

    Window 2

    Begin tran

    Update Products

    Waitfor delay 10 seconds

    Select from Person.Persons

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 22, 2012 at 3:00 am

    #1462561

    Hi Monster~~

    THanks for your reply~ can it be more details, I don't know why my Adventureworks don't have

    person.persons

  • March 22, 2012 at 3:01 am

    #1462562

    please take a look here, this is what I use to test out my deadlock alerts

  • March 22, 2012 at 3:14 am

    #1462569

    Hi anthony.green

    I open SSMS open the first window

    and copy the code and run, i saw it's continue running.

    Then I open another window

    and copy the code and run, I also see the code is keep running

    then I try to follow the instruction and go back to the first window ........so what's going to be happened ?

    USE TEMPDB

    CREATE TABLE dbo.foo (col1 INT)

    INSERT dbo.foo SELECT 1

    CREATE TABLE dbo.bar (col1 INT)

    INSERT dbo.bar SELECT 1

    -- 2) Run in first connection

    BEGIN TRAN

    UPDATE tempdb.dbo.foo SET col1 = 1

    -- 3) Run in second connection

    BEGIN TRAN

    UPDATE tempdb.dbo.bar SET col1 = 1

    UPDATE tempdb.dbo.foo SET col1 = 1

    -- 4) Run in first connection

    UPDATE tempdb.dbo.bar SET col1 = 1

    Am I missing some steps ?

  • March 22, 2012 at 3:25 am

    #1462574

    in windows 1 run the below

    USE TEMPDB

    CREATE TABLE dbo.foo (col1 INT)

    INSERT dbo.foo SELECT 1

    CREATE TABLE dbo.bar (col1 INT)

    INSERT dbo.bar SELECT 1

    Then clear the text from the window

    Then run in the same window

    BEGIN TRAN

    UPDATE tempdb.dbo.foo SET col1 = 1

    --1 row updated

    Open a new query window then run

    BEGIN TRAN

    UPDATE tempdb.dbo.bar SET col1 = 1

    UPDATE tempdb.dbo.foo SET col1 = 1

    --wont return anything

    In window 1, clear the screen then run

    UPDATE tempdb.dbo.bar SET col1 = 1

    --this will eventually deadlock

  • March 22, 2012 at 3:48 am

    #1462578

    how to clear the screen ??

  • March 22, 2012 at 4:16 am

    #1462583

    CTRL+A --> Delete

  • March 22, 2012 at 4:21 am

    #1462585

    so I have to stop the job after

    clean the window 1? then run again ?

  • March 22, 2012 at 4:33 am

    #1462592

    stop what job? we haven't created any jobs?

    if you follow the steps

    create the tables in connection 1

    begin tran in connection 1

    update table1 in connection 1 --this one will complete

    begin tran in connection 2

    update table2 in connection 2 --this one will complete

    update table1 in connection 2 --this one will not complete

    update table2 in connection 1 --this one will deadlock

    rollback transaction in connection 2

  • March 22, 2012 at 4:51 am

    #1462607

    mc2601 (3/22/2012)

    Hi Monster~~

    THanks for your reply~ can it be more details, I don't know why my Adventureworks don't have

    person.persons

    Pick any 2 tables, doesn't matter which ones.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 22, 2012 at 7:58 pm

    #1463136

    Thanks anthony.green

    I finally Get the deadlock la!!!

Viewing 12 posts - 1 through 12 (of 12 total)

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