SQLServer2012 死锁问题讨论

时间：2021-11-06 15:59:04
问题描述:
系统中有一个存储过程用来插入或更新帐号手续费率数据,首先select看是否存在手续费率,如果不存在则insert否则update.同一时刻会有可能会有多个线程调用该存储过程,但是每个线程之间只会修改不同帐号下的数据,所以并发调用并不会修改同一条数据.代码如下:
ALTER procedure [dbo].[AddOrUpdateInstCommiRate]
@p_intUserID int,
@p_chrInstrumentID char(30),
@p_intTradingDay int,
@p_chrInvestorID char(12),
@p_decOpenRatioByMoney decimal(10, 8),
@p_smmnyOpenRatioByVolume smallmoney,
@p_decCloseRatioByMoney decimal(10, 8),
@p_smmnyCloseRatioByVolume smallmoney,
@p_decCloseTodayRatioByMoney decimal(10, 8),
@p_smmnyCloseTodayRatioByVolume smallmoney,
@p_decStrikeRatioByMoney        decimal(10,8),
@p_smmnyStrikeRatioByVolume     smallmoney
as
BEGIN
declare @count int
select @count = count(*)
from [dbo].[CtpTrade_CommiRate] T1  with(nolock)
where T1.[CtpInvestorID] = @p_intUserID and T1.[InstrumentID] = @p_chrInstrumentID and T1.[TradingDay] = @p_intTradingDay

if @count = 0
begin
INSERT INTO [dbo].[CtpTrade_CommiRate]
           ([CtpInvestorID],
   [InvestorID],
   [InstrumentID],
   [TradingDay],
   [OpenRatioByMoney],
   [OpenRatioByVolume],
   [CloseRatioByMoney],
   [CloseRatioByVolume],
   [CloseTodayRatioByMoney],
   [CloseTodayRatioByVolume],
   [StrikeRatioByMoney],
   [StrikeRatioByVolume])
 VALUES(@p_intUserID,
@p_chrInvestorID,
@p_chrInstrumentID,
@p_intTradingDay,
@p_decOpenRatioByMoney,
@p_smmnyOpenRatioByVolume,
@p_decCloseRatioByMoney,
    @p_smmnyCloseRatioByVolume,
@p_decCloseTodayRatioByMoney,
    @p_smmnyCloseTodayRatioByVolume,
@p_decStrikeRatioByMoney,
@p_smmnyStrikeRatioByVolume)
end
else
begin
UPDATE [dbo].[CtpTrade_CommiRate]
SET [InvestorID] = @p_chrInvestorID,
[OpenRatioByMoney] = @p_decOpenRatioByMoney,
[OpenRatioByVolume] = @p_smmnyOpenRatioByVolume,
[CloseRatioByMoney] = @p_decCloseRatioByMoney,
[CloseRatioByVolume] = @p_smmnyCloseRatioByVolume,
[CloseTodayRatioByMoney] = @p_decCloseTodayRatioByMoney,
[CloseTodayRatioByVolume] = @p_smmnyCloseTodayRatioByVolume,
[StrikeRatioByMoney] = @p_decStrikeRatioByMoney,
[StrikeRatioByVolume] = @p_smmnyStrikeRatioByVolume
where [CtpInvestorID] = @p_intUserID and [InstrumentID] = @p_chrInstrumentID and [TradingDay] = @p_intTradingDay
end

