July 14, 2010 at 2:00 am
We are experiencing deadlocks with several 'select' statements acquiring shared locks.
To overcome this we switched to 'read committed snapshot' but still I see deadlocks happening.
I ran this SQL to find locks currently happening, and it shows shared locks being acquired by 'select' statements.
How do I eliminate these deadlocks. We use 'nolock' hints as the last resort in many cases, I dont want to add more and more of these hints to avoid dirty read etc..
SELECT spid,
TEXT AS [query],
request_mode,
request_type AS LOCK,
request_status,
sys.dm_tran_locks.resource_type AS object_type,
Db_name(sysprocesses.dbid) AS dbname,
cpu,
memusage,
physical_io,
status AS query_status,
loginame,
hostname,
lastwaittype,
last_batch,
cmd,
program_name,
open_tran,
Db_name(sys.dm_tran_locks.resource_database_id) AS locked_database_name
FROM sys.dm_tran_locks,
sys.sysprocesses
OUTER APPLY Fn_get_sql(sql_handle)
WHERE spid > 50
AND sys.dm_tran_locks.request_session_id = spid
ORDER BY request_mode DESC
July 14, 2010 at 7:02 am
Even under READ COMMITTED SNAPSHOT, some operations will still take shared locks. The engine enforces this, and there is nothing you can do to prevent it (even the magic NOLOCK will not work). One example (of many) is where the engine has to check a foreign key relationship as part of the plan.
Anyway, I'm not saying that's your issue, because there is not enough information to draw that conclusion. It's just an observation at this stage - there are many other possibilities.
To aid diagnosis, you need to capture some more information about an example deadlock for us. You can find details of how to do this, and other useful information, in the following links:
http://msdn.microsoft.com/en-us/library/ms177433.aspx
http://msdn.microsoft.com/en-us/library/ms178104.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 14, 2010 at 7:16 am
What version of SQL Server is this on? If it is SQL 2000, turn on Trace Flag 1204 and it will write a deadlock graph to the ErrorLog when a deadlock occurs which has some of the details associated with the root cause. If it is SQL 2005, turn on Trace Flag 1222 which is an updated version of the 1204 flag used in 2000 that provides a more detailed graph for troubleshooting. If it is SQL Server 2008, query the system_health session and the graphs will be available in the deadlock events.
Using SQL Profiler to Resolve Deadlocks in SQL Server [/url]
Retrieving Deadlock Graphs with SQL Server 2008 Extended Events[/url]
Once you have the deadlock graph, post it back here and we can help you troubleshoot it further.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
July 14, 2010 at 7:26 am
Despite the current debate on Twitter, I'm pretty sure this is a standard deadlock. We'll know for sure when we get the deadlock details, I guess 🙂
Also, see netflix's other active thread in this forum - about shared locks being taken under row-versioning isolation levels... 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 14, 2010 at 2:26 pm
Thanks everyone for your valuable ideas and spending time to look at my post, I appreciate it. I am running out of ideas and adding nolocks in some cases. Underlying fact is read committed snapshot is stillign letting shared locks to be acquired. I have not tried the snapshot isolation, but in my limited tests that also behaves the same.
This is on SQL Srvr 2005, SP2 mode 90,
Here is one instance of a deadlock showing a 'select statement', having shared lock 'S'
2010-07-01 11:11:22.85 spid15s deadlock-list
2010-07-01 11:11:22.85 spid15s deadlock victim=processfe9888
2010-07-01 11:11:22.85 spid15s process-list
2010-07-01 11:11:22.85 spid15s process id=processfe9888 taskpriority=0 logused=0 waitresource=RID: 5:1:4987752:1 waittime=234 ownerId=24247615 transactionname=user_transaction lasttranstarted=2010-07-01T11:11:22.607 XDES=0x24071258 lockMode=S schedulerid=10 kpid=5956 status=suspended spid=53 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2010-07-01T11:11:22.623 lastbatchcompleted=2010-07-01T11:11:22.607 clientapp=i-net OPTA 2000 hostname=qedmapp1 hostpid=197 loginname=topazuser isolationlevel=serializable (4) xactid=24247615 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2010-07-01 11:11:22.85 spid15s executionStack
2010-07-01 11:11:22.85 spid15s frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x0200000055caef23196d2a9858fe88a84b33e2a3eb7586d8
2010-07-01 11:11:22.85 spid15s select count ( * ) REC_CNT from RECORD_LOCK where LOCK_END_TIME is null and LOCKEE_SID = @0 and LOCKEE_TYPE = @1 and LOCKEE_SUBTYPE = @2 and LOCK_TYPE = @3 and LOCKER_ID = @4
2010-07-01 11:11:22.85 spid15s frame procname=adhoc line=1 sqlhandle=0x0200000000cc4e1d11ac8630a25c26d6bec7e09e96ba396d
2010-07-01 11:11:22.85 spid15s SELECT count(*) REC_CNT FROM RECORD_LOCK WHERE LOCK_END_TIME IS NULL AND LOCKEE_SID = 14208235 AND LOCKEE_TYPE = 99 AND LOCKEE_SUBTYPE = 99 AND LOCK_TYPE = 2 AND LOCKER_ID = '885FEDB0FA86FF0A2BD1895EB36B5F6E'
2010-07-01 11:11:22.85 spid15s inputbuf
2010-07-01 11:11:22.85 spid15s SELECT count(*) REC_CNT FROM RECORD_LOCK WHERE LOCK_END_TIME IS NULL AND LOCKEE_SID = 14208235 AND LOCKEE_TYPE = 99 AND LOCKEE_SUBTYPE = 99 AND LOCK_TYPE = 2 AND LOCKER_ID = '885FEDB0FA86FF0A2BD1895EB36B5F6E'
2010-07-01 11:11:22.85 spid15s process id=process30a53d8 taskpriority=0 logused=84 waitresource=KEY: 5:72057594055557120 (8c04beb6a421) waittime=234 ownerId=24247617 transactionname=DELETE lasttranstarted=2010-07-01T11:11:22.623 XDES=0xed09458 lockMode=X schedulerid=12 kpid=2888 status=suspended spid=56 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-07-01T11:11:22.607 lastbatchcompleted=2010-07-01T11:11:22.607 clientapp=i-net OPTA 2000 hostname=qedmapp1 hostpid=197 loginname=topazuser isolationlevel=read committed (2) xactid=24247617 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2010-07-01 11:11:22.85 spid15s executionStack
2010-07-01 11:11:22.85 spid15s frame procname=adhoc line=1 stmtstart=44 sqlhandle=0x020000005bee7a319011f519b50437d785085f2673f76cc4
2010-07-01 11:11:22.85 spid15s delete from RECORD_LOCK where LOCKEE_SID = @0 and LOCKEE_TYPE = @1 and LOCK_TYPE = @2
2010-07-01 11:11:22.85 spid15s frame procname=adhoc line=1 stmtstart=6 sqlhandle=0x0200000088f81704b906d8dc54f0af3bd8b246d0ccf528e1
2010-07-01 11:11:22.85 spid15s DELETE FROM RECORD_LOCK WHERE LOCKEE_SID = 14208235 AND LOCKEE_TYPE = 99 AND LOCK_TYPE = 2
2010-07-01 11:11:22.85 spid15s inputbuf
2010-07-01 11:11:22.85 spid15s DELETE FROM RECORD_LOCK WHERE LOCKEE_SID = 14208235 AND LOCKEE_TYPE = 99 AND LOCK_TYPE = 2
2010-07-01 11:11:22.85 spid15s resource-list
2010-07-01 11:11:22.85 spid15s keylock hobtid=72057594055557120 dbid=5 objectname=Quantimdb.dbo.RECORD_LOCK indexname=IX_RECORD_LOCK id=lock526f3f40 mode=RangeS-S associatedObjectId=72057594055557120
2010-07-01 11:11:22.85 spid15s owner-list
2010-07-01 11:11:22.85 spid15s owner id=processfe9888 mode=RangeS-S
2010-07-01 11:11:22.85 spid15s waiter-list
2010-07-01 11:11:22.85 spid15s waiter id=process30a53d8 mode=X requestType=wait
2010-07-01 11:11:22.85 spid15s ridlock fileid=1 pageid=4987752 dbid=5 objectname=Quantimdb.dbo.RECORD_LOCK id=lock5bfd1080 mode=X associatedObjectId=56988672720896
2010-07-01 11:11:22.85 spid15s owner-list
2010-07-01 11:11:22.85 spid15s owner id=process30a53d8 mode=X
2010-07-01 11:11:22.85 spid15s waiter-list
2010-07-01 11:11:22.85 spid15s waiter id=processfe9888 mode=S requestType=wait
July 14, 2010 at 2:32 pm
A more recent example
2010-07-08 14:17:47.09 spid23s deadlock-list
2010-07-08 14:17:47.09 spid23s deadlock victim=process89bc48
2010-07-08 14:17:47.09 spid23s process-list
2010-07-08 14:17:47.09 spid23s process id=process89bc48 taskpriority=0 logused=0 waitresource=KEY: 5:72057594062176256 (5401726f0c88) waittime=3062 ownerId=354296750 transactionname=SELECT lasttranstarted=2010-07-08T14:17:44.027 XDES=0xecf44e8 lockMode=RangeS-S schedulerid=1 kpid=4664 status=suspended spid=84 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2010-07-08T14:17:44.027 lastbatchcompleted=2010-07-08T14:17:44.027 clientapp=i-net OPTA 2000 hostname=testsrv3 hostpid=197 loginname=topazuser isolationlevel=serializable (4) xactid=354296750 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2010-07-08 14:17:47.09 spid23s executionStack
2010-07-08 14:17:47.09 spid23s frame procname=adhoc line=1 stmtstart=84 sqlhandle=0x02000000d7c49d112b61ae214ba7f42d26ec9c2ef4cdf316
2010-07-08 14:17:47.09 spid23s select b . item_value , a . format , a . preference_sid from preference a , preference_value b , preference_sharing c where a . preference_sid = c . preference_sid and c . sharer_sid = @0 and a . preference_sid = b . preference_sid and a . preference_name = @1 and usage_type = @2 order by a . preference_sid desc , b . item_index
2010-07-08 14:17:47.09 spid23s frame procname=adhoc line=1 sqlhandle=0x020000000a7cbd2e9bb5d2ddcd63fc231e078cb4f6a2e2b4
2010-07-08 14:17:47.09 spid23s SELECT b.item_value, a.format, a.preference_sid FROM preference a,preference_value b, preference_sharing c WHERE a.preference_sid = c.preference_sid AND c.sharer_sid = 1 AND a.preference_sid = b.preference_sid AND a.preference_name = 'MRN Format' AND usage_type = 'F' ORDER BY a.preference_sid DESC, b.item_index
2010-07-08 14:17:47.09 spid23s inputbuf
2010-07-08 14:17:47.09 spid23s SELECT b.item_value, a.format, a.preference_sid FROM preference a,preference_value b, preference_sharing c WHERE a.preference_sid = c.preference_sid AND c.sharer_sid = 1 AND a.preference_sid = b.preference_sid AND a.preference_name = 'MRN Format' AND usage_type = 'F' ORDER BY a.preference_sid DESC, b.item_index
2010-07-08 14:17:47.09 spid23s process id=processfe96a8 taskpriority=0 logused=1488 waitresource=KEY: 5:72057594062176256 (ffffffffffff) waittime=3062 ownerId=354296746 transactionname=user_transaction lasttranstarted=2010-07-08T14:17:44.027 XDES=0x250959f0 lockMode=RangeI-N schedulerid=10 kpid=2692 status=suspended spid=81 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-07-08T14:17:44.027 lastbatchcompleted=2010-07-08T14:17:44.027 clientapp=i-net OPTA 2000 hostname=testsrv3 hostpid=197 loginname=topazuser isolationlevel=read committed (2) xactid=354296746 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2010-07-08 14:17:47.09 spid23s executionStack
2010-07-08 14:17:47.09 spid23s frame procname=adhoc line=1 stmtstart=30 sqlhandle=0x020000005cba272b33372e8e12e37c1593c8a7425b213399
2010-07-08 14:17:47.09 spid23s insert into preference_sharing ( preference_sid , sharer_sid ) values ( @0 , @1 )
2010-07-08 14:17:47.09 spid23s frame procname=adhoc line=1 stmtstart=2 sqlhandle=0x02000000b4c98d290c7b50dc185e610b625009b584deceb5
2010-07-08 14:17:47.09 spid23s INSERT INTO preference_sharing (preference_sid, sharer_sid) VALUES (122890,191)
2010-07-08 14:17:47.09 spid23s inputbuf
2010-07-08 14:17:47.09 spid23s INSERT INTO preference_sharing (preference_sid, sharer_sid) VALUES (122890,191)
2010-07-08 14:17:47.09 spid23s resource-list
2010-07-08 14:17:47.09 spid23s keylock hobtid=72057594062176256 dbid=5 objectname=Quantimdb.dbo.PREFERENCE_SHARING indexname=IX_PREFERENCE_SHARING id=lock2370fec0 mode=RangeS-S associatedObjectId=72057594062176256
2010-07-08 14:17:47.09 spid23s owner-list
2010-07-08 14:17:47.09 spid23s owner id=process89bc48 mode=RangeS-S
2010-07-08 14:17:47.09 spid23s waiter-list
2010-07-08 14:17:47.09 spid23s waiter id=processfe96a8 mode=RangeI-N requestType=wait
2010-07-08 14:17:47.09 spid23s keylock hobtid=72057594062176256 dbid=5 objectname=Quantimdb.dbo.PREFERENCE_SHARING indexname=IX_PREFERENCE_SHARING id=lock3b3b7e80 mode=X associatedObjectId=72057594062176256
2010-07-08 14:17:47.09 spid23s owner-list
2010-07-08 14:17:47.09 spid23s owner id=processfe96a8 mode=X
2010-07-08 14:17:47.09 spid23s waiter-list
2010-07-08 14:17:47.09 spid23s waiter id=process89bc48 mode=RangeS-S requestType=wait
July 14, 2010 at 2:42 pm
You have one that is a SELECT-DELETE deadlock and one that is a SELECT-INSERT deadlock. The problem is most likely going to be that your SELECT statements have a Bookmark Lookup causing it to lock the Non-clustered index and then require a lock on the Clustered index to perform the lookup, while the INSERT/DELETE operation exclusively locks the Clustered Index and requires a subsequent Exclusive Lock on the Non-clustered index to complete. I blogged about this type of deadlock with graphical explanation:
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
July 14, 2010 at 2:46 pm
Whew! Standard deadlock. You've got INSERT & DELETE statements in there. So the deadlocks are not from the shared locks alone. There's more at work, a lot more.
So, standard questions for deadlocks, it looks like these are parts of multi-statement transactions. Are all the tables accessed in the same order between these transactions? Have you looked at possibly putting upd_lock on the select statements to prevent later lock escalation? Let's see, there are more, but this looks like pretty traditional deadlock issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 17, 2015 at 8:04 pm
The deadlock victim is running at the serializable isolation level. Are you sure you need this. I would suggest READ COMMITTED.
May 17, 2015 at 8:49 pm
bray_tim (5/17/2015)
The deadlock victim is running at the serializable isolation level. Are you sure you need this. I would suggest READ COMMITTED.
Before everyone drops a ton of bricks on you for responding to a post nearly 5 years old, thanks for the good advice. It's still appropriate even after 5 years.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2015 at 5:31 am
Thanks Jeff, having one of those days!
May 18, 2015 at 8:44 am
bray_tim (5/18/2015)
Thanks Jeff, having one of those days!
Absolutely not to worry. My comment wasn't directed at you. It was directed at the people that I know have tons of bricks. 😀
Your post was totally appropriate here and should help other people with the same problem. I hope you have more days like this!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply