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:
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
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?
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