CODEWITHSUNDEEP
sql-server
sam
sam

Reputation: 1304

SQL Server is allowing X lock on shared lock which is cause of deadlock

This is a strange finding. I am doing select and delete on one session and delete on another session on the same row please see my code below. departmentid has clustered index.

------Session 1----------
set transaction isolation level repeatable read
begin transaction
select * from HumanResources.Department

------session 2 ------------
set transaction isolation level repeatable read
begin transaction
delete from HumanResources.Department
where departmentid = 9

--session 1 -----------

delete from HumanResources.Department
where departmentid = 9

This is what should happen.

However when I see the deadlock graph I get a different picture. Deadlock graph tells that session 1 grants S lock. Session 2 grants X lock(I have confusion here) how can x lock be granted on S lock. See the deadlock graph below :

<victimProcess id="process5d1a718"/>
  <process-list>
   <process id="process5d1a718" taskpriority="0" logused="0" waitresource="KEY: 7:72057594073907200 (15ac4ff5e281)" waittime="2723" ownerId="21790" transactionname="user_transaction" lasttranstarted="2016-03-06T15:44:12.863" XDES="0x6056c08" lockMode="X" schedulerid="2" kpid="6016" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-06T15:44:53.147" lastbatchcompleted="2016-03-06T15:44:12.863" clientapp="Microsoft SQL Server Management Studio" hostname="SAMAR-PC" hostpid="3620" loginname="samar-PC\samar" isolationlevel="repeatable read (3)" xactid="21790" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
    <executionStack>
     <frame procname="" line="1" stmtstart="24" sqlhandle="0x0200000033392c164aa2164aaf9c828974edf396604cba95">
     </frame>
     <frame procname="" line="1" sqlhandle="0x02000000e46e292caa2d578b8876190ff31f36bfdf42b892">
     </frame>
    </executionStack>
    <inputbuf>
delete from HumanResources.Department
where departmentid = 9    </inputbuf>
   </process>
   <process id="process19b1c8" taskpriority="0" logused="0" waitresource="KEY: 7:72057594074103808 (10aa4711661d)" waittime="21212" ownerId="21981" transactionname="user_transaction" lasttranstarted="2016-03-06T15:44:34.657" XDES="0x6b4c280" lockMode="X" schedulerid="1" kpid="4148" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-06T15:44:34.657" lastbatchcompleted="2016-03-06T15:43:36.970" lastattention="2016-03-06T15:33:58.423" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SAMAR-PC" hostpid="3620" loginname="samar-PC\samar" isolationlevel="repeatable read (3)" xactid="21981" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
    <executionStack>
     <frame procname="" line="3" stmtstart="24" sqlhandle="0x0200000033392c164aa2164aaf9c828974edf396604cba95">
     </frame>
     <frame procname="" line="3" stmtstart="136" sqlhandle="0x02000000c3907d12b9a0c511ab9b3c2966aa00fd5258d812">
     </frame>
    </executionStack>
    <inputbuf>
set transaction isolation level repeatable read
begin transaction
delete from HumanResources.Department
where departmentid = 9    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594073907200" dbid="7" objectname="" indexname="" id="locka1d5040" mode="X" associatedObjectId="72057594073907200">
    <owner-list>
     <owner id="process19b1c8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process5d1a718" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594074103808" dbid="7" objectname="" indexname="" id="locka1d4780" mode="S" associatedObjectId="72057594074103808">
    <owner-list>
     <owner id="process5d1a718" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process19b1c8" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
</value>
      <text />
    </data>
  </event>
</RingBufferTarget>

I have another finding this is also very strange. If I change my select statement of session 1, that is I add where clause in it I do not get any deadlock. I only see session 2 been blocked and session 1 deletes the row with departmentid = 9. refer to my query below : - 

------Session 1----------
set transaction isolation level repeatable read
begin transaction
select * from HumanResources.Department
where departmentid = 9

------session 2 ------------
set transaction isolation level repeatable read
begin transaction
delete from HumanResources.Department
where departmentid = 9

--session 1 -----------

delete from HumanResources.Department
where departmentid = 9

Can someone share some light here. All my concepts seem to be shattered here.

Upvotes: 0

Views: 366

Answers (1)

sam
sam

Reputation: 1304

The table has departmentid, name, groupname,modifieddate.

Departmentid has clustered index and name has nonclustered index and other columns - groupname and modifieddate are included columns. This what happened.

  1. select query in session 1 puts s lock on nonclustered index.

  2. session 2 put x lock on departmentid = 9 on clustered index.

  3. Now altering clustered index will update nonclustered index so session 2 tries to put X lock to nonclustered index but is blocked by S lock of session 1.

  4. Session 1 tries to delete the same row by putting x lock which is blocked by X lock of session 2 on clustered index. Hence deadlock.

I resolved it by putting hint with (index = Clusteredindexname) with select statement in session 1 :

------Session 1----------
set transaction isolation level repeatable read
begin transaction
select * from HumanResources.Department with (index = Clusteredindexname)

Upvotes: 1

Related Questions