update死锁解决方案

问题：

     多个update语句对数据库做更新操作时，导致数据库表死锁。

SQL语句：

语句1：
UPDATE test_table SET col_4_name = '',col_5_name='' WHERE col_1_name = 'xxx' AND col_2_name = 'xxx' AND col_3_name = 'xxx';
语句2：
UPDATE test_table SET col_4_name = '',col_6_name='' WHERE col_1_name = 'xxx' AND col_2_name = 'xxx' AND col_3_name = 'xxx';

死锁信息如下：

srv_master_thread loops: 2097506 srv_active, 0 srv_shutdown, 7386562 srv_idle
srv_master_thread log flush and writes: 9483563
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 6972941
OS WAIT ARRAY INFO: signal count 10636711
RW-shared spins 0, rounds 15828779, OS waits 5319938
RW-excl spins 0, rounds 55486192, OS waits 447156
RW-sx spins 1788772, rounds 34121454, OS waits 423421
Spin rounds per wait: 15828779.00 RW-shared, 55486192.00 RW-excl, 19.08 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-12-24 09:46:49 0x7f30a87f4700
*** (1) TRANSACTION:
TRANSACTION 90336432, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 14616700, OS thread handle 139847291823872, query id 1166213705 10.99.95.237 test_db updating
UPDATE test_table SET col_4_name = '',col_5_name='' WHERE col_1_name = 'xxx' AND col_2_name = 'xxx' AND col_3_name = 'xxx'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 730 page no 820 n bits 200 index PRIMARY of table `test_db`.`test_table` trx id 90336432 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 1; hex 30; asc 0;;
 2: len 3; hex 8fbe4e; asc   N;;
 3: len 4; hex 800003e9; asc     ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 30; hex 2f686f6d652f66756e642f6e66732f76612f322f5b4143434f554e545f42; asc /home/fund/nfs/va/2/[ACCOUNT_B; (total 60 bytes);
 6: len 6; hex 000002c9141c; asc       ;;
 7: len 7; hex cd0000018f0110; asc        ;;
 8: len 4; hex 80000005; asc     ;;
 9: len 30; hex e69687e4bbb6e78ab6e680815b534a5358582e6462665de69caae590afe7; asc             [SJSXX.dbf]       ; (total 33 bytes);
 10: len 0; hex ; asc ;;
 11: len 6; hex 5dd6432c07a8; asc ] C,  ;;
 12: len 6; hex 5dd6432c07a8; asc ] C,  ;;

*** (2) TRANSACTION:
TRANSACTION 90336428, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4740
mysql tables in use 1, locked 1
431 lock struct(s), heap size 57552, 40698 row lock(s), undo log entries 1
MySQL thread id 14616699, OS thread handle 139846962071296, query id 1166213608 10.99.95.237 test_db updating
UPDATE test_table SET col_4_name = '',col_5_name='' WHERE col_1_name = 'xxx' AND col_2_name = 'xxx' AND col_3_name = 'xxx'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 730 page no 820 n bits 200 index PRIMARY of table `test_db`.`test_table` trx id 90336428 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;

查找问题步骤：

      1.查询死锁信息:

         (1).执行sql：

show engine innodb status;

         (2).查询结果：如下图，status中的信息就是死锁信息，将其内容复制出来分析；

    2.分析上一步中查询到的死锁信息，找到产生死锁的sql语句:

     2.查看执行计划:

        (2.1).SQl语句：

EXPLAIN UPDATE test_table SET col_4_name = '',col_5_name='' WHERE col_1_name = 'xxx' AND col_2_name = 'xxx' AND col_3_name = 'xxx';

       (2.2).分析执行计划：type为range,rows为若干条数据，需要优化。

解决方案：本质是破坏死锁产生的必要条件

      此案例解决方案一：加索引，适合于主键为复合主键

         1.SQL语句

ALTER TABLE test_table ADD INDEX idx_test_table(col_1_name,col_2_name,col_3_name);

         2.注意点：索引的中的字段顺序要与update语句后面的条件一致，可不包含所有条件字段，可视情况而定；

      此案例解决方案二：先select查询所要更新的语句，然后根据主键更新，适合于主键为唯一主键

         1.SQL语句

// 查询
SELECT xxx FROM test_table WHERE col_1_name = 'xxx' AND col_2_name = 'xxx' AND col_3_name = 'xxx';
// 更新对应的记录
UPDATE test_table SET col_4_name = '',col_5_name='' WHERE XXX ='XXX';

            
原因：

      1.此示例原因：主键为复合主键时，主键的顺序和执行语句后面的条件顺序不一致，导致踩了部分索引，使大量数据被锁定，其他语句执行时，导致死锁。

      2.官方解释：1. 互斥条件；2. 请求和保持条件；3. 不剥夺条件；4. 循环等待条件。

 

相关知识点：

      1.数据库索引相关知识；

      2.数据库锁机制，产生的原因，解决方法等；

代码示例：

     如上。