none
请问死锁的知识,为什么采用rowlock仍发生死锁 RRS feed

  • 问题

  • 请教诸位高手帮我解释下,看有木有好办法:

    我这边有两个数据库进程,采用同一个ORDERS表,在采用DEADLOCK GRAPH监测死锁的过程中发现触发器中的

    update orders set editdate=getdate where orderkey=inserted.orderkey, 发生死锁,

    另外一个update orders set status=‘6’ where orderkey=@orderkey and status='0'

    我疑惑的是

    1.两个进程访问不同的ORDERKEY,是如何发生死锁的?

    2.首先看到的是pagelock.我把相应的SQL 改成rowlock,死锁仍然存在只不过变成Pk LOCK,两个进程同时退出,并没有改进。

    3.我猜测是不是orders.status本身有索引造成,更新status的时候索引相关的行全部发生更新?会这样么,我把无用的status索引去掉,发现并没有发生改观,只是?

    4.我用执行计划看到update orders set status=‘6’ where orderkey=@orderkey and status='0' 会产生RID INDEX,我把语句中变为update orders set status=‘6’ where orderkey=@orderkey, 似乎也没改观,deadlock graph告诉我,死锁的发生点还是一样,我有些不解了。谁能帮我解释下,更新不同的记录并且都是采用主键更新也会死锁么?有好方法解决么?不想程序改的太多,主要deadlock涉及到的SQL语句均为PRIMARY KEY

    数据库版本是2008

    • 已编辑 tomsong 2016年3月19日 7:15
    2016年3月19日 2:01

