Content rating: intermediate/tips. We'll do an intro deadlocking blog sometime soon.
I had a bear of a deadlocking issue last week, and reached out to my SQL Tweeps for help. Jonathan Kehayias (blog, SQLSarg on twitter) responded with email, repros, and solutions. I was trying to think of a funny way to say thank you, or thumbs up, in a MidnightDBA way…but I couldn't come up with anything better than "Thanks, Jonathan….that's one full moon up for you!" And that's just lame, and a little bit surreal in the Yo Gabba Gabba way, so we'll leave it at, thanks SQLSarg!
Through profiler traces capturing deadlock graphs, I was able to narrow the deadlock down to a very, very specific piece of code – a stored procedure called UpdateOrder was deadlocking on an insert to the [Order] table. The deadlock graph (images forthcoming) showed Owner Mode Range S-S, and Request Mode Range I-N locks on the primary key for that table.
Do a search for "Range I-N" in books online, and you'll get the article on Key-Range Locking. The first thing that leaps out at you is that these are associated with the serializable isolation level. Now, my database uses the default READ COMMITTED isolation level, and the stored procedure itself wasn't specifically set for Serializable, so I was very confused. The solution to the first mystery: this SP is called as part of a BizTalk process, and BizTalk not only applies Serializable to all the code it calls, but it encapsulates it in a transaction. That'll be important a little bit later.
In the meantime, we're stuck with Serializable. That increases contention, but it shouldn't cause a deadlock by itself, so now we take a closer look at the code. This is somewhat simplified:
UPDATE @tmp
SET inOrder = 1
FROM @tmp tp
INNER JOIN [Order] op ON op.OrderID = @BookingNumber
INSERT INTO [Order]
( PolicyName ,
Text
)
SELECT DISTINCT
@BookingNumber ,
tp.[Message]
FROM @tmp tp
WHERE tp.inOrder= 0
The RANGE I-N lock clearly comes from the INSERT statement, but what about the RANGE S-S? If we look closely, we'll see that the UPDATE statement doesn an inner join on the [Order] table, which is to say, we're SELECTing from [Order] via that join. When we run two or more processes running this stored procedure, both procedures get shared locks for the JOIN [Order] statement, and they request insert locks (Range I-N) for the insert statement. Each proces is in one container transaction, so it can't let go of the shared lock before it also gets the insert lock. Boom, deadlock.
The three suggested solutions, in order of preference, are
-
Change the isolation level from serializable to Read Commited. I can't do that, since BizTalk is enforcing Serializable. Next option….
-
Move any pre-Insert SELECT (in my case, the update with the inner join) outside of the transaction that is performing the INSERT. This doesn't apply to me, also courtesy of BizTalk. Remember that encapsulating transaction we can't get out of? Yeah. So what we gotta do is….
-
Use an UPDLOCK hint. To quote Jonathan, the UPDLOCK hint will "increase the lock type to an update lock from a shared lock which blocks the other executions of the stored procedure until the transaction commits".
UPDATE @tmp
SET inOrder = 1
FROM @tmp tp
INNER JOIN [Order] op WITH (UPDLOCK) ON op.OrderID = @BookingNumber
…
Tah-dah! No more deadlocks. The update lock prevents any other process from getting their mitts on [Order] long enough to finish this update and the insert.
-Jen McCown