记一次公司仓库数据库服务器死锁过程及解决办法_MsSql

死锁的四个必要条件:

互斥条件(Mutual exclusion):资源不能被共享,只能由一个进程使用。

请求与保持条件(Hold and wait):已经得到资源的进程可以再次申请新的资源。

非剥夺条件(No pre-emption):已经分配的资源不能从相应的进程中被强制地剥夺。

循环等待条件(Circular wait):系统中若干进程组成环路,该环路中每个进程都在等待相邻进程正占用的资源。

仓库拣货卡死,排查了数据库的很多地方,都没有头绪,最后到SQL Server 错误日志里查看,终于发现了蛛丝马迹

EXEC xp_readerrorlog 0,1,NULL,NULL,'2015-09-21','2015-10-10','DESC'
   waiter id=process5c30e08 mode=U requestType=wait
  waiter-list
   owner id=process5c26988 mode=X
  owner-list
  keylock hobtid=72057597785604096 dbid=33 objectname=stoxxx.dbo.Orderxxx indexname=IX_PricingExpressProductCode_State id=lock17fa96980 mode=X associatedObjectId=72057597785604096
   waiter id=process5c26988 mode=U requestType=wait
  waiter-list
   owner id=process5c30e08 mode=X
  owner-list
  keylock hobtid=72057597785604096 dbid=33 objectname=stoxxx.dbo.Orderxxx indexname=IX_PricingExpressProductCode_State id=lock87d69e780 mode=X associatedObjectId=72057597785604096
 resource-list
(@OperateState money,@HandledByNewWms bit,@State int,@OrderOut int)
UPDATE [Orderxx] SET [OperateState] = @OperateState,[HandledByNewWms] = @HandledByNewWms WHERE (([Orderxxx].[State] = @State) And ([Orderxxx].[OrderOut] = @OrderOut) And ([Orderxxx].[PricingExpressProductCode] IN ('UKNIR')))
  inputbuf
unknown
   frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
UPDATE [Orderxxx] SET [OperateState] = @OperateState,[HandledByNewWms] = @HandledByNewWms WHERE (([Orderxxx].[State] = @State) And ([Orderxxx].[OrderOut] = @OrderOut) And ([Orderxxx].[PricingExpressProductCode] IN ('UKNIR')))
   frame procname=adhoc line=1 stmtstart=134 sqlhandle=0x020000009d376d18a17e7ea51289d8caa2fb4de65c976389
  executionStack
  process id=process5c30e08 taskpriority=0 logused=10320 waitresource=KEY: 33:72057597785604096 (112399c2054a) waittime=4813 ownerId=31578743038 transactionname=user_transaction lasttranstarted=2015-09-24T10:22:58.410 XDES=0x372e95950 lockMode=U schedulerid=17 kpid=8496 status=suspended spid=153 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-09-24T10:22:58.540 lastbatchcompleted=2015-09-24T10:22:58.540 clientapp=.Net SqlClient Data Provider hostname=CK1-WIN-WEB02 hostpid=37992 loginname=ck1.biz isolationlevel=read committed (2) xactid=31578743038 currentdb=33 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
(@OperateState money,@HandledByNewWms bit,@State int,@OrderOut int)UPDATE [Orderxxx] SET [OperateState] = @OperateState,[HandledByNewWms] = @HandledByNewWms WHERE (([Orderxxx].[State] = @State) And ([Orderxxx].[OrderOut] = @OrderOut) And ([Orderxxx].[PricingExpressProductCode] IN ('UKNIR')))
  inputbuf
unknown
   frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
