Causes of MySQL Deadlock and How to Avoid Deadlock in MySQL

How to Avoid Deadlock in MySQL?

4.png

1. Types of MySQL locks and analysis of MySQL lock

Types of MySQL locks:

There are three types of MySQL locks: page-level lock, table-level lock and row-level lock.

Table-level lock: Low overhead, fast locking; no deadlock occurs; large locking granularity, the highest probability of lock conflict and the lowest concurrency.

Row-level lock: High overhead, slow locking, deadlock may occur, minimum lock granularity, the lowest probability of lock conflict and the highest concurrency.

Page-level lock: the overhead and locking time are between table-level locking and row-level lock; deadlock may occur; locking granularity is between table-level lock and row-level lock; concurrency is general.

Algorithm:

Next-key locks: lock the record (data) and gap before the record at the same time.

Gap lock: only the gap before the record is locked.

Record lock: only records (data) are locked.

Actually, next-key locks equal gap locks plus record locks.

2. Causes of deadlocks and examples

(1)Causes

A deadlock refers to the phenomenon of two or more processes waiting for each other due to resource contention during the execution process. If there is no external intervention, they will not be able to proceed. And now, the system is in a deadlock. These processes that are always waiting for each other are called deadlock processes. Table-level locks do not cause deadlocks. So, the solution to deadlocks is mainly aimed at the most commonly used InnoDB.

The key to deadlock is that the lock order of two (or more) sessions is inconsistent.

Then the corresponding key to deal with the deadlock is to lock different sessions in order.

(2)Occurrence

Case 1

Requirement: divide the investment into several pieces and randomly distribute them to the borrower.

The initial program process idea is like this:

When an investor makes his investment, the program divides the amount into several randomly, and then randomly selects a few from the borrower table, and then updates the balance in the borrower table one by one.

For example, if two users invest at the same time, the amount of user A is randomly divided into 2 shares, which are distributed to the borrowers 1, 2 in order.

The amount of B is randomly divided into 2 shares and distributed to borrowers 2, 1 in order.

Because the order of locking is different, deadlocks will occur.

The improvement to this problem is so easy that locking all the borrowers who get shares at once will make it.

Mysql will automatically sort the list values of in column from small to large, and locks are also added one by one from small to large.

For example (In the following, session id is the primary key):

Session1:

Session2:

Waiting for lock...

In fact, the record with id=10 is not locked at this time, but the record with id=5 has been locked. The lock is waiting here with id=8.

Session3:

Waiting for lock...

Session4:

In other sessions, id=5 cannot be locked, but id=10 can be locked.

Case 2:

In development, programmers often make such judgment requirements: query based on a field value (with index), if it does not exist, insert; otherwise, update.

Take id as the primary key as an example. There is no row with id=22 yet.

Session1:

session2:

Session1:

Waiting for lock...

Session2:

When locking existing rows (primary key), mysql only has row locks.

When locking a row that does not exist (even if the condition is the primary key), mysql will lock a range (with gap lock).

The locked range is:

(Infinitesimal or less than the maximum value of locked id in the table, infinite or greater than the minimum value of locked id in the table)

For example: if there is an existing id (11, 12) in the table, then lock (12, infinity).

If the id currently in the table is (11, 30), then lock (11, 30)

The solution to this deadlock is:

Use MySQL-specific syntax to solve this problem. Because in an insert statement, no matter whether the inserted row exists or not, there will only be a row lock for the primary key.

Case 3

Session2:

Waiting for lock...

Session1:

This is similar to the case 1, except that session1 does not perform normally.

Session2 is waiting for the lock of id=9 in Session1, and session2 is holding the lock from 1 to 8 (note that the range from 9 to 19 is not locked by session2). Finally, session1 has to wait for session2 when inserting a new row, so the deadlock happens.

This deadlock generally does not appear in business requirements. Because you have locked id=9, but you want to insert the row with id=7, which is not logical enough. Of course, there must be a solution, which is to reorganize the business requirement and avoid this.

271.png

In general, two sessions hold a lock respectively through a SQL, and then access each other's locked data, causing a deadlock.

Case 5

272.png

The same locking data involved in two single SQL statements with different locking sequence leads to a deadlock.

Case 6

The deadlock scenario is as follows:

Table Structure:

The three columns a, b, and c are combined into a unique index, and the primary key index is the id column.

Transaction isolation level:

Only one SQL in each transaction:

The execution of the SQL:

Deadlock log:

As we all know, deleting a record on InnoDB is not a physical deletion in the true sense, but marking the record as deleted. (Note: These records marked as deleted status will be recovered by the background Purge operation and physically deleted. However, the deleted records will be stored in the index for a period of time.) Under the RR isolation level, how to lock a record that meets the query condition of the unique index but is a deleted record?