END
系统运行一段时间后,发现有死锁情况,而且死锁发生在update频繁操作的时间段,而且确定该时间段没有insert操作.
抓取死锁图如下:
SQLServer2012 死锁问题讨论
死锁数据:
<deadlock-list>
 <deadlock victim="process27686b0c8">
  <process-list>
   <process id="process27686b0c8" taskpriority="0" logused="0" waitresource="PAGE: 13:1:1360 " waittime="4174" ownerId="940034" transactionname="UPDATE" lasttranstarted="2018-03-26T08:00:26.003" XDES="0x27f3e1900" lockMode="U" schedulerid="3" kpid="11808" status="suspended" spid="54" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2018-03-26T08:00:25.970" lastbatchcompleted="2018-03-26T08:00:25.970" lastattention="1900-01-01T00:00:00.970" clientapp=".Net SqlClient Data Provider" hostname="iZtg7m6a2zdww6Z" hostpid="10064" isolationlevel="read committed (2)" xactid="940034" currentdb="13" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="MidWarCtpTrade.dbo.AddOrUpdateInstCommiRate" line="58" stmtstart="3100" stmtend="4390" sqlhandle="0x03000d0087480f1640c114016ca8000001000000000000000000000000000000000000000000000000000000">
UPDATE [dbo].[CtpTrade_CommiRate]
SET [InvestorID] = @p_chrInvestorID,
[OpenRatioByMoney] = @p_decOpenRatioByMoney,
[OpenRatioByVolume] = @p_smmnyOpenRatioByVolume,
[CloseRatioByMoney] = @p_decCloseRatioByMoney,
[CloseRatioByVolume] = @p_smmnyCloseRatioByVolume,
[CloseTodayRatioByMoney] = @p_decCloseTodayRatioByMoney,
[CloseTodayRatioByVolume] = @p_smmnyCloseTodayRatioByVolume,
[StrikeRatioByMoney] = @p_decStrikeRatioByMoney,
[StrikeRatioByVolume] = @p_smmnyStrikeRatioByVolume
where [CtpInvestorID] = @p_intUserID and [InstrumentID] = @p_chrInstrumentID and [TradingDay] = @p_intTradingDay     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x01000d00a06963219089f0720200000000000000000000000000000000000000000000000000000000000000">
exec AddOrUpdateInstCommiRate 4219,&apos;fb1804&apos;,20180326,&apos;8010801001&apos;,0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0     </frame>
    </executionStack>
    <inputbuf>
exec AddOrUpdateInstCommiRate 4219,&apos;fb1804&apos;,20180326,&apos;8010801001&apos;,0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0    </inputbuf>
   </process>
   <process id="process2615fa558" taskpriority="0" logused="112" waitresource="PAGE: 13:1:808 " waittime="4179" ownerId="940035" transactionname="UPDATE" lasttranstarted="2018-03-26T08:00:26.003" XDES="0x2616a9740" lockMode="U" schedulerid="4" kpid="10512" status="suspended" spid="56" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2018-03-26T08:00:25.967" lastbatchcompleted="2018-03-26T08:00:25.967" lastattention="1900-01-01T00:00:00.967" clientapp=".Net SqlClient Data Provider" hostname="iZtg7m6a2zdww6Z" hostpid="10064" isolationlevel="read committed (2)" xactid="940035" currentdb="13" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="MidWarCtpTrade.dbo.AddOrUpdateInstCommiRate" line="58" stmtstart="3100" stmtend="4390" sqlhandle="0x03000d0087480f1640c114016ca8000001000000000000000000000000000000000000000000000000000000">
UPDATE [dbo].[CtpTrade_CommiRate]
SET [InvestorID] = @p_chrInvestorID,
[OpenRatioByMoney] = @p_decOpenRatioByMoney,
[OpenRatioByVolume] = @p_smmnyOpenRatioByVolume,
[CloseRatioByMoney] = @p_decCloseRatioByMoney,
[CloseRatioByVolume] = @p_smmnyCloseRatioByVolume,
[CloseTodayRatioByMoney] = @p_decCloseTodayRatioByMoney,
[CloseTodayRatioByVolume] = @p_smmnyCloseTodayRatioByVolume,
[StrikeRatioByMoney] = @p_decStrikeRatioByMoney,
[StrikeRatioByVolume] = @p_smmnyStrikeRatioByVolume
where [CtpInvestorID] = @p_intUserID and [InstrumentID] = @p_chrInstrumentID and [TradingDay] = @p_intTradingDay     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x01000d00c74b772e605be3720200000000000000000000000000000000000000000000000000000000000000">
exec AddOrUpdateInstCommiRate 4218,&apos;fb1804&apos;,20180326,&apos;8010801002&apos;,0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0     </frame>
    </executionStack>
    <inputbuf>
