Database common deadlock causes and processing __ Database

A database is a shared resource that is used by multiple users, and when more than one user accesses the data concurrently, multiple transactions are generated in the database and the same data is accessed at the same time. Without control of concurrent operations, incorrect data can be read and stored, which destroys the consistency of the database. Lock is a very important technology to realize concurrency control of database. In the actual application of the lock-related anomalies often encountered, when two transactions need a set of conflicting locks, but not to continue the transaction, there will be deadlock, seriously affect the normal implementation of the application.
There are two basic types of locks in the database: exclusive locks (Exclusive Locks, i.e., x locks) and shared locks (Share Locks, or S locks). When a data object is added to the exclusive lock, no other transaction can read and modify it. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic lock types to concurrently control the transactions of the database.
The following is a summary of the common deadlocks and solutions that are caused by both types of locks:I. Alternating between transactions on the order of access to resourcesReason for the occurrence:
A User A accesses table A (lock table a) and then accesses table B; Another user B accesses table B (locked table B), then attempts to access table A; Then user A because User B has locked table B, it must wait for User B to release table B to continue, same user B to wait for User a release table A to continue, This is where the deadlock is created. Workaround:
This deadlock is more common, due to the bug in the program, in addition to the adjustment of the logic of the program there is no other way. Careful analysis of the logic of the program, for a database of multiple table operations, try to do the same in the same order, as far as possible to avoid locking two resources at the same time, such as operations A and B tables, always in the order of a after B processing, must be locked at the same time two resources, to ensure that at any time should be in the same order to lock the resources.two. Concurrent modification of the same recordReason for the occurrence:
User A queries a record, then User B modifies the record, at which point the nature of the lock in User A's transaction is raised to an exclusive lock by the query's shared lock attempt, and the exclusive lock in User B must wait a to release the shared lock because a has a shared lock. An exclusive lock that cannot be raised because of a exclusive lock on B cannot release the shared lock, and a deadlock occurs. This deadlock is more covert, but often occurs in larger projects.
The general update mode consists of a transaction that reads records, gets a shared (S) lock on a resource (page or row), and then modifies the row, which requires the lock to be converted to an exclusive (X) lock. If two transactions obtain a shared schema lock on the resource and then attempt to update the data at the same time, a transaction attempts to convert the lock to an exclusive (X) lock. The conversion of shared mode to exclusive lock must wait for some time because the exclusive lock of one transaction is incompatible with the shared mode lock of other transactions; The second transaction attempted to obtain an exclusive (X) lock for an update. A deadlock occurs because two transactions are converted to exclusive (X) locks, and each transaction waits for another transaction to free a shared-mode lock. Workaround:
A. Use optimistic locks for control. Most optimistic locks are implemented based on the data version (versioning) recording mechanism. That is, adding a version identity to the data, in a version solution based on a database table, typically by adding a "version" field to the database table. When you read out the data, read the version number together, and then when you update it, add one to this version number. At this point, the version data for the submitted data is compared to the current version information of the corresponding record in the database table, and is updated if the submitted data version number is greater than the current version number of the database table, otherwise it is considered to be an expired data. The optimistic locking mechanism avoids the locking overhead of the database in the long transaction (both user A and User B do not lock the database data), and greatly improves the overall performance of the system under the large concurrent volume. Hibernate has a built-in optimistic lock implementation in its data access engine. It should be noted that because the optimistic locking mechanism is implemented in our system, user update operations from external systems are not controlled by our system and may cause dirty data to be updated to the database.
B. Use pessimistic locks for control. Pessimistic locks are most often dependent on the database's lock mechanism, such as the Oracle SELECT ... for UPDATE statement, to ensure maximum operational exclusivity. But then comes the massive cost of database performance, especially for long transactions, which are often unsustainable. such as a financial system, when an operator reads a user's data, and the user data read out on the basis of modification (such as changing the user account balance), if the use of pessimistic locking mechanism, it means that the entire operation (from the operator read data, start to modify until the completion of the submission of changes to the whole process, Even when the operator goes to make coffee in the middle of the day, the database record is always locked, and it can be expected that, if faced with hundreds of concurrent cases, this will lead to disastrous consequences. Therefore, the use of pessimistic lock control must be considered clearly.
C. SQL Server can support update locks
To resolve deadlocks, SQL Server introduces an update lock, which has the following characteristics:
(1) Lock condition: When a transaction executes an UPDATE statement, the database system assigns an update lock to the transaction first.
(2) Unlock the condition: When the data is read, perform the update operation, the update lock will be upgraded to exclusive lock.
(3) Compatibility with other locks: update locks are compatible with shared locks, that is, a resource can place both an update lock and a shared lock, but a maximum of one update lock is placed. Thus, when more than one transaction updates the same data, only one transaction can get an update lock, then the update lock is upgraded to an exclusive lock, and other transactions must wait until the end of the previous transaction to obtain an update lock, which avoids the deadlock.
(4) Concurrent performance: Allows multiple transactions to read the locked resource at the same time, but does not allow other transactions to modify it.
Examples are as follows:

T1:
BEGIN tran
SELECT * FROM table (updlock) (add update lock)
Update table set column1= ' Hello '
T2:
begin tran< C5/>select * FROM table (updlock)
Update table set column1= ' World '

Update lock means: "I only want to read now, you others can also read, but I may do the update operation, I have acquired from the shared lock (used to read) to exclusive locks (used to update) the eligibility." A thing can only have an update lock to qualify for this.
T1 executes the Select and updates the lock.
T2 running, ready to update the lock, but found that there is an update locked there, had to wait.
When later there are User3, user4 ... When you need to query the data in table tables, you can query and improve efficiency because the T1 select is blocked in execution. three. Improper indexing causes the whole table scan to appear reason:
If a statement that does not meet the criteria is executed in a transaction, a full table scan is performed, and a row-level lock is raised to a table-level lock, which can easily result in deadlocks and blocking when a number of such transactions are executed. Similarly, when the amount of data in a table is very large and the index is too small or unsuitable, a full table scan is often made, and the end application system slows down and eventually blocks or deadlocks. Workaround:
Do not use queries that are too complex to correlate multiple tables in SQL statements, use the execution plan to analyze SQL statements, and establish appropriate indexes for SQL statements with full table scans. Four. The scope of the transaction blockade and waiting for each other

I can refer to this article