InnoDB's processing strategy here is different from the previous two strategies, or it can be regarded as a combination of the first two strategies. For deleted records that meet the conditions, InnoDB will add next key lock X to the record (add X lock to the record itself meanwhile lock the GAP before the record to prevent the insertion of new records that meet the conditions). Three cases of unique query have three locking strategies correspondingly, which are summarized as follow:

Here, we see the next key lock, is it familiar? The locks of transaction 1 and transaction 2 in the waiting state in the previous deadlock are all next key locks. After you understand these three locking strategies and construct a certain concurrency scenario, the cause of the deadlock is already apparent. However, there is another prerequisite strategy that needs to be introduced. That is the deadlock prevention strategy adopted internally by InnoDB.

Find a record that meets the conditions, and the record is valid, then add X lock to the record, No Gap lock (lock mode X locks rec but not gap);

Find a record that meets the conditions, but the record is invalid (marked as a deleted record), add a next key lock to the record (at the same time lock the record itself and the Gap before the record: lock mode X);

If a record that meets the conditions is not found, the first record that does not meet the conditions is added to a gap lock to ensure that no record that meets the conditions is inserted (locks gap before rec);

Deadlock prevention strategy:

Inside the InnoDB engine (or inside all databases), there are many types of locks: transaction lock (row lock, table lock), Mutex (protecting internal shared variable operations), RWLock (also known as Latch, protecting internal Page read and modify).

Each page of InnoDB is 16K. When reading a page, you need to add an S lock to the page, and when you update a page, you need to add an X lock to the page. In any case, operating a page will lock the page. After a page lock is added, the index records stored in the page will not be modified concurrently. Therefore, in order to modify a record, how does InnoDB deal with it internally:

According to the given query conditions, find the page where the corresponding record is stored;

Add X lock (RWLock) to the page, and then search for records that meet the conditions in the page;

In the case of page locks added, add transaction locks to the records that meet the conditions (row lock: according to whether the record meets the query conditions, whether the record has been deleted, it corresponds to one of the three locking strategies mentioned above);

Deadlock prevention strategy:

Compared to transaction lock, page lock is a short-term lock, while transaction lock (row lock, table lock) is a long-term lock. Therefore, in order to prevent deadlock between page locks and transaction locks. InnoDB has implemented a deadlock prevention strategy: holding transaction locks (row locks, table locks), you can wait to obtain page locks. On the contrary, holding page locks, you cannot wait to hold transaction locks.

According to the deadlock prevention strategy, when the page lock is held and the row lock is added, if the row lock needs to wait, then release the page lock, and wait for the row lock. At this time, the row lock acquisition does not have any lock protection, so after adding the row lock, the record may have been modified concurrently. Therefore, the page lock should be added back at this time, the status of the record should be judged again, and the record should be locked again under the protection of the page lock. If the record is not modified concurrently at this time, the second lock can be completed quickly because the lock of the same mode is already held. However, if the record has been modified concurrently, it may cause the deadlock problem mentioned earlier in this article.

The corresponding function for the above InnoDB deadlock prevention processing logic is row0sel.c::row_search_for_mysql(). You can track and debug the processing flow of this function, which is very complicated but concentrates the essence of InnoDB.

Analyze the cause of deadlock:

After these preparation and knowledge of the three locking logics of the Delete operation and the deadlock prevention strategy of InnoDB, let’s go back and analyze the deadlock problem mentioned at the beginning of this article, and you can find it is easy.

First, suppose there is only one record in dltask: (1, ‘a’, ‘b’, ‘c’, ‘data’). Three concurrent transactions execute the following SQL at the same time:

And when the following concurrent execution logic is generated, a deadlock will occur:

274.png

The concurrent process analyzed above fully shows the cause of the deadlock in the deadlock log. In fact, according to the order between step 6 of transaction 1 and step 3/4 of transaction 0, another situation may occur in the deadlock log, that is, the lock mode waiting for transaction 1 is X lock + No on the record. Gap lock (lock_mode X locks rec but not gap waiting).

Several prerequisites for this type of deadlock:

The Delete operation is aimed at deleting the equivalent query on the unique index; (Deletion under the range will also cause deadlock, but the deadlock scenario is different from the scenario analyzed in this article)

At least 3 (or more) concurrent delete operations;

Concurrent delete operation is possible to delete the same record, and it is guaranteed that the deleted record must exist;

The transaction isolation level is set to Repeatable Read, and the innodb_locks_unsafe_for_binlog parameter is not set (this parameter is FALSE by default); (Read Committed isolation level will not have a deadlock because there will be no gap lock, no next key)

InnoDB storage engine is used; ( MyISAM engine has no lock at all)