patternsqlMinor
Seeing 2 exclusive locks on the same index - is this possible?
Viewed 0 times
thisseeingsametheexclusivepossibleindexlocks
Problem
Please help me understand how it is possible that I am seeing 2 exclusive locks on the same index. This is on SQL Server 2012 SP3. It is from within a vendor application and don't know how transaction settings are working. Oh, and this is on SELECTs!
Here is a picture from SQL Sentry's monitoring:
The deadlock graph details are here:
```
SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil >= @P3))
unknown
(@P0 varbinary(8000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime)SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil >= @P3))
SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil >= @P3))
unknown
(@P0 varbinary(8000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime)SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil >= @P3))
Here is a picture from SQL Sentry's monitoring:
The deadlock graph details are here:
```
SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil >= @P3))
unknown
(@P0 varbinary(8000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime)SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil >= @P3))
SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil >= @P3))
unknown
(@P0 varbinary(8000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime)SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil >= @P3))
Solution
Yes, you might have an exclusive rowlock or page lock on different pages or rows. In this case you have a key lock on two different parts of the index. See lock granuality https://technet.microsoft.com/en-us/library/ms189849(v=sql.105).aspx
Context
StackExchange Database Administrators Q#152768, answer score: 3
Revisions (0)
No revisions yet.