Under what circumstances will mysql deadlock?

Deadlock in mysql: refers to a phenomenon of two or more processes waiting for each other due to competition for resources during execution. Without external force, they will not be able to advance. Go on. At this time, the system is said to be in a deadlock state or the system has a deadlock. These processes that are always waiting for each other are called deadlock processes. Table-level locks will not cause deadlocks. Therefore, solving deadlocks is mainly focused on the most commonly used InnoDB.

Recommended courses: MySQL Tutorial.

The key to deadlock is that the order in which two (or more) Sessions are locked is inconsistent.

Then the corresponding key to solving the deadlock problem is: let different sessions be locked in order

Case

Requirement: Invest the money Split into several tranches and distributed randomly to borrowers.

The initial business process idea is as follows:

After the investor invests, the amount is randomly divided into several parts, and then a few are randomly selected from the borrower list. Then use select for update to update the balance in the borrower table, etc.

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

The amount of user B is randomly divided into 2 parts. Assigned to borrowers 2 and 1

. Since the order of locking is different, deadlock will of course occur quickly.

The improvement to this problem is very simple, just lock all the assigned borrowers at once.

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

The list value in in mysql will automatically sort from small to large, add The locks are also a series of locks from small to large

 例如（以下会话id为主键）：
 
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;
锁等待中……
 
其实这个时候id=10这条记录没有被锁住的，但id=5的记录已经被锁住了，锁的等待在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 | JB     | g    | 2016-03-10 11:45:05 |
+----+--------+------+---------------------+
row in set (0.00 sec)
 
 
在其它session中id=5是加不了锁的，但是id=10是可以加上锁的。

The above is the detailed content of Under what circumstances will mysql deadlock?. For more information, please follow other related articles on the PHP Chinese website!