How To Create A Deadlock In Sql Server

Home
Databases
Microsoft SQL Server

I've read a lot about deadlocks on multiple other blogs, and I understand what they are and why they happen... yet I'm having trouble actually producing one myself. Even when I've attempted to follow examples in other blogs, it doesn't seem to work for me.

I'll open two different sessions, do a BEGIN TRAN in each, then run some update queries which should lock on resources. However, it always seems like one of my sessions will process the UPDATE statement, and then the other one will just hang - waiting, of course, for the first one to release the lock so that it can probably update. I'll see a LCK_M_U for the waiting session.

The thing is, yes, that's exactly what I imagine SQL Server will do! Properly wait until the first transaction has released its update lock, then run the second one. But from the examples I've been attempting to run, I imagine that I should be creating a situation that deadlocks, but I'm clearly doing something wrong.

Maybe it's just a timing thing? I'm attempting to "step" through my attempts, but maybe it needs more contention to properly deadlock?

If anyone can help, much appreciated. I'm just trying to get a better understanding about exactly how to create a deadlock myself.

Thanks, all!

Here's one for you. I'm doing this in the AdventureWorks2012 sample database.

1. Open 2 query windows in the database

2. In query window 1, run the following command:

                                                Begin                        Tran                        Select                        *                        From                        [                        Person                        ].[                        Address                        ]                        With                        (                        holdlock                        );

3. In query window 2, run the following command:

                                                Begin                        Tran                        Select                        *                        From                        [                        Person                        ].[                        Person                        ]                        With                        (                        holdlock                        );

4. Both queries should complete fine. Now, to run the queries that will complete the deadlock. In query window 1, run the following:

                                                Update                        [                        Person                        ].[                        Person                        ]                        Set                        [                        Title                        ]                        =                        'Dr'                        ;

5. Without waiting (it will keep running), in query window 2, run the following:

                                                Update                        [                        Person                        ].[                        Address                        ]                        Set                        City                        =                        N                        'Portland'                        ;

6. One of the queries will complete, and the other will be killed as the deadlock victim. Now, run ROLLBACK in the one that completed to undo the changes you made.

8 Replies

I think the issue you're having is that one transaction is blocking the second, but the second transaction isn't blocking the first. They both need to be waiting on the other. Have each transaction do some sort of select statement to retrieve one row, then update a different row. Have each transaction select the row the other transaction will update and set the transaction isolation level to repeatable read. That should do it.

Here's one for you. I'm doing this in the AdventureWorks2012 sample database.

1. Open 2 query windows in the database

2. In query window 1, run the following command:

                                                        Begin                            Tran                            Select                            *                            From                            [                            Person                            ].[                            Address                            ]                            With                            (                            holdlock                            );

3. In query window 2, run the following command:

                                                        Begin                            Tran                            Select                            *                            From                            [                            Person                            ].[                            Person                            ]                            With                            (                            holdlock                            );

4. Both queries should complete fine. Now, to run the queries that will complete the deadlock. In query window 1, run the following:

                                                        Update                            [                            Person                            ].[                            Person                            ]                            Set                            [                            Title                            ]                            =                            'Dr'                            ;

5. Without waiting (it will keep running), in query window 2, run the following:

                                                        Update                            [                            Person                            ].[                            Address                            ]                            Set                            City                            =                            N                            'Portland'                            ;

6. One of the queries will complete, and the other will be killed as the deadlock victim. Now, run ROLLBACK in the one that completed to undo the changes you made.

I edited the above to remove the xlock hints. They're not needed. Shared locks will block an update just like exclusive locks will. The holdlock hint is needed to ensure that the locks from the SELECT is held until the end of the transaction. I could have done updates instead of Selects, but what the heck.

Yes! Thank you both. I was able to now use the examples to write my own simple deadlock scenario. I saw wait types of LCK_M_U on both queries for about 3 seconds at the same time before one was killed. In my example, I was updating on two different tables which caused the lock.

I did notice, however, that when I tried to produce same scenario on a single table, it does not deadlock, even when I'm going after the same rows. The query run later will always just wait until the table is available again. Will this always be the case? It makes sense to me that that is how SQL will behave, but just so I'm clear, is there a simple scenario that will let me produce a deadlock with 2 sessions trying to update a single table?

Sure. Use my previous example with only 1 table.

Query window 1:

                                                        Begin                            Tran                            Select                            *                            From                            [                            Person                            ].[                            Person                            ]                            With                            (                            holdlock                            );                            Update                            [                            Person                            ].[                            Person                            ]                            Set                            Title                            =                            N                            'Mrs'                            ;                            Rollback

Query window 2:

                                                        Begin                            Tran                            Select                            *                            From                            [                            Person                            ].[                            Person                            ]                            With                            (                            holdlock                            );                            Update                            [                            Person                            ].[                            Person                            ]                            Set                            [                            Title                            ]                            =                            'Dr'                            ;                            Rollback

Gotcha. In this scenario, are we just using the HOLDLOCK select in order to create the idea of a long-running transaction? I got my example to work once I made sure to run the statements in the proper order.

By the way, one reason I do deadlock examples using SELECT followed by UPDATE or DELETE is because a very common cause of deadlocks that tend to really surprise people is this:

                                                        If                            Exists                            (                            Select                            *                            From                            dbo                            .                            MyTable                            Where                            Column                            =                            'Value'                            )                            Begin                            Update                            dbo                            .                            MyTable                            Set                            Column                            =                            'Other Value'                            Where                            Column                            =                            'Value'                            ;                            End

Robert L Davis wrote:

By the way, one reason I do deadlock examples using SELECT followed by UPDATE or DELETE is because a very common cause of deadlocks that tend to really surprise people is this:

                                                            If                              Exists                              (                              Select                              *                              From                              dbo                              .                              MyTable                              Where                              Column                              =                              'Value'                              )                              Begin                              Update                              dbo                              .                              MyTable                              Set                              Column                              =                              'Other Value'                              Where                              Column                              =                              'Value'                              ;                              End

Aaaaaah. Thank you. Very enlightening. I feel like this thread has helped me see the code of the Matrix a little bit.

This topic has been locked by an administrator and is no longer open for commenting.

To continue this discussion, please ask a new question.