unexpected lock on index in mssql in select queries

Question

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

Logs of process_id_1

   [1] (@P0 nvarchar(4000))select namesequen0_.id as id1_47_<c/> namesequen0_.container_id as containe9_47_<c/> namesequen0_.increment_ as incremen2_47_<c/> namesequen0_.nextValue as nextValu3_47_<c/> namesequen0_.numChars as numChars4_47_<c/> namesequen0_.padChar as padChar5_47_<c/> namesequen0_.scope as scope6_47_<c/> namesequen0_.sequenceType as sequence7_47_<c/> 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

Logs of process_id_2

[1]select card0_.id as id2_77_<c/> card0_.autoDisplayName as autoDisp3_77_<c/> card0_.autoPartialName as autoPart4_77_<c/> card0_.namedWithSequence_id as namedWi38_77_<c/> card0_.namedWithSequenceValue as namedWit5_77_<c/> card0_.userDisplayName as userDisp6_77_<c/> card0_.userPartialName as userPart7_77_<c/> card0_.lifeCycleState_id as lifeCyc40_77_<c/> card0_.model_id as model41_77_<c/> card0_.outOfService as outOfSe12_77_<c/> card0_.bottomClearance as bottomC13_77_<c/> card0_.leftClearance as leftCle14_77_<c/> card0_.rightClearance as rightCl15_77_<c/> card0_.topClearance as topClea16_77_<c/> 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 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

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

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?

In a default installation readers block writers and writers block readers in SQL Server. I would suspect that the sessions are also modifying some rows which can lead to such a deadlock. — a_horse_with_no_name

FuzzyJulz FuzzyJulz · Accepted Answer · 2015-06-03T04:27:04

MSSQL uses locking on select statements, it can do table, page and row locks. It really depends on what you want to do in your system and what is more important but a good pointer along the way is to look at the Table Hints that you can put on a SELECT statement, especially looking at NOWAIT, NOLOCK, READCOMMITTED and READ_COMMITTED_SNAPSHOT

You may also want to consider setting the Isolation Level on all of your transactions to avoid this issue in other places.

This is some pretty basic information and a DBA may be able to provide a more detailed answer, but these options will prevent that issue from happening

You can tell your isolation level by running DBCC useroptions and you can tell the read_committed_snapshot by running SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'YourDatabase'

unexpected lock on index in mssql in select queries

1 Answers