question

bhuvangu avatar image
bhuvangu asked

unexpected lock on index in mssql in select queries

I keep getting Caused by: java.sql.SQLException: Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. So i turned on the mssql logging using DBCC TRACEON(1222,-1) DBCC TRACEON(1204,-1) and following deadlock info is printed in logs: These line indicate that a deadlock has been detected, and process 1 has been chosen as victim. process-list deadlock victim=PROCESS_ID_1 deadlock-list ------------------------------- Now following line gives details of process_id_1, Ref[1] is the query that we want to execute...Note: its a select query on a table name `NameSequence` and `namesequence0_.container_id` is a foreign key from a table name `vfditem`. Ref[4] says that it is waiting for a resource and is suspended [1] (@P0 nvarchar(4000))select namesequen0_.id as id1_47_ namesequen0_.container_id as containe9_47_ namesequen0_.increment_ as incremen2_47_ namesequen0_.nextValue as nextValu3_47_ namesequen0_.numChars as numChars4_47_ namesequen0_.padChar as padChar5_47_ namesequen0_.scope as scope6_47_ namesequen0_.sequenceType as sequence7_47_ namesequen0_.uppercase as uppercas8_47_ from NameSequence namesequen0_ where (namesequen0_.scope like @P0 ) and (namesequen0_.container_id is null) [2] frame procname=adhoc line=1 stmtstart=40 sqlhandle=0x020000005734e00acfb3060d49cc0e8565acb3b105807744 [3] executionStack [4] process id=PROCESS_ID_1 taskpriority=0 logused=1248 waitresource=KEY: 14:72057594053459968 (95c9fddfaf17) waittime=3506 ownerId=13645568 transactionname=implicit_transaction lasttranstarted=2015-06-02T07:52:41.660 XDES=0xa30dd950 lockMode=S schedulerid=1 kpid=1848 status=suspended spid=68 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-06-02T07:52:41.663 lastbatchcompleted=2015-06-02T07:52:41.660 clientapp=jTDS hostname=QALAB6 hostpid=123 loginname=qalab6_nrm isolationlevel=read committed (2) xactid=13645568 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 --------------- Now following line give details of `process_id_2` which is in deadlock with `process_id_1` Ref[1] Give the query `process_id_2` want to run...Note its a select query on a table name `vfditem` and `card0_.namedWithSequence_id` is a foreign key from table name `NameSequence` Ref[4] says that it is waiting for a resource and is suspended [1]select card0_.id as id2_77_ card0_.autoDisplayName as autoDisp3_77_ card0_.autoPartialName as autoPart4_77_ card0_.namedWithSequence_id as namedWi38_77_ card0_.namedWithSequenceValue as namedWit5_77_ card0_.userDisplayName as userDisp6_77_ card0_.userPartialName as userPart7_77_ card0_.lifeCycleState_id as lifeCyc40_77_ card0_.model_id as model41_77_ card0_.outOfService as outOfSe12_77_ card0_.bottomClearance as bottomC13_77_ card0_.leftClearance as leftCle14_77_ card0_.rightClearance as rightCl15_77_ card0_.topClearance as topClea16_77_ card0_.ncmElementKey as ncmElem24_77_ from VfdItem card0_ where card0_.DTYPE='Card' and card0_.ncmElementKey= @P0 [2]frame procname=adhoc line=1 stmtstart=40 sqlhandle=0x02000000386ba721896ce39b0b7c9d01df11539c0d843c83 [3]executionStack [4]process id=PROCESS_ID_2 taskpriority=0 logused=62048 waitresource=KEY: 14:72057594051297280 (fadae9b0c9d3) waittime=3333 ownerId=13645330 transactionname=implicit_transaction lasttranstarted=2015-06-02T07:52:39.570 XDES=0xbf9f5950 lockMode=S schedulerid=1 kpid=5344 status=suspended spid=66 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-06-02T07:52:41.833 lastbatchcompleted=2015-06-02T07:52:41.813 clientapp=jTDS hostname=QALAB6 hostpid=123 loginname=qalab6_nrm isolationlevel=read committed (2) xactid=13645330 currentdb=14 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 ------------------------ Now following lines prints all the resource that are locked and by whom and who are waiting for that resource. [11] waiter id=PROCESS_ID_2 mode=S requestType=wait [10] waiter-list [9] owner id=PROCESS_ID_1 mode=X [8] owner-list [7] keylock hobtid=72057594051297280 dbid=14 objectname=qalab6_nrm.dbo.vfditem indexname=vfditem_pk id=lock85330d00 mode=X associatedObjectId=72057594051297280 [6] waiter id=PROCESS_ID_1 mode=S requestType=wait [5] waiter-list [4] owner id=PROCESS_ID_2 mode=X [3] owner-list [2] keylock hobtid=72057594053459968 dbid=14 objectname=qalab6_nrm.dbo.namesequence indexname=NameSequence_PK id=lock848c3380 mode=X associatedObjectId=72057594053459968 [1] resource-list This translates to that [1] the resource `qalab6_nrm.dbo.namesequence indexname=NameSequence_PK` is locked by `process_id_2` in exclusive lock mode and `process_id_1` is waiting on it. [2] the resource `qalab6_nrm.dbo.vfditem indexname=vfditem_pk` is locked by `process_id_1` in exclusive lock mode and `process_id_2` is waiting on it. ----------- **BINGO** We can see how its been deadlocked ------------- **NOW the weird part** If we look closely `process_id_1` is trying to run a select query from a table name `NameSequence` and column `NameSequence.container_id` is foreign key from table `vfditem` and `process_id_1` has already acquired a lock on `indexname=vfditem_pk of vfditem table` **[1] Why would `process_is_1` acquire a lock on index of vfditem ?** further `process_id_2` is trying to run a select query from a table name `vfditem` and column `vfditem.namesequence_id` is foreign key from table `NameSequence` and `process_id_2` has already acquired a lock on `indexname=NameSequence_PK of Namesequence table` **[2] Why would `process_is_2` acquire a lock on index of Namesequence?**
sql-server-2008deadlockresource-database
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Venkataraman avatar image
Venkataraman answered
I surmise that, there is DML in the process in addition to SELECT. There is no proper covering index present and due to that SQL Server has to read from Primary key for the data (Key Lookup). I would suggest you to read https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/ for more information on tracking the deadlock.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Looks possible. The stmtstart values in both cases are 40 - there could be statements prior to this taking the X locks. @bhuvangu can you capture the whole batch?
0 Likes 0 ·
i am also looking for how to capture all sql run in that transactions...do you know how to capture the whole batch @kev-riley
0 Likes 0 ·
You could use the sqlhandle in [Ref2] to query the DMV select [text] from sys.dm_exec_sql_text (0x020000005734e00acfb3060d49cc0e8565acb3b105807744) select [text] from sys.dm_exec_sql_text (0x02000000386ba721896ce39b0b7c9d01df11539c0d843c83) Obviously those values will change for each deadlock report
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.