sql server死锁:identify导致的 insert 和 select max(id)
2023-09-11 14:21:09 时间
【1】死锁信息
【1.1】图
左边:select max(id) from tab
右边:insert into tab values.....
两个语句是同一个表,id是主键、聚集索引
【1.2】XML
Deadlock graph <deadlock-list> <deadlock victim="process9ada68"> <process-list> <process id="process9ada68" taskpriority="0" logused="0" waitresource="PAGE: 102:1:5940067" waittime="3953" ownerId="1571798125" transactionname="user_transaction" lasttranstarted="2020-11-16T12:04:38.950" XDES="0x55282258" lockMode="S" schedulerid="2" kpid="2896" status="suspended" spid="80" sbid="1" ecid="0" priority="0" transcount="1" lastbatchstarted="2020-11-16T12:04:38.950" lastbatchcompleted="2020-11-16T12:04:38.950" clientapp="Microsoft SQL Server" hostname="iZqcugpmvybrkfZ" hostpid="1596" loginname="sa" isolationlevel="read committed (2)" xactid="1571798125" currentdb="102" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" sqlhandle="0x020000002b707300c7eefdadd6f8dfe7f8457b5eb4e02c32"> SELECT MAX("Tbl1002"."ID") "Expr1003" FROM "QPRecordDB"."dbo"."RecordUserInout" "Tbl1002" </frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> Proc [Database Id = 32767 Object Id = 315066412] </inputbuf> </process> <process id="processfe03e8" taskpriority="0" logused="620" waitresource="PAGE: 102:1:5940068" waittime="3953" ownerId="1571798118" transactionname="user_transaction" lasttranstarted="2020-11-16T12:04:38.950" XDES="0x37f294a8" lockMode="IX" schedulerid="3" kpid="4960" status="suspended" spid="83" sbid="1" ecid="0" priority="0" transcount="2" lastbatchstarted="2020-11-16T12:04:38.950" lastbatchcompleted="2020-11-16T12:04:38.950" clientapp="Microsoft SQL Server" hostname="iZqcugpmvybrkfZ" hostpid="1596" loginname="sa" isolationlevel="read committed (2)" xactid="1571798118" currentdb="102" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="316" sqlhandle="0x020000002109bb37cd7e5870d56e6418e1fe6003a303a1c9"> INSERT [QPRecordDB].[dbo].[RecordUserInout]([UserID],[KindID],[ServerID],[EnterScore],[EnterUserMedal],[EnterLoveliness],[EnterMachine],[EnterClientIP]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011) </frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> (@Param000004 int,@Param000005 int,@Param000006 int,@Param000007 bigint,@Param000008 int,@Param000009 int,@Param000010 nvarchar(33),@Param000011 nvarchar(15))INSERT [QPRecordDB].[dbo].[RecordUserInout]([UserID],[KindID],[ServerID],[EnterScore],[EnterUserMedal],[EnterLoveliness],[EnterMachine],[EnterClientIP]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011) </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="5940067" dbid="102" objectname="QPRecordDB.dbo.RecordUserInout" id="lockffffffff80008e00" mode="IX" associatedObjectId="72057594056540160"> <owner-list> <owner id="processfe03e8" mode="IX"/> </owner-list> <waiter-list> <waiter id="process9ada68" mode="S" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="5940068" dbid="102" objectname="QPRecordDB.dbo.RecordUserInout" id="lockffffffffb86aa940" mode="S" associatedObjectId="72057594056540160"> <owner-list> <owner id="process9ada68" mode="S"/> </owner-list> <waiter-list> <waiter id="processfe03e8" mode="IX" requestType="wait"/> </waiter-list> </pagelock> </resource-list> </deadlock> </deadlock-list>
【2】分析思路
【2.1】语句分析
select max(id) from RecordUserInout
(1) 当我用max(id)查询的时候,它会顺着索引去找最大的,比如找到了最大记录在这个页,它会这个页上加上S锁
(2) 接着insert into values插入的时候,它需要同时在这两个页上写数据,于是向这个页上加IX锁
(3) 但是第一个页已经加了S锁,于是第一个页的IX就会等待,但是第二个页就不需要
怎么想都感觉不是很对
难道是这样?
(1)select max(id) 要扫描整个聚集索引树才能找到max(id),对聚集索引树页都加了S锁
(2)同时,在select max(id) 还没找到的时候,insert into 插入到了最后面,然后它排它X了, 然后它需要X锁来更新聚集索引上层页
(3)但这个时候上层页正在被select 给S锁阻塞住了
(4)select max(id) 的后续扫描又被 insert 的X锁给阻塞了
感觉其实也不是很对,既然
【2.2】思路分析
可能是跟踪只是捕获到当前语句,但没有语句所在事务的相关上下文,找程序看一看
【3】解决思路
对select max(id) from tab 做 with(nolock)
相关文章
- SQL Server 各版本发布时间、开发代号及下载地址
- SQL Server数据库高级进阶之事务实战演练
- Sql Server之旅——第七站 为什么都说状态少的字段不能建索引
- sql server链接查询
- SQL SERVER中用户定义标量函数(scalar user defined function)的性能问题
- SQL Server基础Sql语句复习
- SQL Server 2008 筛选器报错Microsoft.SqlServer.Management.Sdk.Sfc
- ArcGIS Server 9.3集群部署(多som+多soc)
- SQL Server replication requires the actual server name to make a connection to the server.错误解决
- Windows Server 2003 R2 IIS服务的命令行方式重启命令
- Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’
- Windwos Server 2012 R2 部署iSCSI 虚拟存储
- Sql Server 取出多个字段列中的最大值和最小值
- Sql Server用管理器建表后如何查看创建表的语句
- SQL Server 中读取当前年月
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- 附加数据库时,提示“Microsoft SQL Server,错误: 5120”, 解决方案
- SQL SERVER CHARINDEX函数
- Sql Server在建好的表中,新增一列
- 成功解决SQL Server软件中出现的18456问题
- Sql:成功解决将sql输出的datetime时间格式转为常规格式
- SQL Server -减少代码触发的负担
- C#同步SQL Server数据库中的数据--数据库同步工具[同步新数据]
- Java sql server数据库连接成功
- MySQL----JDBC无法连接数据库,报The driver has not received any packets from the server的解决办法
- SQL Server-【知识与实战VI】索引信息
- [SQL] SQL Server获取数据库服务器当前时间