Shulou(Shulou.com)06/03 Report--

In view of why MySQL causes deadlock, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Analysis of Mysql Lock types and locking

MySQL has three levels of locks: page level, table level, and row level.

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

Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

Page locks: overhead and locking time are between table locks and row locks; deadlocks occur; lock granularity is between table locks and row locks, and concurrency

Algorithm:

Next KeyLocks lock, simultaneously locking the record (data), and locking the Gap in front of the record

Gap lock, do not lock the record, only record the previous Gap

Recordlock lock (lock data, not Gap)

So in fact, Next-KeyLocks=Gap lock + Recordlock lock

Causes and examples of deadlock

Cause

The so-called deadlock: refers to two or more processes in the implementation process, caused by the competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to move forward. At this point, it is said that the system is in a deadlock state or the system has a deadlock, and these processes that are always waiting for each other are called deadlock processes. Table-level locks do not produce deadlocks. So solving deadlocks is mainly aimed at the most commonly used InnoDB.

The key to deadlocks is that two (or more) Session locks are not in the same order.

Then the corresponding key to solve the deadlock problem is to make different session locks in order.

Generate an example

Case one

Demand: divide the investment money into several pieces and randomly distribute it to the borrower.

At first, the business program thought like this:

Investors invest, the amount will be randomly divided into several, and then randomly selected from the borrower table, and then through a select for update to update the balance in the borrower table and so on.

For example, if two users invest at the same time, the amount of user An is randomly divided into two parts and distributed to the borrower.

B the amount of user is randomly divided into two parts, which are distributed to the borrower.

Deadlocks occur very quickly, of course, because of the different order of locking.

The improvement to this problem is as simple as locking up all assigned borrowers at once.

Select * from xxx where id in (xx,xx,xx) for update

The list value mysql in in is automatically sorted from small to large, and locks are also added from small to large.

For example, (the following session id is the primary key): Session1:mysql > select * from T3 where id in (8pm 9) for update +-+ | id | course | name | ctime | +-+ | 8 | WA | f | 2016-03-02 11:36: 30 | | 9 | JX | f | 0-03-01 11:36:30 | +-+ rows in set (0.04 sec) Session2:select * from T3 where id in (2016) 5) for update Waiting for the lock.

In fact, the record of id=10 was not locked at this time, but the record of id=5 was already locked. The lock is waiting here in id=8.

If you don't believe me, please see.

Session3:mysql > select * from T3 where id=5 for update; lock waiting Session4:mysql > select * from T3 where id=10 for update +-+ | id | course | name | ctime | +-+ | 10 | JB | g | 2016-03-10 11:45: 05 | +-+ row in set (0.00 sec)

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

Case two

In development, this kind of judgment is often done: query based on field value (with index), insert if it does not exist, otherwise update.

Taking id as the primary key, there is no id=22 line Session1:select * from T3 where id=22 for update;Empty set (0.00 sec) session2:select * from T3 where id=23 for update;Empty set (0.00 sec) Session1:insert into T3 values. Session2:insert into T3 values (23); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

When there is a row lock (primary key), mysql only has a row lock.

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

The range of locks is:

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

For example, if the existing id in the table is (11,12)

Then lock it (12, infinity)

If the existing id in the table is (11,30)

Then lock it (115.30)

The solution to this deadlock is:

Insert into T3 (xx,xx) on duplicate key update xx='XX'

Use mysql-specific syntax to solve this problem. Because insert statements for primary keys, inserted rows will have only row locks, regardless of whether they exist or not.

Case three

Mysql > select * from T3 where id=9 for update +-+ | id | course | name | ctime | +-+ | 9 | JX | f | 2016-03-01 11:36: 30 | +-+ row in set (0.00 sec) Session2:mysql > select * from T3 where id insert into T3 values (7 'ae','a',now ()) ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

This is similar to the other cases, except that session1 doesn't play the cards according to common sense.

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

This generally does not occur in business requirements, because you lock the id=9, but want to insert the row of id=7, which is a bit jump, of course, there must be a solution, that is to re-manage business requirements and avoid this way of writing.

Case four

In general, two session hold a lock through a sql, and then access each other's locked data to create a deadlock.

Case five

Two single sql statements involve the same locking data, but the locking order is different, resulting in a deadlock.

Case 6

Deadlock scenarios are as follows:

