Mysql lock type and lock analysis

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

** table level lock: ** low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low.

** row-level lock: ** overhead, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.

** page locks: ** overhead and lock time are between table locks and row locks; Deadlocks occur; Lock granularity between table locks and row locks, concurrency

Algorithm:

  • Next KeyLocks, which also locks the record (data) and locks the Gap in front of the record

  • Gap lock, do not lock the record, only record the Gap in front

  • Recordlock Data, not Gap

  • Next-keylocks =Gap lock + Recordlock

Deadlock causes and examples

The reasons causing

A DeadLock is a situation in which two or more processes wait for each other during execution because they are competing for resources, and neither can move forward without external intervention. The system is said to be in a deadlock state or a deadlock occurs in the system. These processes that are always waiting for each other are called deadlocked processes. Table level locks do not cause deadlocks. So deadlock resolution is mainly for InnoDB, the most common.

The key to deadlocks is that two (or more) sessions are locked in an inconsistent order.

Therefore, the key to solve the deadlock problem is to make different sessions lock in order

Produce the sample

Case a

Demand: Split the investment money into several pieces and randomly distribute them to borrowers.

The initial business program idea was this:

After the investor invests, he will randomly divide the amount into several parts, and then randomly select several from the borrower table, and then update the balance in the borrower table by selecting for update one by one.

For example, if two users invest at the same time, the amount of user A is randomly divided into two parts and given to the borrower 1,2

The amount of user B is divided into 2 parts randomly and given to borrower 2, 1

Deadlocks occur quickly, of course, because of the order in which the locks are placed.

The improvement to this problem is very simple, directly all the allocated borrowers directly locked up once.

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

Mysql automatically sorts the values in the in list from smallest to largest, and locks are added from smallest to largest

Session1: mysql> select * from t3 where id in (8,9) for update; +----+--------+------+---------------------+ | id | course | name | ctime | +----+--------+------+---------------------+  | 8 | WA | f | 2016-03-02 11:36:30 | | 9 | JX | f | 2016-03-01 11:36:30 | +----+--------+------+---------------------+ Rows in set (0.04 SEC) Session2: select * from t3 where id in (10,8,5) for update; Lock waiting...Copy the code

In fact, the record with id=10 is not locked, but the record with id=5 is locked, and the lock is waiting for the record with id=8

Session3: mysql> select * from t3 where id=5 for update; Session4: mysql> select * from t3 where id=10 for update; +----+--------+------+---------------------+ | id | course | name | ctime | +----+--------+------+---------------------+ | | 10 11:45:05 JB | g | 2016-03-10 | + - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + row in the set (0.00 SEC)Copy the code

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

Case 2

In development, there are often judgment requirements such as: query by field value (indexed), insert if not present; Otherwise update.

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(22,'ac','a',now()); Lock waiting... Session2: insert into t3 values(23,'bc','b',now()); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionCopy the code

Mysql only has row locks when locking existing rows (primary key). Mysql will lock a range (with a gap lock) when a row does not exist (even if it is a primary key).

The range of locking is:

(infinitesimal or less than the maximum lock ID in the table, infinite or greater than the minimum lock ID in the table)

If there is an existing id (11, 12) in the table

So it locks (12, infinity)

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

