在mysql的使用过程中常遇到执行语句被阻塞的情况，本文简单总结了几种比较常见的数据库阻塞问题，详细内容如下。
0.数据准备
构造表t,插入100000条数据（测试环境mysql8.0）
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000) do
insert into t values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
1.全局锁导致的阻塞
全局锁是对整个数据库实例进行加锁，mysql中可以使用flush tables with read lock;（FTWRL）来对整个数据库加读锁。加读锁后数据库处于只读状态，数据的更新（增删改）、数据定义语句（建表，修改表结构等）、更新类事务将会被阻塞。
一个典型的应用场景。如要对数据库进行逻辑备份时，为了保证备份数据在同一个逻辑时间点上，就可以使用全局锁，先让数据库处于只读的状态，防止备份期间数据库中的数据被更改。
正常情况下，flush 动作很快就会执行完，如果flush时有数据表还在执行增删改查操作，flush命令或等待任务执行结束后才会关闭数据表。等待的过程中如果发起查询，那么这时候查询也会被阻塞。示例如下：
| session A | session B | session C |
|---|---|---|
| select sleep(1) from t | ||
| flush tables with read lock | ||
| select * from t where id = 1 |
session A中每行记录查询时都调用一次sleep(1),整个语句执行行结束就需要10万秒
mysql> select sleep(1) from t;
执行中...
session B试图去关闭表，但是由于session A中查询任务还未结束，所以就被阻塞了
mysql> flush tables with read lock;
阻塞...
session C 这时session C 再来查询就会被阻塞
mysql> select * from t where id = 1;
阻塞...
查看mysql正在执行的线程，可以看到 flush tables with read lock和select * from t where id = 1 都在等flush
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+-------------------------+------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+-------------------------+------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 9405 | Waiting on empty queue | NULL |
| 29 | root | localhost | test | Query | 145 | User sleep | select sleep(1) from t |
| 30 | root | localhost | test | Query | 138 | Waiting for table flush | flush tables with read lock |
| 36 | root | localhost | test | Query | 125 | Waiting for table flush | select * from t where id = 1 |
| 38 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+-------------------------+------------------------------+
那么该如何安全的对数据库进行备份呢？mysql在可重复读级别下，使用其自带的mysqldump工具进行数据备份时可以加上-single-transaction参数，这样mysql在进行数据备份前会先启动一个事务，保证备份期间拿到的数据是不变的，同时数据库还可以进行更新操作。不过这个方法仅适用于所有表都支持事务的引擎，这也是为什么mysql一般推荐使用InnoDB 数据引擎的原因之一。
2.表锁导致的阻塞
mysql中有两种表级锁，一种是表锁，一种是元数据锁。表锁的语法是 lock tables table_name read/write. 解锁需要手动调用unlock tables. 元数据锁（MDL）是mysql 5.5版本之后引入的，加锁与解锁都是数据库自动维护的，当对数据表作增/删/改/查操作时加MDL读锁，当修改表结构时加MDL 写锁。一般的语句执行完成后MDL锁就会自动释放，如果在事务中，事务提交后MDL锁才会释放。以上两种读写锁，读锁和读锁之间不互斥，读锁和写锁之间，写锁与写锁之间是互斥的。
具体示例如下：
示例1：
| session A | session B |
|---|---|
| lock tables t read | |
| update t set c=c+1 where id =1 |
sesssion A中对表t加了读锁
mysql> lock table t read;
Query OK, 0 rows affected (0.00 sec)
session B在读锁释放之前去更新表中的字段就会发生阻塞
mysql> update t set c=c+1 where id = 1;
阻塞
示例2：
| session A | session B | session C | session D |
|---|---|---|---|
| begin; select * from t limit 1 | |||
| select * from t limit 1 | |||
| alter table t add f int | |||
| select * from t limit 1 |
session A中开启事务，查询一条记录，这时数据库会给表t加上MDL读锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t limit 1;
+----+------+
| id | c |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)
session B中执行查询（增删改也是）操作，同样加MDL 读锁，但读锁之间不互斥，所以可正常执行
mysql> select * from t limit 1;
+----+------+
| id | c |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)
session C给表t中增加字段，这时需要对表t加MDL写锁，写锁与读锁互斥（session A中事务未提交，MDL表锁还未释放），所以session C被阻塞了。
mysql> alter table t add f int;
阻塞...
session D这时再去执行查询操作也会被阻塞
mysql> select * from t limit 1;
阻塞...
查看各mysql线程的执行情况如下，可以看到session C/D 都在Waiting for table metadata lock。实际业务中线上数据库修数据表结构（比如增加字段），如果有长事务还没有执行完毕，就容易造成数据库阻塞，所以要格外小心。
mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+---------------------------------+-------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+-------+---------------------------------+-------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 12149 | Waiting on empty queue | NULL |
| 41 | root | localhost | test | Sleep | 208 | | NULL |
| 42 | root | localhost | test | Sleep | 58 | | NULL |
| 43 | root | localhost | test | Query | 33 | Waiting for table metadata lock | alter table t add f int |
| 44 | root | localhost | test | Query | 18 | Waiting for table metadata lock | select * from t limit 1 |
| 47 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+-------+---------------------------------+-------------------------+
6 rows in set (0.00 sec)
3.行锁导致的阻塞
mysql中的Innodb数据引擎还支持行锁。行锁的加锁方式是自动的，对于update,delele,insert语句，inndb会自动给涉及到的数据加行锁，对于普通的select,innodb不会加任何锁。当然也可通过显示的方式来加锁.读锁语法为select … lock in share mode(读锁也叫共享锁，select … lock in share mode读到的是最新值，所以也叫当前读);写锁语法为select … for update（也叫排他锁）。行锁是加在索引上的，所以如果索引失效了，行锁会退化为表级锁。
| session A | session B |
|---|---|
| begin; update t set c=c+1 where id = 1; | |
| select * from t where id = 2 lock in share mode; select * from t where id = 1 lock in share mode; update t set c=c+1 where id =2; update t set c=c+1 where id =1; |
session A开启事务，并拿到写锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set c=c+1 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session B在id=1的行上去获取读锁，就会被session A 中的写锁阻塞，id=2的行则不受影响，可正常读取。
mysql> select * from t where id = 2 lock in share mode;
+----+------+
| id | c |
+----+------+
| 2 | 2 |
+----+------+
1 row in set (0.00 sec)
mysql> select * from t where id = 1 lock in share mode;
阻塞
mysql> update t set c=c+1 where id =1;
阻塞
mysql> update t set c=c+1 where id =2;
Query OK, 1 row affected (0.00 sec)
mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 101571 | Waiting on empty queue | NULL |
| 15 | root | localhost | test | Query | 11 | statistics | select * from t where id =1 lock in share mode |
| 27 | root | localhost | test | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------------------+
通过上面几种阻塞情况可以发现，长事务是导致数据库阻塞的一个原因之一，可以通过
**select * from information_schema.innodb_trx\G;**语句来查询长事务， 示例如下，从下面的信息中找到长事务的线程ID等详细信息。
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 1066289
trx_state: LOCK WAIT
trx_started: 2020-12-19 23:41:12
trx_requested_lock_id: 140342894575856:18:5:2:140342821210472
trx_wait_started: 2020-12-19 23:44:51
trx_weight: 4
trx_mysql_thread_id: 42
trx_query: update t set c=c+1 where id =1
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 3
trx_lock_memory_bytes: 1136
trx_rows_locked: 4
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_schedule_weight: 1
*************************** 2. row ***************************
trx_id: 1066288
trx_state: LOCK WAIT
trx_started: 2020-12-19 23:40:47
trx_requested_lock_id: 140342894575000:18:5:3:140342821204328
trx_wait_started: 2020-12-19 23:44:46
trx_weight: 4
trx_mysql_thread_id: 48
trx_query: update t set c=c+1 where id =2
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 3
trx_lock_memory_bytes: 1136
trx_rows_locked: 4
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_schedule_weight: 1
2 rows in set (0.00 sec)
mysql数据库的阻塞状态也可通过 select * from sys.innodb_lock_waits\G;语句来查询，查询示例如下。可以通过杀死线程方式来强制结束阻塞状态，如 sql_kill_blocking_connection: KILL 48。
mysql> select * from sys.innodb_lock_waits\G;
*************************** 1. row ***************************
wait_started: 2020-12-19 23:46:27
wait_age: 00:00:16
wait_age_secs: 16
locked_table: `test`.`t`
locked_table_schema: test
locked_table_name: t
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 1066289
waiting_trx_started: 2020-12-19 23:41:12
waiting_trx_age: 00:05:31
waiting_trx_rows_locked: 5
waiting_trx_rows_modified: 1
waiting_pid: 42
waiting_query: update t set c=c+1 where id =1
waiting_lock_id: 140342894575856:18:5:2:140342821210816
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 1066288
blocking_pid: 48
blocking_query: update t set c=c+1 where id =2
blocking_lock_id: 140342894575000:18:5:2:140342821203296
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2020-12-19 23:40:47
blocking_trx_age: 00:05:56
blocking_trx_rows_locked: 5
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 48
sql_kill_blocking_connection: KILL 48
*************************** 2. row ***************************
wait_started: 2020-12-19 23:46:30
wait_age: 00:00:13
wait_age_secs: 13
locked_table: `test`.`t`
locked_table_schema: test
locked_table_name: t
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 1066288
waiting_trx_started: 2020-12-19 23:40:47
waiting_trx_age: 00:05:56
waiting_trx_rows_locked: 5
waiting_trx_rows_modified: 1
waiting_pid: 48
waiting_query: update t set c=c+1 where id =2
waiting_lock_id: 140342894575000:18:5:3:140342821204672
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 1066289
blocking_pid: 42
blocking_query: update t set c=c+1 where id =1
blocking_lock_id: 140342894575856:18:5:3:140342821209440
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2020-12-19 23:41:12
blocking_trx_age: 00:05:31
blocking_trx_rows_locked: 5
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 42
sql_kill_blocking_connection: KILL 42
2 rows in set (0.01 sec)
总结
本文简单总结了几种mysql常见的数据库阻塞的情况。对数据库阻塞问题的排查有一定的帮助。但数据库出现阻塞的情况远不止这些，特别是间隙锁导致的阻塞本文中并没有体现。未完待续…
2716
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
