程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> 記一次公司倉庫數據庫辦事器逝世鎖進程及處理方法

記一次公司倉庫數據庫辦事器逝世鎖進程及處理方法

編輯: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毛病日記給出了足夠的信息定位逝世鎖成績,所以碰到成績的時刻必定要剖析清晰日記

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved