I need a deadlock in SQL Server 2008R2, Win 7 64-bit

Question

Post reply

I need a deadlock in SQL Server 2008R2, Win 7 64-bit

LisaGB

SSC-Addicted

Points: 418
More actions
December 2, 2011 at 6:35 am

#391013

Hello all.
This is a homework question, but I need help with the SETUP of the question, not the answer.
The homework is to set up a trace to show a deadlock, and the book includes code to force the deadlock. However, my setup is stubbornly refusing to have a deadlock (I swear the only time I'll never see a deadlock is when I want one!)
The setup in the book asks you to set up the trace for the deadlock (I did this, so no help needed there.)
The book uses AdventureWorks; I have AdventureWorks2008R2.
I am supposed to open a "new query 1" for the database, and execute the following code:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
UPDATE Production.Product
SET ReorderPoint = 600
WHERE ProductID = 316
I did this, and in the messages window, got (1 row(s) affected)
Then, I was to open "new query window 2" and execute the following code:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
UPDATE Production.ProductInventory
SET Quantity = 532
WHERE ProductID = 316
AND LocationID = 5
SELECT Name, ReorderPoint
FROM Production.Product
WHERE ProductID = 316
I tried this, and the query was still trying to execute after 21 minutes. I tried running DBCC OPENTRAN - this should have showed an open transaction at least for the first query, but stated that there were no open transactions.
There is a third part to the code: once you execute the above codes, you are supposed to go BACK to "new query 1" window and run the following:
SELECT ProductID, LocationID, Shelf, Bin, Quantity, ModifiedDate
FROM Production.ProductInventory
WHERE ProductID = 316
AND LocationID = 5
I have tried "Googling" and have found plenty on identifying deadlocks, avoiding them, etc. but not much about creating them. I don't know enough (yet) to try to create one on my own besides trying some other variation of the code above. Does anyone have any ideas that might help? Thanks for your time!

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Here's a blog with step by step on what you are trying to do.

http://blogs.msdn.com/b/bartd/archive/2006/09/13/751343.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LisaGB SSC-Addicted Points: 418 More actions · Answer 2

LisaGB

SSC-Addicted

Points: 418

December 2, 2011 at 7:47 am

#1415898

Thank you - that worked like a charm!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Good to hear

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

I need help with the SETUP of the question, not the answer.

I appreciate it very much. It's something we see very rarely... 😀

LisaGB SSC-Addicted Points: 418 More actions · Answer 5

I have found in many classes/situations that when I'm having trouble getting the answer, the more work I have to do to figure it out, the better I'll remember what I'm learning. I would normally try to recreate the setup as well, but I know just enough to be dangerous - which means I don't want to try blowing up my database (even test) without having a better grasp 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

My favourite way to generate a deadlock is like this:

Window 1

BEGIN TRANSACTION

UPDATE <someTable> SET <whatever> WHERE <pk column> = @value1

WAITFOR DELAY '00:00:05'

SELECT * FROM <SomeTable> WHERE <pk column> = @value2

Window 2

BEGIN TRANSACTION

UPDATE <someTable> SET <whatever> WHERE <pk column> = @value2

WAITFOR DELAY '00:00:05'

SELECT * FROM <SomeTable> WHERE <pk column> = @value1

The waitfor means that the timing isn't critical. Run the within 5 sec of each other and they will deadlock.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Thanks Gail. I like that method.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events