How to Create Deadlocks and Troubleshoot Them
Watch in awe and wonder as I create a deadlock, then use sp_BlitzLock after the fact to show you which queries and tables were involved:
Here are the scripts to run in the left hand window:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE dbo.Lefty (Numbers INT PRIMARY KEY CLUSTERED); INSERT INTO dbo.Lefty VALUES (1), (2), (3); CREATE TABLE dbo.Righty (Numbers INT PRIMARY KEY CLUSTERED); INSERT INTO dbo.Righty VALUES (1), (2), (3); GO BEGIN TRAN UPDATE dbo.Lefty SET Numbers = Numbers + 1; GO UPDATE dbo.Righty SET Numbers = Numbers + 1; GO |
And here are the right hand window commands:
|
1 2 3 4 5 6 7 8 |
BEGIN TRAN UPDATE dbo.Righty SET Numbers = Numbers + 1; GO UPDATE dbo.Lefty SET Numbers = Numbers + 1; GO |
sp_BlitzLock is available in our free First Responder Kit.
To learn more about locking, blocking, and concurrency, check out my Mastering Query Tuning class.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Thanks for the short and helpful video on this stored procedure I had not used yet. I have a database for a 3rd party application that gets deadlocks frequently. The vendor says to ignore them but now I have a tool to see them and it is quite interesting.
i’ve seen this happen a lot on a server that was used by OLTP and reporting. Several hundred applications total with a few core tables
I wish developers would use something like this, Rather than thinking that the “database” is deadlocking.
Hi Brent! Thank you very much for your information.
I want to say to you troubleshooting deadlock is very hard.
For example when XEvents only shows two select statements in the XML information, How can the root of the problem be found?
Glad you liked it. To learn about that, watch the deadlocks module in the Mastering Query Tuning class.