So lock it. (11,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 an INSERT statement for a primary key will only have a row lock whether the inserted row exists or not

Case 3

mysql> select * from t3 where id=9 for update; +----+--------+------+---------------------+ | id | course | name | ctime | +----+--------+------+---------------------+ | | 9 JX | | 2016-03-01 f 11:36:30 | + - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + row in the set (0.00 SEC) Session2: mysql> select * from t3 where id<20 for update;Copy the code

In the lock wait

Session1:
mysql> insert into t3 values(7,'ae','a',now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Copy the code

So this is pretty much the same thing as in case one, except session1 doesn’t play by the rules,

Session2 is waiting for the lock with Session1 id=9, Session2 is holding the lock from 1 to 8 (note that the range from 9 to 19 is not locked by Session2), and finally, Session1 has to wait for Session2 to insert a new row, so the deadlock occurs.

This usually doesn’t happen in business requirements, because if you want to insert a row with id=7 and lock id=9, that’s a bit of a jump, but there’s definitely a way around that, too, which is to rearrange the business requirements to avoid this.

Four cases

Typically, two sessions each hold a lock through an SQL and then access each other’s locked data to create a deadlock.

Case 5

Two single SQL statements involve the same locking data, but the locking sequence 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), Uniq_a_b_c (a, B, C) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT= 'deadlock test';Copy the code

A, B, and C are combined into a unique index. The primary key index is the ID column.

Transaction isolation level:

RR (Repeatable Read)

Only one SQL per transaction:

delete from dltask where a=? and b=? and c=? ;

SQL execution plan

Deadlock log

As we all know, deleting a record in InnoDB is not really a physical deletion, but marks the record as being deleted. (Note: These records, which are identified as deleted status, are subsequently reclaimed and physically deleted by the Purge operation in the background. However, records of deleted status are stored in the index for some time. In RR isolation level, how can I lock a unique index that meets query conditions but deletes records? InnoDB’s processing strategy here is different from the first two strategies, or a combination of the first two strategies: For deleted records that meet the criteria, InnoDB places a next key lock X on the record (X is locked on the record itself, and the GAP before the record is locked, preventing new records that meet the criteria from being inserted). Select * from Unique; select * from Unique;

Here we see the next key lock. Does that look familiar? The locks on transaction 1 and transaction 2 in the wait state are next key locks. Understand these three locking strategies, in fact, to construct a certain concurrency scenario, the cause of deadlock has been clearly revealed. However, there is another prerequisite strategy that needs to be introduced, and that is the deadlock prevention strategy that InnoDB uses internally.

  • Lock No Gap (lock_mode X locks rec but not Gap);

  • If a record is found that meets the condition, but the record is invalid (marked as deleted record), then add a next key lock to the record (at the same time, lock the record itself, and the Gap: lock_mode X before the record);

  • A Gap lock is applied to the first record that does not meet the condition. Locks Gap before REC.

Deadlock prevention strategies

Inside the InnoDB engine (or all databases), there are many types of locks: transactional locks (row locks, table locks), Mutex(to protect internal shared variable operations), rwLocks (also known as latches, to protect internal page reads and changes).

Each InnoDB page is 16K, when reading a page, it needs to lock the page S, when updating a page, it needs to lock the page X. In any case, when a page is operated on, a lock is placed on the page so that the index records stored in the page cannot be concurrently modified.

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

  • According to the given query conditions, find the corresponding record page;

  • Add an RWLock to the page, and then search the page for records that meet the criteria;

  • In the case of a page lock, a transaction lock is added to the records that meet the criteria (row lock: according to whether the record meets the query criteria, whether the record has been deleted, respectively corresponding to one of the three locking strategies mentioned above);

Deadlock prevention strategy: As opposed to transaction locks, page locks are short-term locks, while transaction locks (row locks, table locks) are long-term locks. Therefore, to prevent deadlocks between page locks and transaction locks. InnoDB implements deadlock prevention policies: hold transaction locks (row locks, table locks) and wait for page locks; However, holding a page lock cannot wait to hold a transaction lock.

According to the deadlock prevention policy, when holding a page lock, add a row lock, if the row lock needs to wait. Then release the page lock and wait for the row lock. At this point, the row lock acquisition is not protected by any lock, so the record may have been concurrently modified after the row lock was added. Therefore, add the page lock again, judge the status of the record again, and lock the record again under the protection of the page lock. If the record is not concurrently modified at this point, the second lock can be completed quickly because the same schema lock is already held. However, if the record has been concurrently modified, it can cause the deadlock problem mentioned earlier in this article.

The InnoDB deadlock prevention logic above corresponds to row0sel.c::row_search_for_mysql(). If you are interested, you can track the process of debugging this function. It is very complicated, but it concentrates the essence of InnoDB.

Analyze the cause of deadlocks

After so much preparation, with the three lock logic of Delete operation, InnoDB deadlock prevention strategy and other preparation knowledge, then go back to analyze the deadlock problem mentioned in this article, it will come to hand, half the work and twice the work.

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

Delete from dltask where a= 'a' and b= 'b' and c= 'c';

A deadlock occurs when the following concurrent execution logic is generated:

The concurrency flow analyzed above fully illustrates the cause of deadlocks in deadlock logs. Depending on the order between step 6 of transaction 1 and step 3/4 of transaction 0, another situation can occur in deadlock logs where transaction 1 waits in X + No Gap lock mode (lock_mode X locks rec but not Gap waiting). This second situation is also the cause of the deadlock tested by MySQL 5.6.15 in the deadlock case given by “Runjie”.

This type of deadlock arises from several premises:

  • The Delete operation deletes an equivalent query on a unique index. (Deadlocks can also occur when a delete is in scope, but the deadlock scenario is different from the one 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;

  • Transaction isolation level is set to Repeatable Read and innodb_LOCKS_unSAFE_for_binlog parameter is not set (this parameter is FALSE by default). (Read Committed isolation level, no Gap locks, no next keys, and therefore no deadlocks)

  • The InnoDB storage engine is used; (crap! MyISAM has no row locks at all.