October 10, 2016 at 10:32 am
I'm seeing some deadlocks that I can't explain - it seems that a select is requesting an an intent-exclusive lock on a table. How is that possible? I thought select only ever issued shared locks?
The database is running in the default READ COMMITTED isolation level.
This example (there are lots of similar occurrences, a select and an update being deadlocked - output posted at the bottom from SQL Monitor rather than native XML) shows a select with a shares (S) lock and an update with an intent exclusive (IX) lock, the update requests an S lock and the select, somehow, requests an IX lock?! Am I misinterpreting the data?
[Please excuse the code - this is an ugly legacy system I want to rewrite, but for now this is what I have to contend with.]
The update is issued directly from some Coldfusion code I have no control over.
The select is in a small procedure, no explicit transaction, just:
CREATE PROCEDURE [dbo].[<proc name>]
@AccountID VARCHAR(15)
,@SearchList VARCHAR(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF LEN(@SearchList) > 0
BEGIN
SELECT a.AccountID
,a.CompanyName
,a.City
,(
SELECT COUNT(*)
FROM Vehicles
WHERE AccountID = a.AccountID
AND STATUS = 1
) AS VehicleCount
FROM Accounts AS a
WHERE (
a.AccountID = @AccountID
OR a.AccountID IN (SELECT Item
FROM dbo.Split(@SearchList, ','))
)
ORDER BY a.CompanyName
,a.City;
END;
ELSE
BEGIN
SELECT a.AccountID
,a.CompanyName
,a.City
,(
SELECT COUNT(*)
FROM Vehicles
WHERE AccountID = a.AccountID
AND STATUS = 1
) AS VehicleCount
FROM Accounts AS a
WHERE a.AccountID = @AccountID
ORDER BY a.CompanyName
,a.City;
END;
END;
The deadlock output is as follows (I've edited out emails, names and IDs, but otherwise it's straight out of SQL Monitor):
deadlock-list
deadlock victim=process5696e08
process-list
process id=process5696e08 taskpriority=0 logused=0 waitresource=PAGE: 5:5:96510 waittime=8922 ownerId=26524355837 transactionname=SELECT lasttranstarted=2016-10-10T09:17:07.303 XDES=0x2b1e6dbb0 lockMode=S schedulerid=11 kpid=13112 status=suspended spid=92 sbid=0 ecid=46 priority=0 trancount=0 lastbatchstarted=2016-10-10T09:17:07.303 lastbatchcompleted=2016-10-10T09:17:07.303 clientapp=.Net SqlClient Data Provider hostname=<server name> hostpid=292228 isolationlevel=read committed (2) xactid=26524355837 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=<DBName>.<schema>.<Procname> line=27 stmtstart=2940 stmtend=4378 sqlhandle=0x03000500bd3de10afe65d80066a600000100000000000000
SELECT a.AccountID
,a.CompanyName
,a.City
,(
SELECT COUNT(*)
FROM Vehicles
WHERE AccountID = a.AccountID
AND STATUS = 1
) AS VehicleCount
FROM Accounts AS a
WHERE (
a.AccountID = @AccountID
OR a.AccountID IN (SELECT Item
FROM dbo.Split(@SearchList, ','))
)
ORDER BY a.CompanyName
,a.City;
inputbuf
process id=process10643642c8 taskpriority=0 logused=15580 waitresource=PAGE: 5:5:96509 waittime=4999 ownerId=26524354460 transactionname=UPDATE lasttranstarted=2016-10-10T09:17:06.540 XDES=0x163c133660 lockMode=IX schedulerid=1 kpid=12644 status=suspended spid=116 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-10-10T09:17:06.523 lastbatchcompleted=2016-10-10T09:17:06.523 clientapp=.Net SqlClient Data Provider hostname=<server name> hostpid=21788 loginname=<login name> isolationlevel=read committed (2) xactid=26524354460 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 sqlhandle=0x02000000380f3c38bbff623cfa62f3316dbb688d9cf54b31
Update Vehicles
Set Status = 0,
EditTime = {ts '2016-10-10 09:17:06'},
EditBy = '<email address>'
Where VehicleID IN (<list of vehicle ids>)
inputbuf
Update Vehicles
Set Status = 0,
EditTime = {ts '2016-10-10 09:17:06'},
EditBy = '<email address>'
Where VehicleID IN (<list of vehicle ids>)
resource-list
pagelock fileid=5 pageid=96510 dbid=5 objectname=<DBName>.<schema>.Vehicles id=lock266b7b800 mode=IX associatedObjectId=72063014887817216
owner-list
owner id=process10643642c8 mode=IX
waiter-list
waiter id=process5696e08 mode=S requestType=wait
pagelock fileid=5 pageid=96509 dbid=5 objectname=<DBName>.<schema>.Vehicles id=locka6a01d500 mode=S associatedObjectId=72063014887817216
owner-list
owner id=process5696e08 mode=S
waiter-list
waiter id=process10643642c8 mode=IX requestType=wait
October 10, 2016 at 10:41 am
You're misreading the deadlock graph.
The session running the select holds an S lock , and it's requesting an S lock.
The select is process5696e08
resource-list
pagelock fileid=5 pageid=96510 dbid=5 objectname=<DBName>.<schema>.Vehicles id=lock266b7b800 mode=IX associatedObjectId=72063014887817216
owner-list
owner id=process10643642c8 mode=IX
waiter-list
waiter id=process5696e08 mode=S requestType=wait
pagelock fileid=5 pageid=96509 dbid=5 objectname=<DBName>.<schema>.Vehicles id=locka6a01d500 mode=S associatedObjectId=72063014887817216
owner-list
owner id=process5696e08 mode=S
waiter-list
waiter id=process10643642c8 mode=IX requestType=wait
The session that holds and is requesting the IX locks is that update. It's process10643642c8
resource-list
pagelock fileid=5 pageid=96510 dbid=5 objectname=<DBName>.<schema>.Vehicles id=lock266b7b800 mode=IX associatedObjectId=72063014887817216
owner-list
owner id=process10643642c8 mode=IX
waiter-list
waiter id=process5696e08 mode=S requestType=wait
pagelock fileid=5 pageid=96509 dbid=5 objectname=<DBName>.<schema>.Vehicles id=locka6a01d500 mode=S associatedObjectId=72063014887817216
owner-list
owner id=process5696e08 mode=S
waiter-list
waiter id=process10643642c8 mode=IX requestType=wait
Looks like a pretty standard reader-writer deadlock. Maybe look at indexing, could be the indexing doesn't support the query (the select). Updates will always take IX at the page and table level if they're taking a row-level X lock, but it could be that the select's taking too-wide a lock and could be tuned to take row-level S locks and page and table IS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2016 at 10:50 am
Thanks Gail,
I can see where I was getting confused, it all makes sense now.
I've not spent any time digging into deadlocks before, so thanks for your help. I'll do better next time 🙂
Cheers,
Dave.
October 10, 2016 at 12:29 pm
No worries, deadlocks can get fairly hairy.
Shameless self-promotion: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply