Shulou(Shulou.com)06/01 Report--

This article mainly explains "sql server how to troubleshoot deadlock optimization performance", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "sql server how to troubleshoot deadlock optimization performance" bar!

one。 Overview

I remember that in the past, when customers were using the software, there was an occasional deadlock problem, because the time of occurrence was uncertain and it was difficult to reproduce the problem. It was a bit difficult to solve the problem at that time. Now summarize the two common ways to view deadlocks.

1.1 the first is graphical monitoring:

Sqlserver-- > tools-- > after logging in to sqlserver profiler, select the following in the tracking properties:

The deadlock pattern heard by the monitor is as follows

The description here is roughly as follows: there are two processes, one process ID is 96, and the other ID is 348. The system automatically kill drops the process ID:96 and retains the transaction Commit of the process ID:348.

The above deadlock is due to the occurrence of PAG range locks in batch updates, and both processes are on the same partition index resource. ID96348 requests that you want to acquire an update lock (U), and the exclusive lock (x) is not released until the lock times out.

1.2 the second is to use log tracing (errorlog)

Opens the specified trace tag globally

DBCC TRACEON (1222) DBCC TRACEON (1204)

Use EXEC master..xp_readerrorlog to view the log.

Deadlock encountered.... Printing deadlock informationWait-for graphNULLNode:1 PAGE: 7 0x0000000575C7E970SPID 1V 6229275 CleanCnt:2 Mode:IX Flags: 0x3Grant List 3:Owner:0x00000004E99B7880 Mode:IX Flg:0x40 Ref:1 Life:02000000 SPID:219 ECID:0 XactLockInfo: 0x0000000575C7E970SPID: 219 ECID:0 Statement Type: UPDATE Line #: 84Input Buf: Language Event: exec proc_PUB_StockDataImportRequested by: ResType:LockOwner Stype:'OR'Xdes:0x0000000C7A905D30 Mode: U SPID:64 BatchID:0 ECID:59 TaskProxy: (0x0000000E440AAFE0) Value:0x8d160240 Cost: (0max 0) NULLNode:2 PAGE: CleanCnt:2 Mode:U Flags: 0x3Grant List 3:Owner:0x0000000D12099B80 Mode:U Flg:0x40 Ref:0 Life:00000001 SPID:64 ECID:0 XactLockInfo: 0x000000136B4758F0SPID: 64 ECID:0 Statement Type: UPDATE Line #: 108Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 907150277] several key messages displayed in the node:1 section:

PAGE 7vl PAGE 6229275 (database ID 7, 1 partition, number of 6229275 rows)

Mode: pattern intention exclusive lock of IX lock

SPID: 219 process ID

Event: name of the stored procedure executed by exec proc_PUB_StockDataImport

Several key messages displayed in the node:2 section

PAGE 7vl PAGE 5692366 (database ID 7, 1 partition, number of 5692366 rows)

Mode update lock for Mode:U lock

RPC Event: Proc remote call

SPID: 64 process ID

Victim Resource Owner:ResType:LockOwner Stype:'OR'Xdes:0x0000000C7A905D30 Mode: U SPID:64 BatchID:0 ECID:59 TaskProxy: (0x0000000E440AAFE0) Value:0x8d160240 Cost: (0Unip 0) deadlock-listdeadlock victim=process956f4c8process-listprocess id=process956f4c8 taskpriority=0 logused=0 waitresource=PAGE: 7Gan1 Value:0x8d160240 Cost 6229275 waittime=2034 ownerId=2988267079 transactionname=UPDATE lasttranstarted=2018-04-19T13:54:00.360 XDES=0xc7a905d30 lockMode=U schedulerid=24 kpid=1308 status=suspended spid=64 sbid=0 ecid=59 priority=0 trancount=0 lastbatchstarted=2018-04-19T13:53:58.033 lastbatchcompleted=2018-04-19T13:53:58.033 clientapp=.Net SqlClient Data Provider hostname=VMSERVER76 hostpid=16328 isolationlevel=read committed (2) xactid=2988267079 currentdb=7 lockTimeout=4294967295 Clientoption1=671088672 clientoption2=128056executionStackframe procname=Test.dbo.proc_CnofStock line=108 stmtstart=9068 stmtend=9336 sqlhandle=0x03000700c503123601ba25019ca800000100000000000000update dbo.pub_stockset UpdateTime=GETDATE () from pub_stock ajoin PUB_PlatfromStocktemp b on a.GUID=b.StockGuid

From the information above, you can see that what kill drops is the process. Id is process956f4c8.

Process spid=64

LockMode=U acquires update lock

Isolationlevel=read committed

Heap information executed by executionStack:

Storage name procname=Test.dbo.proc_CnofStock

The statement update dbo.pub_stock set UpdateTime=GETDATE ()..

The source of the event initiated by clientapp

1.3 finally summarize the solutions to avoid deadlock

Access objects in the same order.

Optimize the index to avoid full table scanning and reduce the number of lock applications.

Avoid user interaction in transactions.

Use isolation levels based on row versioning.

Change the committed read of the transaction default isolation level to a snapshot

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Use nolock to remove the shared lock, but if the deadlock occurs on the u or x lock, nolock does not work

Upgrade lock granularity (page lock, table lock) to block and replace deadlock

At this point, I believe that everyone on the "sql server how to troubleshoot deadlock optimization performance" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.