mysql中常见锁异常初探

mysql5.6中的Table_locks_immediate、Table_locks_waited这两个参数

MySQL [(none)]> show status like "Table%";
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| Table_locks_immediate      | 1445647048 |
| Table_locks_waited         | 61         |
| Table_open_cache_hits      | 0          |
| Table_open_cache_misses    | 0          |
| Table_open_cache_overflows | 0          |
+----------------------------+------------+
5 rows in set (0.00 sec)

对于InnoDB引擎来说，针对mysql中的行锁，有如下几个参数，

root@db 10:48:  [mysql]> show global status like "Innodb_row%";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |*当前正在等待的行锁数*
| Innodb_row_lock_time          | 0     |*获取InnoDB表的行锁所花费的总时间，以毫秒为单位*
| Innodb_row_lock_time_avg      | 0     |*获取InnoDB表的行锁的平均时间，以毫秒为单位*
| Innodb_row_lock_time_max      | 0     |*获取InnoDB表的行锁的最长时间，以毫秒为单位*
| Innodb_row_lock_waits         | 0     |*InnoDB表上的操作必须等待行锁的次数*
| Innodb_rows_deleted           | 0     |*从InnoDB表中删除的行数*
| Innodb_rows_inserted          | 0     |*插入InnoDB表的行数*
| Innodb_rows_read              | 72035 |*从InnoDB表读取的行数*
| Innodb_rows_updated           | 3     |*InnoDB表中更新的行数*
+-------------------------------+-------+
9 rows in set (0.01 sec)

下面模拟几类常见的事务并发操作过程中的锁争用
1.等待行锁
2.死锁场景
3.①DDL语句等待Metadata 写锁 | ②insert, update, delete语句等待Metadata 读锁 | ③select 语句等待Metadata 读锁
MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。另外注意，如果给定锁有多个等待着，则首先满足最高优先级的锁请求（当然是MDL写锁优先级高咯）

1.等待行锁
所谓行锁，顾名思义，就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新，当然不仅局限于更新，其他例如查询数据（fetching rows）的时候，另外一个事务要inserting,也是需要等待行锁的。

二阶段锁协议
上图中事务B被阻塞，直到事务A commit之后，由此可以得到在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
实验演示：
A会话：

root@db 14:44:  [ipcis_mdm]> update web_mdm_user t set t.c_mobile='987654322' where t.c_user_id='47417I91E37CM9V0BD4652X2G8AE8T';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@db 14:46:  [ipcis_mdm]> show full processlist;
+----+------+-------------------+-----------+---------+------+----------+-----------------------------------------------------------------------------------------------------+
| Id | User | Host              | db        | Command | Time | State    | Info                                                                                                |
+----+------+-------------------+-----------+---------+------+----------+-----------------------------------------------------------------------------------------------------+
| 31 | root | localhost         | ipcis_mdm | Query   |    0 | init     | show full processlist                                                                               |
| 39 | root | 10.0.20.142:13127 | ipcis_mdm | Sleep   |  143 |          | NULL                                                                                                |
| 40 | root | 10.0.20.142:13136 | ipcis_mdm | Sleep   |  143 |          | NULL                                                                                                |
| 41 | root | localhost         | ipcis_mdm | Query   |   19 | updating | update web_mdm_user t set t.c_mobile='987654323' where t.c_user_id='47417I91E37CM9V0BD4652X2G8AE8T' |
+----+------+-------------------+-----------+---------+------+----------+-----------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

B会话：innodb行锁等待超时时间，可以在参数变量中指定

#innodb_lock_wait_timeout = 35
#innodb_rollback_on_timeout = on

root@db 14:45:  [ipcis_mdm]> update web_mdm_user t set t.c_mobile='987654323' where t.c_user_id='47417I91E37CM9V0BD4652X2G8AE8T';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2.死锁场景:死锁分析起来比较复杂，对底层的知识储备要求很高，这方面我也是小白，走一步看一步吧。。。

root@db 09:51:  [(none)]> set global innodb_status_output=ON;*开启标准监控*
Query OK, 0 rows affected (0.00 sec)

root@db 10:44:  [(none)]> set global innodb_status_output_locks=ON;*开启锁监控*
Query OK, 0 rows affected (0.00 sec)