全部回复

  • 详细的deadlock graph可以贴出来吗,还有索引定义

    Love SQL

    2016年3月19日 11:49
  • 首先,分别持有主键和索引的锁确实可能造成单个Update语句死锁。

    其次,你先确定一下,事务里的其它语句持有什么锁。第一个语句明显是触发器里面的,那么触发这个触发器的语句获取了什么锁(以及顺序),触发器里面有没有其它语句获取了锁。第二个update语句是不是事务中的,它获取了什么锁。

    第三,死锁牺牲品不应该全部退出啊,是不是还有第三个事务?

    还是把deadlock graph贴出来吧。


    想不想时已是想,不如不想都不想。

    2016年3月20日 14:51
    版主
  • update orders set editdate=getdate where orderkey=inserted.orderkey, 发生死锁,

    另外一个update orders set status=‘6’ where orderkey=@orderkey and status='0'

    ----------------------------------------------------- 有查过执行计划么?这两句用的是不同的条件,有可能是使用不同的索引,如果通过执行计划确认不是相同的索引,那么出现死锁就很容易解释了

    2016年3月21日 1:47
  • 多谢,这是我减少索引过程中的三次过程,似乎大同小异,帮俺看看呀,最近看得有点晕了,实在不想再去看了

    这是最初的Deadlock graph

    <deadlock-list>
     <deadlock victim="process54434c8">
      <process-list>
       <process id="process54434c8" taskpriority="0" logused="25620" waitresource="PAGE: 7:1:39689" waittime="527" ownerId="418897565" transactionname="implicit_transaction" lasttranstarted="2016-03-17T17:09:18.557" XDES="0x27dee53c0" lockMode="IU" schedulerid="18" kpid="6164" status="suspended" spid="70" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-17T17:13:22.017" lastbatchcompleted="2016-03-17T17:13:21.947" clientapp="PowerBuilder" hostname="SSC1-W92" hostpid="5712" loginname="pvmi.dbo" isolationlevel="read committed (2)" xactid="418897565" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
        <executionStack>
         <frame procname="PVMI_DEV.dbo.SP_ProcessOrders" line="937" stmtstart="82454" stmtend="82614" sqlhandle="0x0300070089d6c551e47cd600cba500000100000000000000">
    UPDATE ORDERS SET STATUS = &apos;3&apos; WHERE ORDERKEY = @C_ORDERKEY AND STATUS = &apos;0&apos;     </frame>
        </executionStack>
        <inputbuf>
    Proc [Database Id = 7 Object Id = 1371920009]    </inputbuf>
       </process>
       <process id="process53ff288" taskpriority="0" logused="410340" waitresource="PAGE: 7:1:206715" waittime="2547" ownerId="418936563" transactionname="UPDATE" lasttranstarted="2016-03-17T17:13:24.230" XDES="0x2cce35950" lockMode="U" schedulerid="16" kpid="8176" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-17T17:13:24.230" lastbatchcompleted="2016-03-17T17:13:24.153" clientapp="RFWMS" hostpid="5464" loginname="pvmi.dbo" isolationlevel="read committed (2)" xactid="418936563" currentdb="7" lockTimeout="4294967295" clientoption1="136445984" clientoption2="16416">
        <executionStack>
         <frame procname="PVMI_DEV.dbo.ntrOrderHeaderUpdate" line="136" stmtstart="9008" stmtend="9312" sqlhandle="0x0300070085854c3be5b4b900cba500000000000000000000">
    UPDATE ORDERS
    SET  EditDate = GETDATE(),
    EditWho =  USER_NAME(),
    TrafficCop =null
    FROM ORDERS,
    INSERTED
    WHERE ORDERS.OrderKey = INSERTED.OrderKey     </frame>
         <frame procname="PVMI_DEV.dbo.ntrOrderDetailUpdate" line="229" stmtstart="25054" stmtend="28960" sqlhandle="0x03000700c7575e689a56a400cba500000000000000000000">
    UPDATE  ORDERS
                                    SET     ORDERS.OpenQty = ( Orders.Openqty
                                                               - ( SELECT
                                                                  SUM(DELETED.OpenQty)
                                                                  FROM
                                                                  DELETED
                                                                  WHERE
                                                                  DELETED.OrderKey = ORDERS.OrderKey
                                                                 )
                                                               + ( SELECT
                                                                  SUM(INSERTED.OpenQty)
                                                                  FROM
                                                                  INSERTED
                                                                  WHERE     </frame>
         <frame procname="PVMI_DEV.dbo.ntrPickDetailUpdate" line="809" stmtstart="64206" stmtend="65760" sqlhandle="0x030007003ae6db728030a400cba500000000000000000000">
    UPDATE ORDERDETAIL SET
         ORDERDETAIL.QtyPicked = ORDERDETAIL.QtyPicked +
               (SELECT SUM(INSERTED.Qty )
                FROM INSERTED WHERE INSERTED.OrderKey =ORDERDETAIL.OrderKey
                AND INSERTED.OrderLineNumber = ORDERDETAIL.OrderLineNumber
                AND INSERTED.Status IN (&apos;5&apos;,&apos;6&apos;,&apos;7&apos;,&apos;8&apos;,&apos;9&apos;)  )
           /* Concurrency, changed PAGELOCK to ROWLOCK, SMO - OngPW 2005/05/27 */
                FROM ORDERDETAIL (ROWLOCK),INSERTED
                 WHERE INSERTED.OrderKey =ORDERDETAIL.OrderKey
                 and INSERTED.OrderLineNumber = ORDERDETAIL.OrderLineNumber
                 AND INSERTED.Status IN (&apos;5&apos;,&apos;6&apos;,&apos;7&apos;,&apos;8&apos;,&apos;9&apos;)
       --James liu 20150112 mark
       --PRINT &apos;----qtypick add &apos; +CAST(@@rowcount AS varchar(30))
       --James liu 20150112 mark     </frame>
         <frame procname="PVMI_DEV.dbo.SLI_Outbound_Scanning" line="664" stmtstart="52810" stmtend="53256" sqlhandle="0x03000700fdff6b081a8e24013aa500000100000000000000">
    UPDATE  PICKDETAIL WITH ( ROWLOCK )
                    SET     PICKDETAIL.STATUS = @ToStatus
                    WHERE   PICKDETAIL.DropId = @TruckID
                            AND PICKDETAIL.STATUS &lt;&gt; &apos;9&apos;     </frame>
        </executionStack>
        <inputbuf>
    Proc [Database Id = 7 Object Id = 141295613]    </inputbuf>
       </process>
      </process-list>
      <resource-list>
       <pagelock fileid="1" pageid="39689" dbid="7" objectname="PVMI_DEV.dbo.ORDERS" id="lock7c4587c80" mode="U" associatedObjectId="698582106308608">
        <owner-list>
         <owner id="process53ff288" mode="U"/>
        </owner-list>
        <waiter-list>
         <waiter id="process54434c8" mode="IU" requestType="wait"/>
        </waiter-list>
       </pagelock>
       <pagelock fileid="1" pageid="206715" dbid="7" objectname="PVMI_DEV.dbo.ORDERS" id="lock7c1cdc300" mode="IX" associatedObjectId="698582106308608">
        <owner-list>
         <owner id="process54434c8" mode="IX"/>
        </owner-list>
        <waiter-list>
         <waiter id="process53ff288" mode="U" requestType="wait"/>
        </waiter-list>
       </pagelock>
      </resource-list>
     </deadlock>
    </deadlock-list>

    这个是我最后去掉不相干索引后的记录

    <deadlock-list>
     <deadlock victim="process849948">
      <process-list>
       <process id="process849948" taskpriority="0" logused="43272" waitresource="KEY: 7:698582106308608 (77066d8f2b44)" waittime="4138" ownerId="420993288" transactionname="implicit_transaction" lasttranstarted="2016-03-18T17:16:17.627" XDES="0x41ee0a3b0" lockMode="U" schedulerid="4" kpid="5944" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-18T17:20:36.850" lastbatchcompleted="2016-03-18T17:20:36.780" clientapp="PowerBuilder" hostname="SSC1-W92" hostpid="10956" loginname="pvmi.dbo" isolationlevel="read committed (2)" xactid="420993288" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
        <executionStack>
         <frame procname="PVMI_DEV.dbo.SP_ProcessOrders" line="937" stmtstart="82454" stmtend="82618" sqlhandle="0x0300070089d6c551208b1901cda500000100000000000000">
    UPDATE ORDERS SET STATUS = &apos;3&apos; WHERE ORDERKEY = @C_ORDERKEY --AND STATUS = &apos;0&apos;     </frame>
        </executionStack>
        <inputbuf>
    Proc [Database Id = 7 Object Id = 1371920009]    </inputbuf>
       </process>
       <process id="process849dc8" taskpriority="0" logused="468396" waitresource="KEY: 7:698582106308608 (13a5b61f1327)" waittime="5490" ownerId="421111365" transactionname="UPDATE" lasttranstarted="2016-03-18T17:20:38.643" XDES="0x38e9c0e80" lockMode="U" schedulerid="4" kpid="6388" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-18T17:20:38.643" lastbatchcompleted="2016-03-18T17:20:38.563" clientapp="RFWMS" hostpid="6304" loginname="pvmi.dbo" isolationlevel="read committed (2)" xactid="421111365" currentdb="7" lockTimeout="4294967295" clientoption1="136445984" clientoption2="16416">
        <executionStack>
         <frame procname="PVMI_DEV.dbo.ntrOrderHeaderUpdate" line="136" stmtstart="9008" stmtend="9342" sqlhandle="0x0300070085854c3b091b1b01cda500000000000000000000">
    UPDATE ORDERS WITH (ROWLOCK)
    SET  EditDate = GETDATE(),
    EditWho =  USER_NAME(),
    TrafficCop =null
    FROM ORDERS,
    INSERTED
    WHERE ORDERS.OrderKey = INSERTED.OrderKey     </frame>
         <frame procname="PVMI_DEV.dbo.ntrOrderDetailUpdate" line="275" stmtstart="31092" stmtend="32562" sqlhandle="0x03000700c7575e6817ee0501cda500000000000000000000">
    UPDATE  ORDERS
                            SET     ORDERS.Status = &apos;5&apos;
                            WHERE   Orders.OrderKey IN (
                                    SELECT  OrderDetail.OrderKey
                                    FROM    OrderDetail
                                    WHERE   OrderDetail.OrderKey IN (
                                            SELECT DISTINCT
                                                    INSERTED.OrderKey
                                            FROM    INSERTED )
                                    GROUP BY OrderDetail.OrderKey
                                    HAVING  SUM(OrderDetail.OpenQty) = SUM(OrderDetail.QtyPicked) )
                                    AND Orders.Status &lt; &apos;5&apos;     </frame>
         <frame procname="PVMI_DEV.dbo.ntrPickDetailUpdate" line="809" stmtstart="64206" stmtend="65760" sqlhandle="0x030007003ae6db728030a400cba500000000000000000000">
    UPDATE ORDERDETAIL SET
         ORDERDETAIL.QtyPicked = ORDERDETAIL.QtyPicked +
               (SELECT SUM(INSERTED.Qty )
                FROM INSERTED WHERE INSERTED.OrderKey =ORDERDETAIL.OrderKey
                AND INSERTED.OrderLineNumber = ORDERDETAIL.OrderLineNumber
                AND INSERTED.Status IN (&apos;5&apos;,&apos;6&apos;,&apos;7&apos;,&apos;8&apos;,&apos;9&apos;)  )
           /* Concurrency, changed PAGELOCK to ROWLOCK, SMO - OngPW 2005/05/27 */
                FROM ORDERDETAIL (ROWLOCK),INSERTED
                 WHERE INSERTED.OrderKey =ORDERDETAIL.OrderKey
                 and INSERTED.OrderLineNumber = ORDERDETAIL.OrderLineNumber
                 AND INSERTED.Status IN (&apos;5&apos;,&apos;6&apos;,&apos;7&apos;,&apos;8&apos;,&apos;9&apos;)
       --James liu 20150112 mark
       --PRINT &apos;----qtypick add &apos; +CAST(@@rowcount AS varchar(30))
       --James liu 20150112 mark     </frame>
         <frame procname="PVMI_DEV.dbo.SLI_Outbound_Scanning" line="664" stmtstart="52810" stmtend="53256" sqlhandle="0x03000700fdff6b081a8e24013aa500000100000000000000">
    UPDATE  PICKDETAIL WITH ( ROWLOCK )
                    SET     PICKDETAIL.STATUS = @ToStatus
                    WHERE   PICKDETAIL.DropId = @TruckID
                            AND PICKDETAIL.STATUS &lt;&gt; &apos;9&apos;     </frame>
        </executionStack>
        <inputbuf>
    Proc [Database Id = 7 Object Id = 141295613]    </inputbuf>
       </process>
      </process-list>
      <resource-list>
       <keylock hobtid="698582106308608" dbid="7" objectname="PVMI_DEV.dbo.ORDERS" indexname="PKOrders" id="lock38a531b00" mode="U" associatedObjectId="698582106308608">
        <owner-list>
         <owner id="process849dc8" mode="U"/>
        </owner-list>
        <waiter-list>
         <waiter id="process849948" mode="U" requestType="wait"/>
        </waiter-list>
       </keylock>
       <keylock hobtid="698582106308608" dbid="7" objectname="PVMI_DEV.dbo.ORDERS" indexname="PKOrders" id="lock4ee9b1f00" mode="X" associatedObjectId="698582106308608">
        <owner-list>
         <owner id="process849948" mode="X"/>
        </owner-list>
        <waiter-list>
         <waiter id="process849dc8" mode="U" requestType="wait"/>
        </waiter-list>
       </keylock>
      </resource-list>
     </deadlock>
    </deadlock-list>

    2016年3月21日 2:09
  • 多谢大家,帮我分析分析,万分感谢
    2016年3月21日 2:10
  • 第一个语句是出自SP,在transaction里面。

    第二个语句出自触发器trigger

    2016年3月21日 2:16
  • AssociatedObjectId is an allocation unit ID, you can query sys.partitions to find what it is. 
    2016年3月21日 2:35
  • Thanks rmiao, I'll try to monitor on this, by the way, where the AssociatedObjectId cames from, from inserted or deletd records from trigger or update data range?
    2016年3月21日 5:59
  • From deadlock trace, every session that accesses db has that info to identify which object/page to access.
    2016年3月21日 14:01
  • Does this info disappear after session ends?

    MSDN Deadlock issue

    2016年3月21日 15:18
  • Yes, only available for connected sessions but that value is always there as long as referenced object/page are still exist.
    2016年3月21日 17:36
  • 你把那些id都解析出来吧,我们可看不到这些id是哪个对象哪条数据。

    想不想时已是想,不如不想都不想。

    2016年3月22日 2:10
    版主
  • SQL Excution plan shows that

    1. update orders set editdate=getdate where orderkey=inserted.orderkey use Primary Key PK_Orders as index

    2.update orders set status=‘6’ where orderkey=@orderkey and status='0' also Primary Key PK_Orders as index but it also use a extra RID index by status (I am confused on that, wheter this kind of RID index can cause this kind of lock?, and how to avoid it?)


    MSDN Deadlock issue

    2016年3月22日 2:13
  • Thanks a lot, I'll check on that, I have just one question, if an index has just one partition, whether one update SQL statement can involve all the rows on this partition? am I right?


    MSDN Deadlock issue


    • 已编辑 tomsong 2016年3月22日 3:04
    2016年3月22日 3:03
  • 其实deadlock graph已经很清楚associatedObjectId="698582106308608“就是orders table,可以参考我上面发的图

    hobtid="698582106308608“就是PK_ORDERS主键,看了大家的回复,我想问一下,我看到partition中的记录是不是一个partition的update使用,会更新所有的行


    MSDN Deadlock issue


    • 已编辑 tomsong 2016年3月22日 3:08
    2016年3月22日 3:06
  • Depends on how many rows updated. By the way, which column is Pkey in the table?
    2016年3月22日 3:11
  • orderkey is primary key and no-duplicated orderkey in orders

    MSDN Deadlock issue

    2016年3月22日 3:52
  • the may involved mutiple lines , process SP_ProcessOrders update 100 orders one by one in the SP and commit at end of SP, while another one SP update orders involve also 100 rows:

    UPDATE  PICKDETAIL WITH ( ROWLOCK )
                    SET     PICKDETAIL.STATUS = @ToStatus
                    WHERE   PICKDETAIL.DropId = @TruckID
                            AND PICKDETAIL.STATUS

                                 and status<'9'

    those two SPs all run in inividual transaction and update different orders and commit or rollback at end


    MSDN Deadlock issue


    • 已编辑 tomsong 2016年3月22日 4:12
    2016年3月22日 4:07
  • 似乎PK不是Clustered的?

    想不想时已是想,不如不想都不想。

    2016年3月22日 5:29
    版主
  • 是的是的,NON-CLUSTERED会有影响么

      CONSTRAINT [PKOrders] PRIMARY KEY NONCLUSTERED
    (
     [OrderKey] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]


    MSDN Deadlock issue

    2016年3月22日 5:33
  • 是有影响。会先获取PK的锁,再去获取row的锁。

    想不想时已是想,不如不想都不想。

    2016年3月22日 7:58
    版主
  • There's update trigger in the table?
    2016年3月22日 12:12
  • yes, many triggers in the old database, pickdetail update trigger order detail update then TRIGGER ORDERS update trigger:(

    MSDN Deadlock issue

    2016年3月23日 3:11