Directory:

  1. What locks does MySQL have
  2. When can deadlock be caused
  3. Common deadlock cases


1. What locks does MySQL have

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

Table 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: expensive, 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-level locking: the overhead and locking time are between table and row locks. Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average

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

2. When can deadlock be caused

Deadlock: refers to the phenomenon that two or more processes are waiting for each other in the process of execution due to the competition for resources. Without external force, they will not be able to proceed.

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

3. Common deadlock cases

Case 1:

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.

A session through the for loop will have several statements like this:

Select * from XXX where id=’ id’ for update

Basically, a program will be deadlocked shortly after it is opened. This is probably the most classic deadlock situation.

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 t3where id in(8, 9)forupdate; +----+--------+------+---------------------+ | id | course | name | ctime | +----+--------+------+---------------------+  | 8 | WA | f | 2016-03-02 11:36:30 | | 9 | JX | f | 2016-03-01 11:36:30 | +----+--------+------+---------------------+ rowsin set (0.04 sec)
Session2:
select * from t3 where id in (10,8,5) forupdate; Lock waiting... In fact, the record with id=10 is not locked, but the record with ID =5 is already locked, and the lock is waiting for the record with ID =8. Mysql > select * from t3where id=5 forupdate; Mysql > select * from t3where id=10 forupdate; +----+--------+------+---------------------+ | id | course | name | ctime | +----+--------+------+---------------------+  | 10 | JB | g | 2016-03-10 11:45:05 | +----+--------+------+---------------------+ rowin set(0.00 SEC) If id=5, lock is not allowed, but if id=10, lock is allowed.Copy the code

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 (id=22)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 transaction
Copy 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).

Lock range: infinitesimal or less than the maximum lock ID in the table, infinite or greater than the minimum lock ID in the table

For example, if there are existing ids (11, 12) in the table, then lock (12, infinity). Lock (11, 30); lock (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:

Go straight to the scene:


mysql> select * from t3 where id=9 forupdate; +----+--------+------+---------------------+ | id | course | name | ctime | +----+--------+------+---------------------+  | 9 | JX | f | 2016-03-01 11:36:30 | +----+--------+------+---------------------+ rowin set (0.00 sec)
Session2:
mysql> select * from t3 where id<20 forupdate; 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

This is actually the same situation as in case 1, but session1 is not in accordance with the rules.

Session2 is waiting for a lock with session1 id=9, session2 is holding a lock with session1 id=9, session2 is holding a lock with session1 id=9.

Finally, session1 has to wait for session2 to insert a new row, so a 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.