root@db 10:44:  [(none)]> set global innodb_print_all_deadlocks=ON;*开启死锁日志到error文件*
Query OK, 0 rows affected (0.00 sec)

通过show engine innodb status查看最近的一次死锁日志：

------------------------
2020-07-31 18:00:03 7f5b5f866700
*** (1) TRANSACTION:
TRANSACTION 1260236231, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6507 lock struct(s), heap size 636456, 519272 row lock(s), undo log entries 18
MySQL thread id 28001268, OS thread handle 0x7f5aff5d3700, query id 9705761129 10.98.21.136 issadmin update
insert into web_ply_base (C_ACT_SUBTYP, C_AGT_AGR_NO, C_AMT_CUR, C_APP_CATEGORY, C_APP_NO, C_APP_PRSN_CDE, C_APP_PRSN_NME, C_APP_TYP, C_AREA_HOSPITAL, C_AUTO_PADDING, C_AUTO_PAY_FLAG, C_AUTO_RENEWAL, C_BRK_SLS_CDE, C_BRKR_CDE, C_BRKR_SLS_CDE, C_BRKR_SLS_CDE_MOBILE, C_BRKR_SLS_NME, C_BSNS_SUBTYP, C_BSNS_TYP, C_CERTF_NO, C_CHA_SUBTYPE, C_CHA_TYPE, C_CI_INP_TYP, C_CI_MRK, C_CI_PRI_TYP, C_CI_TYP, C_CRT_CDE, C_DATA_SRC, C_DDUCT_DESC, C_DISPT_STTL_CDE, C_DISPT_STTL_ORG, C_DPT_CDE, C_EDR_CTNT, C_EDR_MRK, C_EDR_RATIO_TYP, C_EDR_RSN_BUNDLE_CDE, C_EDR_TYPE, C_FIN_TYP, C_GRP_MRK, C_HOSPITAL, C_IMMEFF_MRK, C_INST_MRK, C_INSU_YEAR, C_INSU_YEAR_FLAG, C_INWD_MRK, C_JURI_CDE, C_LARGE_BUSINESS, C_Latest_Mrk, C_LEGAL_BNFC, C_LISTORCOL_MRK, C_LONG_TERM_MRK, C_MANUAL_MRK, C_MULT_INSRNT_MRK, C_OC_PLY_NO, C_OPERA_ACCIDENT_INSU, C_OPR_CDE, C_OPR_TYP, C_ORDER_CDE, C_ORIG_INSURER, C_ORIG_PLY_NO, C_PAY_INTV, C_PAY_TIME_FALG, C_
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5439 page no 1640 n bits 200 index `IDX_BASE_PROD_NO` of table `ipcis_nvhlpcis`.`web_ply_base` trx id 1260236231 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 1260236259, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
1844 lock struct(s), heap size 177704, 43728 row lock(s), undo log entries 2
MySQL thread id 28008622, OS thread handle 0x7f5b5f866700, query id 9705761087 10.98.21.136 issadmin updating
update web_ply_base set C_PLY_STS='4', N_PRM=0.0, C_EDR_RSN_BUNDLE_CDE='02', C_EDR_CTNT='对险种不满意', T_EDR_APP_TM='2020-07-31 18:00:03.167', C_EDR_TYPE='退保终止', T_INSRNC_END_TM='2020-07-30 00:00:00' where C_PKG_NO='62000215161' and C_PROD_NO='300024'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5439 page no 1640 n bits 200 index `IDX_BASE_PROD_NO` of table `ipcis_nvhlpcis`.`web_ply_base` trx id 1260236259 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;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 6; hex 343030303037; asc 400007;;
 1: len 13; hex 39323030373330303138343432; asc 9200730018442;;

对以上的死锁日志分析如下：

Transaction1
Key Value
Host    10.98.21.136
Thread  28001268
SQL 语句  insert into web_ply_base (C_ACT_SUBTYP, C_AGT_AGR_NO, C_AMT_CUR, C_APP_CATEGORY, C_APP_NO, C_APP_PRSN_CDE, C_APP_PRSN_NME, C_APP_TYP, C_AREA_HOSPITAL, C_AUTO_PADDING, C_AUTO_PAY_FLAG, C_AUTO_RENEWAL, C_BRK_SLS_CDE, C_BRKR_CDE, C_BRKR_SLS_CDE, C_BRKR_SLS_CDE_MOBILE, C_BRKR_SLS_NME, C_BSNS_SUBTYP, C_BSNS_TYP, C_CERTF_NO, C_CHA_SUBTYPE, C_CHA_TYPE, C_CI_INP_TYP, C_CI_MRK, C_CI_PRI_TYP, C_CI_TYP, C_CRT_CDE, C_DATA_SRC, C_DDUCT_DESC, C_DISPT_STTL_CDE, C_DISPT_STTL_ORG, C_DPT_CDE, C_EDR_CTNT, C_EDR_MRK, C_EDR_RATIO_TYP, C_EDR_RSN_BUNDLE_CDE, C_EDR_TYPE, C_FIN_TYP, C_GRP_MRK, C_HOSPITAL, C_IMMEFF_MRK, C_INST_MRK, C_INSU_YEAR, C_INSU_YEAR_FLAG, C_INWD_MRK, C_JURI_CDE, C_LARGE_BUSINESS, C_Latest_Mrk, C_LEGAL_BNFC, C_LISTORCOL_MRK, C_LONG_TERM_MRK, C_MANUAL_MRK, C_MULT_INSRNT_MRK, C_OC_PLY_NO, C_OPERA_ACCIDENT_INSU, C_OPR_CDE, C_OPR_TYP, C_ORDER_CDE, C_ORIG_INSURER, C_ORIG_PLY_NO, C_PAY_INTV, C_PAY_TIME_FALG, C_
Status  Normal
LockRequest RECORD LOCKS space id 5439 page no 1640 n bits 200 index `IDX_BASE_PROD_NO` of table `ipcis_nvhlpcis`.`web_ply_base` trx id 1260236231 lock_mode X insert intention waiting
LockHold    
Transaction2
Key Value
Host    10.98.21.136
Thread  28008622
SQL 语句  update web_ply_base set C_PLY_STS='4', N_PRM=0.0, C_EDR_RSN_BUNDLE_CDE='02', C_EDR_CTNT='对险种不满意', T_EDR_APP_TM='2020-07-31 18:00:03.167', C_EDR_TYPE='退保终止', T_INSRNC_END_TM='2020-07-30 00:00:00' where C_PKG_NO='62000215161' and C_PROD_NO='300024'
Status  Rollback
LockRequest RECORD LOCKS space id 5439 page no 3051 n bits 96 index `PK_PLY_BASE` of table `ipcis_nvhlpcis`.`web_ply_base` trx id 1260236259 lock_mode X waiting
LockHold    RECORD LOCKS space id 5439 page no 1640 n bits 200 index `IDX_BASE_PROD_NO` of table `ipcis_nvhlpcis`.`web_ply_base` trx id 1260236259 lock_mode X

先关注status,事务1在执行insert语句的时候会先申请意向排他锁，此时会被阻塞，因为事务2持有行锁（记录锁），形成僵持，要破坏死锁，只需要其中一个线程回滚放弃持有的锁就可以了，这里能看到事务2的状态为Rollback，事务1的进程得以进行下去。
关于死锁的例子数不胜数，这里只是简单介绍一下。继续学习，任重而道远。

3.①DDL语句等待Metadata 写锁 | ②insert, update, delete语句等待Metadata 读锁 | ③select 语句等待Metadata 读锁
这里模拟一个慢sql查询过程，DDL语句被阻塞的状态：

| 25366126 | lis  | 10.3.23.189:9337   | lis  | Query   |    2 | executing                       | SELECT (SELECT CODE FROM ldcode WHERE CODE = ( SELECT state FROM lccont WHERE contno = a.contno AND codetype = 'contstate' AND state IN ( '30', '81' ) ) 
    ) AS TerminationReason FROM lccont a WHERE 1 = 1  AND a.conttype <> '1' AND a.modifydate BETWEEN '2020-07-10' AND '2020-07-10' LIMIT 100000 |
| 25366128 | lis  | 10.3.23.189:9343   | lis  | Sleep   | 1041 |                                 | NULL                                                                                                                                                                                                                                                                                                   |
| 25366267 | lis  | 10.88.201.11:60532 | lis  | Query   |    1 | Waiting for table metadata lock | alter table lccont add testfield0805 VARCHAR(30)  

实际生产过程中等待MDL锁的情况一般很少见。