exec AddOrUpdateInstCommiRate 4218,&apos;fb1804&apos;,20180326,&apos;8010801002&apos;,0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0    </inputbuf>
   </process>
   <process id="process276864558" taskpriority="0" logused="10000" waittime="4144" schedulerid="2" kpid="10700" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-26T08:00:25.967" lastbatchcompleted="2018-03-26T08:00:25.967" lastattention="1900-01-01T00:00:00.967" clientapp=".Net SqlClient Data Provider" hostname="iZtg7m6a2zdww6Z" hostpid="10064" loginname="MidWarUser" isolationlevel="read committed (2)" xactid="940035" currentdb="13" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="MidWarCtpTrade.dbo.AddOrUpdateInstCommiRate" line="58" stmtstart="3100" stmtend="4390" sqlhandle="0x03000d0087480f1640c114016ca8000001000000000000000000000000000000000000000000000000000000">
UPDATE [dbo].[CtpTrade_CommiRate]
SET [InvestorID] = @p_chrInvestorID,
[OpenRatioByMoney] = @p_decOpenRatioByMoney,
[OpenRatioByVolume] = @p_smmnyOpenRatioByVolume,
[CloseRatioByMoney] = @p_decCloseRatioByMoney,
[CloseRatioByVolume] = @p_smmnyCloseRatioByVolume,
[CloseTodayRatioByMoney] = @p_decCloseTodayRatioByMoney,
[CloseTodayRatioByVolume] = @p_smmnyCloseTodayRatioByVolume,
[StrikeRatioByMoney] = @p_decStrikeRatioByMoney,
[StrikeRatioByVolume] = @p_smmnyStrikeRatioByVolume
where [CtpInvestorID] = @p_intUserID and [InstrumentID] = @p_chrInstrumentID and [TradingDay] = @p_intTradingDay     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x01000d00c74b772e605be3720200000000000000000000000000000000000000000000000000000000000000">
exec AddOrUpdateInstCommiRate 4218,&apos;fb1804&apos;,20180326,&apos;8010801002&apos;,0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0     </frame>
    </executionStack>
    <inputbuf>
exec AddOrUpdateInstCommiRate 4218,&apos;fb1804&apos;,20180326,&apos;8010801002&apos;,0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="1360" dbid="13" subresource="FULL" objectname="MidWarCtpTrade.dbo.CtpTrade_CommiRate" id="lock2612d0880" mode="U" associatedObjectId="72057594040877056">
    <owner-list>
     <owner id="process276864558" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process27686b0c8" mode="U" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <pagelock fileid="1" pageid="808" dbid="13" subresource="FULL" objectname="MidWarCtpTrade.dbo.CtpTrade_CommiRate" id="lock261561d80" mode="U" associatedObjectId="72057594040877056">
    <owner-list>
     <owner id="process27686b0c8" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2615fa558" mode="U" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <exchangeEvent id="Pipe2616bc700" WaitType="e_waitPipeGetRow" nodeId="1">
    <owner-list>
     <owner id="process2615fa558"/>
    </owner-list>
    <waiter-list>
     <waiter id="process276864558"/>
    </waiter-list>
   </exchangeEvent>
  </resource-list>
 </deadlock>
</deadlock-list>
现在结果很明确是 U锁 导致了循环等待 进而导致死锁
查了不少资料还是不太清楚为什么会发生这种情况,按道理来说只更新一条数据不应该产生死锁,求个位大神赐教

1 个解决方案

#1

跟几条数据没有关系， 跟你的 where 有关系。
如果确实只需要更新一条数据， 改成先查出记录， 再根据主键来更新。

#1

跟几条数据没有关系， 跟你的 where 有关系。
如果确实只需要更新一条数据， 改成先查出记录， 再根据主键来更新。
标签：

相关文章