Cow man Note----(Deadlock problem location and resolution)

 1--Deadlock problem location and resolution 2 3-in order to resolve a deadlock problem, the SQL Server Database engine deadlock monitor periodically checks for a deadlock task 4--If the monitor detects this dependency cycle relationship, it chooses one of the tasks as the victim, then 5--terminates its transaction and prompts for an error 。  This is the deadlock error that the user will encounter 6 7--A deadlock can occur in resource 8--it should be explained that the deadlock does not occur only on the lock resource, the following types of resources can cause blocking, and 9--eventually lead to deadlock 10 11--1, Locks: For example: page, row, metadata and lock on application 12 --2, worker threads: If a queued-waiting task has a resource that blocks other worker threads, it can also cause a deadlock of 13-for example, when a session 1session1 initiates a transaction and acquires a shared lock (S lock) 1row1 The row, it goes to sleep. 14-An active session running on all available worker threads is attempting to acquire an exclusive lock (x Lock) for row ROW1. 15--When the session Session1 awake, because the worker thread cannot be fetched, the transaction cannot be committed and the 16-row ROW1 Lock is released, resulting in a deadlock of 17 18--3, Memory: 19-deadlock occurs when concurrent requests are waiting for memory and the current available memory does not meet their needs. For example: two concurrent queries (Q1 and Q2) are executed as user-defined functions, respectively, to obtain the--10MB and 20MB of memory. If each query requires 30MB and the total usable memory is 20MB, then Q1 and Q2 must be 21--waiting for each other to free up memory, resulting in a deadlock 22 23--4, a related resource for parallel query execution: When a statement is run with multiple threads, the thread and thread may be 24--deadlock 25 26--Deadlock detection Test 27-The deadlock SQL listed above can be detected using his deadlock detection mechanism. Deadlock detection is performed by Lock Monitor 28, the system thread, which periodically searches for all tasks in SQL. The default time interval is 5 seconds 29 30-After a deadlock is detected, SQL chooses one of the threads to end the deadlock as a deadlock victim. Database Engine 31--terminates the currently executing batch of the thread, rolls back the deadlock victim's transaction, and returns the 1205 error to 32--the application. Rolling back a deadlock victim frees all locks held by the transaction. This causes the transaction of the other thread to unlock 33--and continue running. By default, the database engine chooses session 34 that runs the transaction with the least rollback cost-as a deadlock victim 35 36 37 38-the difference between deadlock and blocking 39-many users confuse deadlocks with blocking. When a transaction request is locked on a resource by another transactionLock, 40-The transaction that made the request waits until the lock is released by someone else and applies to it. At this point 41--is blocking. By default, unless set Lock_timeout 30 30 is the time-out value of 42--Otherwise the SQL transaction waits until he times out. So blocking tends to last for a long time 43--The concurrency of symmetry has a significant impact. Some people call this phenomenon "locked up" because the wait will always be 44--persistent, and SQL will not intervene 45 46 47 48--While a deadlock is a wait between two or more processes, but because SQL has a database engine deadlock detection 49-scheme, at least 5 seconds will eliminate an existing deadlock  , so the impact on the end-user is some 50--instruction request failed, the performance impact is often not as serious as blocking 51 52 53--Problem Location 54--to check the look Dead lock information, SQL provides monitoring tools: Two trace Flags: 1204 and 1222 55--and SQL Trace 56 57--trace flag 1204 and trace flag 1222 58--When a deadlock occurs, trace flags 1204 and 1222 return the captured information to the SQL error log 59--trace Flag 1204 reports deadlock information formatted by each node involved in the deadlock 60-- He is a feature of the previous version of SQL2005. Trace flag 1222 is the SQL2005 only start 61-some new features that will format the deadlock information in the order of first pressing process and then pressing 62--resource. The result of 1222 is not only a basic 1204 of all information, but also a lot of 1204 63-no information. So after SQL2005 can be used directly after the trace flag 1222来 64--Trace Deadlock 65 66 67--Open trace flag the DBCC TRACEON (1222,-1) 69 70--and 1204 output results are different, 1222 of the output is divided into 3 parts, and Because the amount of information is more, the length is much longer than the result of 71--1204. The deadlock participant is not shown directly by the SPID, but with processxxxx method 72-so it takes some patience to understand. However, the content contained is much richer than the output of 1204 73 74 75--1222 contains 3 parts 76--The first part: 77--Deadlock Sacrifice Process 78 79--Part Two: process-list 80--Deadlock occurs process information 81 82--Part III  : Resource-list 83--resource information for deadlock occurred 8485 86 87--Deadlock graph event (locks-deadlock graph) 88--There is also an event in SQL Trace that represents a graphical description of the tasks and resources involved in deadlocks 89 90--When you want to analyze deadlocks, you typically combine trace flags 1222 and SQL Trace 。 First in the SQL Errorlog 91--Search for the output of trace flag 1222, according to the output time in the trace to find the corresponding connection 92--and then can be detailed analysis of the cause of the deadlock 94----------------------------solution Approach---------------------------------------------------------------------96--although deadlocks cannot be completely avoided, adherence to specific coding conventions can minimize the chance of deadlocks occurring. 97--The following methods help minimize deadlocks: 98-Access Object 99 in the same order-avoid user interaction in transactions 100-keep transactions short and in one batch 101-use lower isolation LEVEL 102-Adjust statement execution plan, reduce lock request number 103 104 105 106 107--Access Object 108 in the same order--if all concurrent transactions access the object in the same order, the likelihood of a deadlock is reduced. For example, if 109--Two concurrent transactions get the lock on the supplier (producer) Table first, then get the lock on consumer (consumer) Table 110. Another transaction will be blocked on the supplier table until one of the transactions is completed 111--when the first transaction commits or rolls back, the second transaction will continue to execute so that no deadlock occurs 112 113 114-Avoid user interaction in the transaction 115-- Avoid writing transactions that contain user interaction, because batches that do not have user intervention run at 116 faster-much faster than the user must manually respond to queries. For example, if a transaction is waiting for user 117-input, and the user goes to lunch or even goes home for weeks, the user delays the completion of the transaction by 118-which reduces the throughput of the system, and any locks held by the transaction are only released after the transaction commits or rolls back 119. Even if there is no deadlock, access 120-other transactions of the same resource will be blocked until the resource-intensive transaction is completed 121 122 123-Keep the transaction short and in a batch of 124-it is more likely that a deadlock can occur concurrently in the same database with multiple transactions that require long running time. 125--The longer the transaction runs, the time he holds an exclusive lock or update lockThe longer it will be 126--blocking other activities and possibly causing a deadlock 127--keeping the transaction in one batch can minimize network traffic round trips in the transaction, reduce 128--The latency of completing transactions and releasing the lock may incur 129 130 131--using a lower isolation level of 132-- Determines whether a transaction can run at a lower isolation level. Using a lower isolation level (for example, read-committed) 133-it takes less time to hold a shared lock than with a higher isolation level (for example, serializable). This is 134--reduces lock contention, helps to avoid blocking and deadlocks 135 136 137--Adjust statement execution plan, reduce lock request number 138--similar to blocking, a major reason for deadlocks is to do one thing that requires more resources. 139--For example, SQL needs to scan the entire table to find the modified records, while in the process of scanning, SQL 140--locks each record read. If the execution plan is seek, the number of records that need to be read compares 141-less, the number of locks requested will be less, may be able to avoid deadlocks 142 143 144 145-from the above recommendations, in addition to the same order to access the object this requirement, avoid blocking and deadlock many 146-the place is connected, So the means used are very similar. The only difference is that sometimes a deadlock can be difficult to avoid in the case of a high concurrency of 147. This is one of the ways to solve the problem, which can be 148--Increase the lock granularity of the connection request, and let the deadlock party first encounter blocking because the other party has acquired a lock with a high granularity of 149--for example, a page lock or even a table lock. A temporary blockage instead of a deadlock, 150--allows the two sides to complete sequentially, albeit slightly slower

Cow man Note----(Deadlock problem location and resolution)