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 ------------------------------- 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?**