Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been staring at this problem for almost a week now. I'm getting a deadlock in some query, and I've reduced the problem to these reproducible steps:

Create a database with:

create table offering (
    Id int NOT NULL,
    CustomerName nvarchar(255) NULL,
    PRIMARY KEY CLUSTERED (Id asc))

-- notice how there is no index on CustomerName and it isn't unique, this
-- is important because it means that in serializable isolation key range
-- locks will be placed on the whole range of keys (that is all rows)

You're going to need two sessions for the next part. Execute this T-SQL:

Session #1:

set transaction isolation level serializable

begin tran

select * 
from offering with (updlock, rowlock)
where customername = 'hank'
-- notice how we're not committing the transaction
-- at this point, there is a single RangeS-U (fffffffff) lock on the key

Session #2:

set transaction isolation level serializable

begin tran

-- this will try to acquire (but fail) the RangeS-U (ffffffff) lock.
-- So until the transaction in session #1 commits this will block.
select * 
from offering with (updlock, rowlock)
where customername = 'harry'

back to Session #1:

-- The insert is important here, it seems to throw off the range locking
insert into offering values (1, 'hank')

commit tran
-- Session #1 releases RangeS-U (ffffffff), so it's available for
-- session #2 to take it.

-- At this point I'd expect session #2 to acquire the locks needed (ffffffff  
-- and the lock on the just inserted row "hank"), except...

begin tran

-- This session will try and succeed(!) to take the RangeS-U (*hank*) lock
-- Surprisingly, Session #2 now takes the RangeS-U (ffffffff) lock
-- Session #1 continues to try (and fail) to take the RangeS-U (ffffffff) lock
-- Session #2 continues to try (and fail) to take the RangeS-U (*hank*) lock
select * 
from offering with (updlock, rowlock)
where customername = 'hank'

And presto! Deadlock (this is using SQL Management Studio at least)

I've traced this and what I'm seeing is that session 1 and 2 both try to acquire range locks on the "infinity range" (aka ffffffff) and the newly inserted row, but in opposite directions: Resulting in the aforementioned deadlock.

Now I thought that the acquisition of range locks was supposed to be an atomic thing: you either get all of them, or you don't, but I can't find any documentation about this and now I'm not sure. What's supposed to happen here?

Edit

Created a microsoft connect item which can be found at https://connect.microsoft.com/SQLServer/feedback/details/836666/race-condition-or-bug-in-sql-server-using-serializable-til-with-updlock

share

migration rejected from stackoverflow.com Apr 2 at 2:40

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Paul White, Marian, ypercube, RolandoMySQLDBA, Max Vernon Apr 2 at 2:40

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Marian, ypercube, RolandoMySQLDBA, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

3  
Important - what version of SQL Server is this? You tagged it 2008, can you give the full version number? –  JNK Mar 19 at 19:15
    
@JNK I've reproduced this on several versions after posting this, including sql server express 2012 and sql server 2014 CTP2. I don't think specific build numbers matter. –  dvdvorle Mar 19 at 20:18
    
I have little knowledge about oracle and it's implementation of transaction isolation levels, but could someone try this out in an oracle db and post the results? –  dvdvorle Mar 30 at 21:15
1  
This did not repro for me on 2012 (build 3393) or 2008 R2 (build 4286). –  Paul White Apr 1 at 11:28
2  
@dvdvorle Voting to close as "too localized". I don't see how this can help any future reader, having in mind that it isn't reproduced by others and that you can simply avoid the problem with an index on ColumnName. –  ypercube Apr 1 at 15:03
show 2 more comments