February 24, 2011 at 2:38 am
I was wondering if someone could give me some pointers as to how I should go about investigating what appears to be a single-resource deadlock. Deadlock info below:
Node:1
KEY: 5:72057594839564288 (840056a4f35f) CleanCnt:2 Mode:X Flags: 0x1
Grant List 1:
Owner:0x000000064D901440 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:85 ECID:0 XactLockInfo: 0x00000003692E99B0
SPID: 85 ECID: 0 Statement Type: INSERT Line #: 28
Input Buf: Language Event: (@p0 int,@p1 uniqueidentifier,@p2 int,@p3 nvarchar(4000),@p4 binary(64),@p5 datetime,@p6 nvarchar(4000),@p7 nvarchar(4000),@p8 varchar(8000),@p9 nvarchar(4000),@p10 datetime,@p11 datetime,@p12 datetime,@p13 datetime,@p14 int,@p15 int,@p16 int,@p17 bit,@p1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000006A62AF780 Mode: RangeS-S SPID:102 BatchID:0 ECID:0 TaskProxy:(0x000000034DD78538) Value:0x48be8800 Cost:(0/0)
Node:2
KEY: 5:72057594840154112 (590087058dc2) CleanCnt:2 Mode:RangeS-S Flags: 0x1
Grant List 3:
Owner:0x0000000254DEBD00 Mode: RangeS-S Flg:0x40 Ref:0 Life:02000000 SPID:102 ECID:0 XactLockInfo: 0x00000006A62AF7C0
SPID: 102 ECID: 0 Statement Type: SELECT Line #: 15
Input Buf: Language Event: (@p0 int,@p1 uniqueidentifier,@p2 int,@p3 nvarchar(4000),@p4 binary(64),@p5 datetime,@p6 nvarchar(4000),@p7 nvarchar(4000),@p8 varchar(8000),@p9 nvarchar(4000),@p10 datetime,@p11 datetime,@p12 datetime,@p13 datetime,@p14 int,@p15 int,@p16 int,@p17 bit,@p1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000003692E9970 Mode: RangeI-N SPID:85 BatchID:0 ECID:0 TaskProxy:(0x00000006FC59E538) Value:0x56f7b2c0 Cost:(0/1904)
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x00000006A62AF780 Mode: RangeS-S SPID:102 BatchID:0 ECID:0 TaskProxy:(0x000000034DD78538) Value:0x48be8800 Cost:(0/0)
February 24, 2011 at 2:56 am
-Going my snippet of log posted it seems which is INSERT statement pasted in first part, so application may have to re-run the transaction at later point of time.
- Assuming the application is returned this message to run the insert after some time.
Some Questions...
What type of isolation level is set up on the database ?
Are these deadlocks frequent ?
Do you have SP\SQL script which may issue INSERT\Update ?
Cheers
Sat
Cheer Satish 🙂
February 24, 2011 at 3:08 am
That looks like an ordinary conversion deadlock to me, which can be fixed by accessing objects in a consistent order or by using the UPDLOCK hint appropriately. You will need to post the code the two connections were running, and the reasons for using the serializable isolation level if you want a deeper analysis. Table and index definitions might be helpful too.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2011 at 3:10 am
Answers to your questions:
1. READ COMMITTED.
2. That particular deadlock is rare, but it's potentially a large problem.
3. It's a LINQ to SQL application so the INSERTs all look like:
exec sp_executesql N'INSERT INTO [TableName]([COL1],[COL2]...[COLN]) VALUES (@p0, @p1... @p2)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
February 24, 2011 at 3:12 am
SQLKiwi:
The INSERT and SELECT are from 1 table with 1 index (the clustered one), so how does the UPDLOCK come into play here?
February 24, 2011 at 3:21 am
Alex Webber (2/24/2011)
The INSERT and SELECT are from 1 table with 1 index (the clustered one), so how does the UPDLOCK come into play here?
Typically, the broken pattern is to SELECT to see if a record exists, and then INSERT if not. This easily results in a conversion deadlock (depending on timing) unless the SELECT and INSERT are inside the same transaction and the SELECT takes an UPDLOCK.
The two queries in your deadlock trace are running at SERIALIZABLE by the way.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2011 at 3:32 am
Thanks for the replies SQLKiwi. The table in question is clustered/pk'ed on an identity column and thus there is no "SELECT to see if exists and then INSERT" pattern going on. Excuse the noobishness, but which part of that trace indicates both queries are running SERIALIZABLE?
February 24, 2011 at 3:46 am
Alex Webber (2/24/2011)
Thanks for the replies SQLKiwi. The table in question is clustered/pk'ed on an identity column and thus there is no "SELECT to see if exists and then INSERT" pattern going on. Excuse the noobishness, but which part of that trace indicates both queries are running SERIALIZABLE?
Range locks (like RangeS-S) are only taken under SERIALIZABLE isolation. See 'Key-Range Locking' in Books Online. SPID 102 in the deadlock output was running a SELECT.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2011 at 4:10 am
Thanks for pointing me at that article, makes sense.
However what I don't get is why range locks are coming into play at all when it's an IDENTITY based table and the two queries in question are based on inserting ONE row and selecting ONE row (by key). Also how can you translate those KEY values (the massive 72057594839564288 and 72057594840154112) into the ranges?
February 24, 2011 at 4:29 am
Alex Webber (2/24/2011)
However what I don't get is why range locks are coming into play at all when it's an IDENTITY based table and the two queries in question are based on inserting ONE row and selecting ONE row (by key).
INSERT...VALUES and SELECT SCOPE_IDENTITY wouldn't take range locks, but something is. I would trace one of these sessions and see exactly what calls it is making within the transaction. You might also want to speak to the developer, if possible, to ask if serializable really is needed, or if it just a consequence of the data access technique they are using (e.g. System.Transaction in ADO.NET). Also check that the table concerned does not have an indexed view referencing it - indexed view maintenance runs at serializable temporarily, regardless of the isolation level effective for the user query.
Also how can you translate those KEY values (the massive 72057594839564288 and 72057594840154112) into the ranges?
That's reasonably straightforward, though tedious to explain and do, and I'm not sure it would help you much.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2011 at 4:38 am
I am one of the developers 😉 (accidental DBA scenario). And yes it's a consequence of the data access tech, more specifically the ORM tool (LINQ-to-SQL). SERIALIZABLE isn't needed, so maybe I'll provide a work-around for that. No indexed views on that table.
As regards the KEY to RANGE translations it would be useful, as we have decent logging so I might be able to marry them up and eventually get back to the exact query executions. So if you can point me at something to achieve this then that would be great.
Thanks again for your time, Alex.
February 24, 2011 at 5:02 am
Alex Webber (2/24/2011)
I am one of the developers 😉 (accidental DBA scenario). And yes it's a consequence of the data access tech, more specifically the ORM tool (LINQ-to-SQL). SERIALIZABLE isn't needed, so maybe I'll provide a work-around for that. No indexed views on that table.
That's a puzzle then - definitely worth a code check / SQL trace to see exactly what statements are being executed within the transaction.
As regards the KEY to RANGE translations it would be useful, as we have decent logging so I might be able to marry them up and eventually get back to the exact query executions. So if you can point me at something to achieve this then that would be great.
The Range S-S KEY locks (from sys.dm_tran_locks) will have a couple of values associated with them - the associated entity id is the HoBt (sys.partitions) of the page, and the resource description will be a lock hash. The lock hash is the same value returned by the undocumented %%LockRes%% pseudo-function. The format of the query needed to obtain lock hashes is SELECT something, %%LockRes%% FROM table WITH (INDEX(index_name)). It's tedious, but possible to match the lock hashes that way - but be sure to specify the same access method (index path) as the original query using the INDEX hint mentioned.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2011 at 5:08 am
Demo:
SET STATISTICS XML OFF;
SET NOCOUNT ON;
CREATE TABLE S (A INT IDENTITY PRIMARY KEY);
GO
INSERT S DEFAULT VALUES
GO 1000
BEGIN TRANSACTION
SELECT * FROM S WITH (SERIALIZABLE) WHERE A BETWEEN 501 AND 502;
SELECT resource_description, resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
AND resource_type = 'KEY'
AND request_mode = 'RangeS-S';
-- I got these resource descriptions:
--(f50041acc16e)
--(f700ca64c8c4)
--(f600af03747c)
SELECT IDENTITYCOL
FROM S WITH (INDEX(1))
WHERE %%LockRes%% IN
(
'(f50041acc16e)',
'(f700ca64c8c4)',
'(f600af03747c)'
);
ROLLBACK;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2011 at 7:58 am
Thanks a lot for all this, it's been very helpful indeed 🙂
February 24, 2011 at 8:21 am
Just to add, I can't find anywhere that we're using SERIALIZABLE isolation level (we have no TransactionScope usage either) but after googling around there appear to be one or two posts that suggest key-range-locks can occur whilst in READ COMMITTED isolation level. Like the top answer here:
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply