Transferred from: http://blog.csdn.net/eseaqyq/article/details/7795023
————————————————————————————————————————————————————
Speaking of the deadlock in the data, has been repeatedly encountered in the written questions. Today is a summary of the database deadlock, abortions, roar!
First, let's look at a few definitions: 1. Deadlock the so-called deadlock: refers to two or two processes in the execution process, because of the competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to proceed. At this point the system is in a deadlock state or the system generates a deadlock, and these processes, which are always waiting on each other, are called deadlock processes. Because the resource consumption is mutually exclusive, when a process requests resources, so that the process without external assistance, never allocated the necessary resources and can not continue to run, which creates a special phenomenon of deadlock. 2. A database database is a warehouse that organizes, stores, and manages data in accordance with its data structure. 3. The necessary conditions for generating a deadlock
- Mutex: Refers to a process that uses an exclusive use of the resources assigned to it, that is, a resource is occupied by only one process over a period of time. If there are other processes requesting resources at this time, the requestor can wait until the resource-occupying process is freed.
- request and hold condition: means that the process has maintained at least one resource, but a new resource request has been made, and the resource has been occupied by another process, at which point the request process is blocked, but the other resources that it obtains are persisted.
- No deprivation condition: refers to the resources that the process has acquired, cannot be deprived of until it is exhausted, and can only be released by itself when it is exhausted.
- Loop Wait Condition: When a deadlock occurs, there must be a process-the circular chain of the resource, that is, the P0 in the process collection {p0,p1,p2,,pn} is waiting for a resource that is P1, P1 is waiting for the resource to be occupied by P2 ..., PN is waiting for resources that have been consumed by P0.
4. Basic ways to handle deadlocks 1) prevent deadlocks. This is a simpler and more intuitive approach to proactive prevention. The method is to prevent deadlocks by setting certain constraints to destroy one or more of the four necessary conditions that produce deadlocks. The prevention of deadlocks is a more easily implemented method and has been widely used. However, due to the often restrictive conditions imposed, the system resource utilization and system throughput can be reduced. 2) avoid deadlocks. This method is also a pre-prevention strategy, but it does not have to take a variety of restrictive measures to destroy the four necessary conditions to generate deadlocks, but in the dynamic allocation process of resources, in a way to prevent the system from entering the unsafe state, so as to avoid the occurrence of deadlocks. 3) Detection of deadlocks. This method does not need to take any restrictive measures beforehand, nor does it have to check if the system has entered an unsafe zone, which allows the system to deadlock during operation. However, the detection mechanism set up by the system can detect the occurrence of the deadlock in time, and determine the process and resources related to the deadlock, then take appropriate measures to remove the deadlock from the system. 4) Unlock the deadlock. This is a measure to match the detection of deadlocks. When a deadlock has been detected in the system, the process must be freed from the deadlock state. A common practice is to undo or suspend some processes in order to reclaim some resources, and then assign those resources to a process that is already in a blocked state and make it ready to continue running. Deadlock detection and lifting measures, it is possible to make the system to achieve better resource utilization and throughput, but the implementation of the most difficult. Ii. What is the deadlock in the database? 1.sql the mechanism of the server lock
Locks are generated for all SQL Server activities. The smaller the locked unit, the more capable of increasing concurrency, but the greater the overhead of managing locks. How to find the balance point, so that concurrency and performance are acceptable is the difficulty of SQL Server.
There are several types of SQL Server:
1. Shared lock
Used for read-only operations (SELECT) to lock a shared resource. Shared locks do not prevent other users from reading, but they prevent other users from writing and modifying them.
2, update the lock
An update lock is an intent lock that occurs when a thing has requested a share and attempts to request an exclusive lock. For example, when two things use a shared lock on a few rows of data, and while attempting to acquire an exclusive lock to perform an update operation, a deadlock occurs: both wait for the other party to release the shared lock and implement an exclusive lock. The purpose of the update lock is to let only one thing get an update lock to prevent this from happening.
3, exclusive lock (row it lock)
Only one exclusive lock can be used on one resource at a time, and all other locks are blocked including shared indents. Write is exclusive lock, can effectively prevent ' dirty read '
4. Intent Lock
Use intent locks before using shared and exclusive locks. View the intent lock from the level of the table to determine whether things can get shared locks and exclusive locks, improve the performance of the system, do not need to check from the master or the line.
5. Program Lock
Sch-m,sch-s. When the database structure changes with SCH-M, the query is compiled with Sch-s. These two locks do not block any thing locks, including exclusive locks.
Read is a shared lock, write is an exclusive lock, read first after the updated operation is update lock, update lock succeeds and change the data when update lock upgrade to exclusive lock.
2. Below we see how to avoid deadlock 1 when using transactions, as far as possible to shorten the logical processing of transactions, early commit or rollback transactions;
2 Set the deadlock timeout parameter to a reasonable range, such as: 3 minutes-10 minutes; Over time, automatically abandon this operation, to avoid the process of hanging;
3 All SPS must have error handling (via @Error
4 generally do not modify the default level of SQL Server transactions. Forced lock is not recommended
5 Optimize the program, check and avoid the deadlock phenomenon;
1) Arrange table access order reasonably
2) Try to avoid user intervention in the transaction and try to make a transaction less task.
3) The use of dirty reading technology. Dirty reads avoid lock collisions because they do not lock the table being accessed. In a client/server application environment, some transactions often do not allow the reading of dirty data, but under certain conditions, we can use dirty read.
4) Data access time domain Discretization method. The time domain discretization method of data access refers to the use of various control methods in the client/server structure to control the period of access to objects in the database or database. Mainly through the following ways: The reasonable arrangement of the background transaction execution time, the use of workflow to the background transaction management unified. Workflow in the management of tasks, on the one hand limit the number of threads of the same class of tasks (often limited to 1), to prevent excessive consumption of resources; On the other hand, reasonable scheduling of different tasks to execute timing, time, as far as possible to avoid multiple background tasks at the same time, in addition, avoid running background tasks during peak hours of reception
5) data storage space discretization method. The data storage space discretization method refers to the use of various means to spread the data logically in a table into a number of discrete spaces, in order to improve the access performance of the table. Mainly by the following methods: First, the large table by row or column decomposition into a number of small tables; Second, according to different user groups decomposition.
6) Use the lowest possible isolation level. The isolation level is the degree to which multi-user transactions are isolated to ensure the integrity and consistency of database data, SQL92 defines 4 isolation levels: uncommitted read, read-committed, Repeatable read, and serializable. If you choose too high an isolation level, such as serializable, although the system can achieve greater isolation to ensure the integrity and consistency of the data, but the conflict between the transactions and the chance of deadlock greatly increased, greatly affecting the system performance.
7) Use bound Connections. Bound connections allows two or more transactional connections to share transactions and locks, and any one transaction connection to request a lock is like another transaction requesting a lock, so these transactions can be allowed to share data without locking conflicts.
8) Consider using optimistic locking or having the transaction get an exclusive lock first.
can you see if there is a deadlock in the following situation? A. Select, update,selectb.select,select,updatec.select,update,updated.update,select,update above these four kinds of circumstances which will appear deadlock, which kind of deadlock? What is your reason? 3. Let's take a look at the following deadlock problem!
[SQL]View Plaincopyprint?
- <span style="font-family: ' Microsoft Yahei ';" >-a transaction first update the Table1 table, then delay 30 seconds, and then update the Table2 table;
- BEGIN Tran
- Update table1 set a=' AA ' where b=' B2 ';
- -This will generate an exclusive row lock in the Table1 until the transaction is complete before releasing the lock.
- WAITFOR DELAY ' 00:00:30 ';
- --Entry delay
- Update table2 set d=' D5 ' where e=' E1 ';
- Commit Tran
- --B Transaction First update the Table2 table, then delay 10 seconds, and then update the Table1 table;
- BEGIN Tran
- Update table2 set d=' D5 ' where e=' E1 ';
- --this will generate an exclusive row lock in the Table2 until the transaction is complete before releasing the lock
- WAITFOR DELAY ' 00:00:10 '
- --Entry delay
- Update table1 set a=' AA ' where b=' B2 ';
- Commit tran</span>
A Transaction First updates the Table1 table, then delays 30 seconds, and then updates the Table2 table; Begin tranupdate table1 set a= ' AA ' where b= ' B2 ';--this will generate an exclusive row lock in Table1 until the transaction is complete before releasing the lock. WAITFOR DELAY ' 00:00:30 ';--Enter delay update table2 set d= ' d5 ' where e= ' E1 '; commit tran--b transaction First update table2 table, then delay 10 seconds, then update table1 table ; Begin tranupdate Table2 set d= ' d5 ' where e= ' E1 ';--this will generate an exclusive row lock in Table2 until the transaction is complete before releasing the lock waitfor delay ' 00:00:10 '--Enter delay update T Able1 set a= ' AA ' where b= ' B2 '; Commit Tran
If both transactions are performed concurrently, A, b two transactions must wait for the other party to release the exclusive lock, thus forming a deadlock.
The deadlock in the database, the thing.