UPDATE [Orderxxx] SET [OperateState] = @OperateState,[HandledByNewWms] = @HandledByNewWms WHERE (([Orderxxx].[State] = @State) And ([Orderxxx].[OrderOut] = @OrderOut) And ([Orderxxx].[PricingExpressProductCode] IN ('UKNIR')))
   frame procname=adhoc line=1 stmtstart=134 sqlhandle=0x020000009d376d18a17e7ea51289d8caa2fb4de65c976389
  executionStack
  process id=process5c26988 taskpriority=0 logused=9892 waitresource=KEY: 33:72057597785604096 (70f5b089bb2b) waittime=4813 ownerId=31579268946 transactionname=user_transaction lasttranstarted=2015-09-24T10:27:01.357 XDES=0x98312f950 lockMode=U schedulerid=16 kpid=9184 status=suspended spid=454 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-09-24T10:27:01.490 lastbatchcompleted=2015-09-24T10:27:01.487 clientapp=.Net SqlClient Data Provider hostname=CK1-WIN-WEB02 hostpid=37992 loginname=ck1.biz isolationlevel=read committed (2) xactid=31579268946 currentdb=33 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
 process-list
 deadlock victim=process5c26988
deadlock-list

咋一看上面的错误信息,可以发现两条相同的语句造成的死锁,但是这么短的语句不可能持有排他锁太久

再仔细分析一下错误日志,发现都死锁在同一个非聚集索引上,再问了一下开发,开发那边说,这条语句是在一个大事务里面,这个事务会做7、8件事

索引属性

还有索引里面的数据,发现很多重复值

SQL语句是这样的

(@OperateState money,@HandledByNewWms bit,@State int,@OrderOut int)
@HandledByNewWms=(1) @OperateState=($1.0000) @OrderOut=(4055484) @State=(3)
UPDATE [Orderxxx] SET [OperateState] = $1.0000,[HandledByNewWms] = 1
WHERE (([Orderxxx].[State] = 3) And ([Orderxxx].[OrderOut] = 4055484) And ([Orderxxx].[PricingExpressProductCode] IN ('UKRRM','UKRLE')))

下图为语句生成的执行计划

当时的情况是大量SQL语句被阻塞,而阻塞的语句正是下面这条语句

UPDATE [Orderxxx] SET [OperateState] = $1.0000,[HandledByNewWms] = 1
WHERE (([Orderxxx].[State] = 3) And ([Orderxxx].[OrderOut] = 4055484) And ([Orderxxx].[PricingExpressProductCode] IN ('UKRRM','UKRLE')))

解决方法

上面得出几个症状

1、update语句是在一个大事务里面,事务太大导致其他session等待排他锁的时间变长

2、大家都在使用同一个非聚集索引,并扫描PricingExpressProductCode字段

3、索引里的重复值很多

从上面的症状基本可以判断,这个非聚集索引无啥用,可以禁用之

ALTER INDEX [IX_PricingExpressProductCode_State] ON [dbo].[Orderxxx] DISABLE

禁用之后,死锁消失,问题解决,仓库的怨气也随之消失

这一次排查过程时间有点长,但是很好定位,SQL Server错误日志给出了足够的信息定位死锁问题,所以遇到问题的时候一定要分析清楚日志

以上是小编为您精心准备的的内容,在的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索数据库死锁
数据库死锁解决
mssql 数据库死锁、mssql 死锁、mssql 查询死锁、mssql 查看死锁、数据库死锁,以便于您获取更多的相关知识。

时间: 2024-12-25 17:27:13

记一次公司仓库数据库服务器死锁过程及解决办法_MsSql的相关文章

记一次公司仓库数据库服务器死锁过程及解决办法

死锁的四个必要条件: 互斥条件(Mutual exclusion):资源不能被共享,只能由一个进程使用. 请求与保持条件(Hold and wait):已经得到资源的进程可以再次申请新的资源. 非剥夺条件(No pre-emption):已经分配的资源不能从相应的进程中被强制地剥夺. 循环等待条件(Circular wait):系统中若干进程组成环路,该环路中每个进程都在等待相邻进程正占用的资源. 仓库拣货卡死,排查了数据库的很多地方,都没有头绪,最后到SQL Server 错误日志里查看,终于

sqlserver数据库迁移后,孤立账号解决办法_MsSql

复制代码 代码如下: declare @cmd nvarchar(4000) set @cmd = N'exec [?].sys.sp_change_users_login @Action = ''Auto_Fix'' , @UserNamePattern = ''qa'' , @LoginName = null , @Password = ''abc'' ' exec sp_msforeachdb @cmd

