When it comes to SQL Server, the most frustrating thing is the locking mechanism. In the default Read Committed isolation mode, even the most basic select operation has to apply for a variety of granularity locks, and in the process of reading data will continue to lock upgrade, conversion. In a non-uncommitted read isolation level, a select operation adds an S lock on each read record or key value (when the release depends on whether the record is returned and the isolation level), plus s lock on the key value on each index used, and an IS lock on each page and table read ... Update, insert, and delete operations request locks in a much larger amount and complexity. Deadlocks and blocking are the product of SQL Server's transaction isolation. Sometimes a transaction is isolated on the same table, a deadlock occurs at a higher concurrency level, and a low level of concurrency occurs because of blocking. So deadlock problem locating and solving and blocking have figured out where to solve the deadlock the most critical is to find the deadlock between the two sides or the multi-party competing resources is which. Below you'll find a case study of a real production environment that was recently encountered, parsing the deadlock crawl and resolving the process. A foreign logistics company operating system: Windows Server Enterprise x64 database: SQL Server Enterprise X64 Data Volume: 300GB about daily transaction concurrency is relatively high status: due to a A large number of parallel runs of the business SP result in deadlocks, and a deadlock occurs when a party as a sacrifice resource has a long rollback process that causes important business tables to be locked and business interrupts to troubleshoot the process: You must first find the deadlock resource: 1) Create a new trace from SQL Server Profiler, Event selection can be streamlined to allow us to observe deadlocks and select the "Locks" event.
Lock:deadlock and deadlock graph below, trace file size set to 100M upper limit for analysis 2) after a period of time to stop the crawl, it is very intuitive to see the deadlock has been seen, and all the deadlock graph to get a deadlock graph analysis, Deadlocks occur on the same resource:
By the end of this step, we can only confirm that the deadlock has occurred, and that it has not been able to obtain a specific deadlock statement and resource information on the same resource, and the problem cannot be solved fundamentally. 3) in order to check the look dead lock information, the database engine provides a monitoring tool: Trace identity (1222). Open this trace switch, and all the obtained deadlock information is written to the SQL Server error log for further analysis. This step opens the switch and runs DBCC TRACEON (1222,-1) in SSMs; 4) The deadlock pattern from trace look dead occurs very frequently, in order not to let the log grow too large, after 2-3 minutes turn off the switch. It is important to run DBCC TRACEOFF (1222,-1) in SSMs, 5) Open SQL Server errorlog, find the deadlock output information, this output is very rich and complex, here only a few important points we need to pick out
The deadlock information starts with the Deadlock-list keyword (upside down), deadlock victim shows the victim of the deadlock, the process ID shows the progress ID number, and because it is not complete, it also contains a lot of deadlock information, such as the process SPID number, the transaction isolation level, The batch operation currently in progress, the statement that is currently running, the resource in the request, and so on. The deadlock batch and deadlock statements are obtained by parsing the error log: Exec Usp_obal_import_so, find the deadlock statement: Delete from T_po_detail where Po_number in (select Po_number from T_so_po where [email protected]_vchsoid and [email Protected]_vchwhid] and[email protected]_vchwhid, this is a statement in Usp_obal_import_so, lock resource: Table tbl_po_detail_generic (User script timed execution obtained), but this SP does not operate at all Tbl_po_detail_ Generic, is there something wrong? 6) We can look at the execution plan of this statement in SSMs, run the statement before running set statistics profile in SSMs or select "Include Actual execution plan" in the "Query" submenu, we use the second more intuitive, as follows
It is obvious that in the execution plan you can see that tbl_po_detail_generic has a full table scan operation, and then a hash connection with the T_po_detail table. A full table scan results in a table lock on the table for each statement execution, and a drill-down reason finds that Tbl_po_detail_generic's foreign KEY constraint causes the Tbl_po_detail_generic table to be manipulated each time the T_po_detail data is deleted. 7) Here to analyze the basic end of the deadlock work, there are two ways to solve the problem: first, according to the implementation plan in tbl_po_detail_generic to establish an appropriate index to avoid the table scan; second, if the business logic permits, delete the foreign key constraint. Summary: To really do from the source to reduce the probability of deadlock, or from the program itself to do a good job. If you are unable to modify the program, consider removing deadlocks from several other aspects:
1 adjust the index to adjust the execution plan, reduce the number of lock applications;
2 use ' nolock ' parameter, let SELECT statement do not request S lock, reduce lock request number
3 upgrade lock granularity to turn deadlocks into blocking issues
4 using snapshot Isolation levels snapshot level
SQL server deadlock troubleshooting Experience-based on SQLProfiler
