SQLServer2012 死锁问题讨论

whu_iss_sunny 2018-05-18 05:25:03
问题描述:
系统中有一个存储过程用来插入或更新帐号手续费率数据,首先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操作.
抓取死锁图如下:

死锁数据:
<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,'fb1804',20180326,'8010801001',0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0 </frame>
</executionStack>
<inputbuf>
exec AddOrUpdateInstCommiRate 4219,'fb1804',20180326,'8010801001',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,'fb1804',20180326,'8010801002',0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0 </frame>
</executionStack>
<inputbuf>
exec AddOrUpdateInstCommiRate 4218,'fb1804',20180326,'8010801002',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,'fb1804',20180326,'8010801002',0.00010006,0.1,0.00010006,0.1,5.006E-05,0.1,0,0 </frame>
</executionStack>
<inputbuf>
exec AddOrUpdateInstCommiRate 4218,'fb1804',20180326,'8010801002',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锁 导致了循环等待 进而导致死锁
查了不少资料还是不太清楚为什么会发生这种情况,按道理来说只更新一条数据不应该产生死锁,求个位大神赐教
...全文
1098 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
aohan 2018-05-21
  • 打赏
  • 举报
 回复
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 这个写法可以调整成Exists if exists(select 1 from from [dbo].[CtpTrade_CommiRate] T1 with(nolock) where T1.[CtpInvestorID] = @p_intUserID and T1.[InstrumentID] = @p_chrInstrumentID and T1.[TradingDay] = @p_intTradingDay) begin update ...... end else begin insert .... end 当然也可直接使用merge into的语法，判断数据是否存在不要使用Count去处理，这会导致额外的标量运算符开销。 另外直接看一下涉及到的三条语句所对应的执行计划，这样可以有一个综合性的判断
whu_iss_sunny 2018-05-21
  • 打赏
  • 举报
 回复
引用 2 楼 aohan 的回复:
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 这个写法可以调整成Exists if exists(select 1 from from [dbo].[CtpTrade_CommiRate] T1 with(nolock) where T1.[CtpInvestorID] = @p_intUserID and T1.[InstrumentID] = @p_chrInstrumentID and T1.[TradingDay] = @p_intTradingDay) begin update ...... end else begin insert .... end 当然也可直接使用merge into的语法，判断数据是否存在不要使用Count去处理，这会导致额外的标量运算符开销。 另外直接看一下涉及到的三条语句所对应的执行计划，这样可以有一个综合性的判断
引用 1 楼 yenange 的回复:
跟几条数据没有关系， 跟你的 where 有关系。 如果确实只需要更新一条数据， 改成先查出记录， 再根据主键来更新。
谢谢 楼上的回答 很有启发 问题已经解决了 是由于迁移部署数据库的时候原数据表主键没有导出,导致现在这个表没有主键索引 所以update的时候产生了冲突
吉普赛的歌 2018-05-18
  • 打赏
  • 举报
 回复
跟几条数据没有关系， 跟你的 where 有关系。 如果确实只需要更新一条数据， 改成先查出记录， 再根据主键来更新。
SQL Server 2008高级程序设计 4/6
　本书首先介绍SQL Server 2008的新功能，然后在更详实的示例代码的引导下全面深入地展开论述，讨论了如何编写复杂查询、构建各种数据结构以及提高应用程序性能，还讲述了如何管理高级脚本和数据库以及如何确定和...
SQL Server 2008高级程序设计 2/6
　本书首先介绍SQL Server 2008的新功能，然后在更详实的示例代码的引导下全面深入地展开论述，讨论了如何编写复杂查询、构建各种数据结构以及提高应用程序性能，还讲述了如何管理高级脚本和数据库以及如何确定和...
sql server 性能优化和日常管理维护
课程总结sql server运行过程中对系统资源的使用和由此产生性能问题，介绍数据库规划设计、事务和锁的基本知识，如何处理阻塞和死锁，优化索引和读懂统计信息和执行计划等知识，同时详细介绍SQL 2014新功能特性内存...
SQL.Server.2008编程入门经典(第3版).part2.rar
第1章 RDBMS基础：SQLServer数据库的构成 1.1 数据库对象概述 1.1.1 数据库对象 1.1.2 事务日志 1.1.3 最基本的数据库对象：表 1.1.4 文件组 1.1.5 数据库关系图 1.1.6 视图 1.1.7 存储过程 1.1.8 用户自定义函数 ...
深入浅出解析mssql在高频,高并发访问时键查找死锁问题
SQL Server死锁使我们经常遇到的问题，数据库操作的死锁是不可避免的，本文并不打算讨论死锁如何产生，重点在于解决死锁。希望对您学习SQL Server死锁方面能有所帮助。
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章