SQLServer 2008 R2 死锁监控及解决办法

1. 死锁原理     根据操作系统中的定义:死锁是指在一组进程中的各个进程均占有不会释放的资源,但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态.     死锁的四个必要条件: 互斥条件(Mutual exclusion):资源不能被共享,只能由一个进程使用. 请求与保持条件(Hold and wait):已经得到资源的进程可以再次申请新的资源. 非剥夺条件(No pre-emption):已经分配的资源不能从相应的进程中被强制地剥夺. 循环等待条件(Circular wai

SQLSERVER数据库备份后无法还原的解决办法_MsSql

有时候完全备份, 当还原的时候说不时数据库文件不让还原, 解决办法: 可以直接复制数据库文件, xxx.mdf 和 xxx.ldf (实际复制过程中需要先停止sqlserver服务才可以) 用 sp_attach_db 存储过程 就能搞定. 示例 下面的示例将 pubs 中的两个文件附加到当前服务器. EXEC sp_attach_db @dbname = N'pubs', @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Da

DNS服务器错误码4010解决办法

  DNS服务器错误码4010解决办法: 在域级别上,就是在domain.com这个区域新建你服务器出错的 a记录. c62fc7a6-ad5e-4150-a277-017523a082ca._msdcs.ist.local是我们大陆服务器; 新建它为a记录并指向该服务器ip地址,数十分钟后此记录消失,问题就解决了. 强制的把记录复制到了dns 的_msdcs区域内.

Oracle数据库ORA 54013错误的解决办法_oracle

ORA-54013: 不允许对虚拟列执行 INSERT 操作 这是Oracle 11 的新特性 -- 虚拟列. 在以前的Oracle 版本,当我们需要使用表达式或者一些计算公式时,我们会创建数据库视图,如果我们需要在这个视图上使用索引,我们会创建基于函数的索引.现在Oracle 11允许我们直接在表上使用虚拟列来存储表达式.虚拟列的值是不存储在磁盘的,它们是在查询时根据定义的表达式临时计算的.我们不能往虚拟列中插入数据,我们也不能隐式的添加数据到虚拟列,我们只能使用物理列来插入数据.然后可以查询

解决SQL SERVER数据库备份时出现“操作系统错误5(拒绝访问)。BACKUP DATABASE 正在异常终止。”错误的解决办法_MsSql

SQL SERVER数据库进行备份时出现"操作系统错误5(拒绝访问).BACKUP DATABASE 正在异常终止."错误.我们应该如何解决这个问题?小编今天为大家推荐一个解决办法. 一般备份文件选择的目录为磁盘根目录或备份所选分区未授予sqlserver用户读写权限时会出现此错误. 解决办法就是给sqlserver用户授予权限:    选择要备份的文件夹 ,右键-->属性-->安全-->看下"组或用户"是否包涵Authenticated User

SQL数据库实例名称找不到或远程连接失败并显示错误error40的原因及解决办法_MsSql

分享由字符"\"转义引起的SQL数据库实例名称找不到或远程连接失败并显示错误error40的解决办法: 一.问题介绍 很久没有用c#去连数据库程序了,不过在网上找了一些资料,基本上还是写出来了,但是调试初步完成的程序时候,却发现在con.open()这个方法总是出错,说找不到数据库实例名称,或者远程连接失败,显示的错误是error40. 错误如下:   二.环境介绍 1.我的数据库是sqlserver2008,使用的开发环境是vs2013,.net4.0 2.我的数据库安装采用的命名实

DllMain加载其他DLL造成的死锁问题及其解决办法

  使用VS 2008新建一个MFC ActiveX工程,因为在工程里要用到GDI+.我习惯把初始化GDI+库的代码放在应用程序类的InitInstance函数,对应的销毁代码放在ExitInstance函数.具体如下: 先在应用程序类里定义一个数据成员:         [cpp] view plaincopy ULONG_PTR   m_gdiplusToken;       然后添加初始化GDI+库的代码和对应的销毁代码:        [cpp] view plaincopy BOOL