CREATE TABLE dltask (id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto id', a varchar (30) NOT NULL COMMENT' uniq.a', b varchar (30) NOT NULL COMMENT 'uniq.b', c varchar (30) NOT NULL COMMENT' uniq.c', x varchar (30) NOT NULL COMMENT 'data', PRIMARY KEY (id), UNIQUE KEY uniq_a_b_c (a, b, c)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='deadlock test'

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

Transaction isolation level:

RR (Repeatable Read)

Each transaction has only one SQL:

Delete from dltask where axioms? And baked? And Cruise?

SQL's execution plan

Deadlock log

As we all know, deleting a record on InnoDB is not a physical deletion in the real sense, but identifying the record as a deleted state. (note: these records identified as deletion status will be reclaimed and physically deleted by the Purge operation at the background. However, records of deleted states are stored in the index for a period of time.) Under the RR isolation level, the query condition is satisfied on the unique index, but the record is deleted, how to lock it? The processing strategy of InnoDB here is different from the first two strategies, or a combination of the first two strategies: for delete records that meet the conditions, InnoDB adds next key lock X to the records (X locks the records themselves and locks the GAP before the records to prevent the insertion of new records that meet the conditions.) Unique query, three cases, corresponding to three locking strategies, summarized as follows:

Here, we see the next key lock. Does it look familiar? By the way, transaction 1 and transaction 2 in the previous deadlock are all next key locks. Understand these three locking strategies, in fact, the construction of a certain concurrency scenario, the cause of deadlock has been revealed. However, there is another prerequisite strategy that needs to be introduced, and that is the deadlock prevention strategy adopted within InnoDB.

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

Find a record that meets the criteria, but the record is invalid (identified as a deleted record), then add a next key lock to the record (locking the record itself, as well as Gap:lock_mode X before the record)

If no record satisfying the condition is found, a Gap lock is added to the first record that does not meet the condition to ensure that there is no record insertion that meets the condition (locks gap before rec).

Deadlock prevention strategy

Inside the InnoDB engine (or within all databases), there are many types of locks: transaction locks (row locks, table locks), Mutex (protect internal shared variable operations), and RWLock (also known as Latch, which protects internal page reads and modifications).

InnoDB each page is 16K, when reading a page, you need to add S lock to the page, and when you update a page, you need to add X lock to the page. In any case, manipulating a page will lock the page, and after the page lock is added, the index records stored on the page will not be modified concurrently.

So, in order to modify a record, how does InnoDB handle it internally:

Find the page where the corresponding record is located according to the given query condition

Add an X lock (RWLock) to the page, and then look for records within the page that meet the criteria

In the case of holding a page lock, add a transaction lock to the record that meets the condition (row lock: according to whether the record meets the query condition, whether the record has been deleted, corresponding to one of the three locking strategies mentioned above)

Deadlock prevention strategy: compared to a transaction lock, a page lock is a short-term lock, while a transaction lock (row lock, table lock) is a long-held lock. Therefore, in order to prevent deadlocks between page locks and transaction locks. InnoDB has a deadlock prevention strategy: holding transaction locks (row locks, table locks) and waiting for page locks to be acquired; but conversely, holding page locks, not waiting for transaction locks.

According to the deadlock prevention strategy, when holding a page lock and adding a row lock, if the row lock needs to wait. Release the page lock and wait for the row lock. At this point, the row lock acquisition does not have any lock protection, so after adding the row lock, the record may have been modified concurrently. Therefore, at this time, it is necessary to re-add the page lock, re-judge the state of the record, and lock the record again under the protection of the page lock. If the record is not concurrently modified at this time, the second locking can be completed quickly, because the lock of the same pattern is already held. However, if the record has been modified concurrently, it may lead to the deadlock problem mentioned earlier in this article.

The above InnoDB deadlock prevention processing logic, corresponding to the function, is row0sel.c::row_search_for_mysql (). Interested friends, you can track and debug the processing flow of this function, very complex, but focus on the essence of InnoDB.

Analysis of the causes of deadlock

After laying the groundwork, with the preparation knowledge of the three locking logic of Delete operation and the deadlock prevention strategy of InnoDB, we will come back to analyze the deadlock problem mentioned in this article, and we will get twice the result with half the effort.

First, suppose there is only one record in dltask: (1,'a record, 'baked,' clocked, 'data'). Three concurrent transactions, executing the following SQL simultaneously:

Delete from dltask where axia`and breadcrumb 'and cantilever c'

And the following concurrent execution logic is generated, resulting in a deadlock:

The concurrent process analyzed above fully shows the cause of the deadlock in the deadlock log. In fact, depending on the order between transaction 1 step 6 and transaction 0 step 3 No Gap 4, another situation may occur in the deadlock log, that is, transaction 1 waits for an X lock + No Gap lock (lock_mode X locks rec but not gap waiting) on the record. This second case is also the reason for the deadlock tested by MySQL 5.6.15 in the deadlock case given by Runjie students.

Several prerequisites for this type of deadlock:

The Delete operation is aimed at deleting the equivalent query on the unique index. (deletion under the scope will also result in deadlock, but the deadlock scenario is different from the scenario analyzed in this article.)

At least 3 (or more) concurrent delete operations

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

The isolation level of the transaction is set to Repeatable Read, and the innodb_locks_unsafe_for_binlog parameter is not set (this parameter defaults to FALSE); (Read Committed isolation level, because there is no Gap lock, no next key, so there is no deadlock)

Using the InnoDB storage engine; (nonsense! The MyISAM engine has no row lock at all)

This is the answer to the question about why MySQL causes deadlock. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.