Detailed introduction to the code to solve the MySQL deadlock problem

Solution to a MySQL deadlock problem

1. Environment

• CentOS, MySQL 5.6.21-70, JPA

• Problem scenario: The system has a scheduled batch update data status operation, updating thousands of records each time, and the total number of records in the table is about 5 million.

2. Error log

2017-2-25 17:38:41 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Lock wait timeout exceeded; try restarting transaction
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
警告: SQL Error: 1213, SQLState: 40001
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Deadlock found when trying to get lock; try restarting transaction

3. Troubleshooting

Check InnoDB status for locks
mysql> SHOW ENGINE InnoDB STATUS;

Check MySQL open tables
mysql> SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions
mysql> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

Check lock dependency - what blocks what
mysql> SELECT * FROM `information_schema`.`innodb_locks`;

After troubleshooting, it was found that problems occurred when executing statements similar to this:

update t_task_tel set state='iok', update_date='2017-02-27 11:03:02' where tel_id=66042 and task_id=350199;

4. Analysis

After searching for relevant information, I found that MySQL InnoDB does not necessarily all have row-level locks.

Relevant reference material fragments are as follows:

MySQL InnoDB锁机制之Gap Lock、Next-Key Lock、Record Lock解析
http://www.php.cn/

4、锁选择
1）、如果更新条件没有走索引，例如执行”update from t1 set v2=0 where v2=5;” ，此时会进行全表扫描，扫表的时候，要阻止其他任何的更新操作，所以上升为表锁。
2）、如果更新条件为索引字段，但是并非唯一索引（包括主键索引），例如执行“update from t1 set v2=0 where v1=9;” 
那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因：
a）、首先要保证在符合条件的记录上加上排他锁，会锁定当前非唯一索引和对应的主键索引的值；
b）、还要保证锁定的区间不能插入新的数据。
3）、如果更新条件为唯一索引，则使用Record Lock（记录锁）。
 
InnoDB根据唯一索引，找到相应记录，将主键索引值和唯一索引值加上记录锁。但不使用Gap Lock（间隙锁）。

MySQL InnoDB 锁表与锁行
http://www.php.cn/

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

According to the analysis conclusion, it is guessed that the reason why tel_id and task_id did not establish UNIQUE (unique index) in the Where condition when updating the _task_tel table;

5. Solution

Based on this analysis, try to solve the problem by establishing a UNIQUE (unique index) through the two fields of tel_id and task_id. (You can also query it first and then update it based on the primary key ID, so that the large amount of data in the table will not affect online business).

After solving this problem, the problem has not recurred.

If your problem is similar to what I encountered, you can try to solve it accordingly.

The above is the detailed introduction of the code to solve the MySQL deadlock problem. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!