sql卡住不动，如何排查？

MySQL很少在单用户环境下使用。通常，它会同时处理很多的连接线程，这些线程来自不同的用户，执行不同的任务。这些并发连接可能会访问相同的数据库和表，所以当某个连接发生问题的时候，很难判断数据库的状态。

并发问题的一个典型特征就是本来优化良好的查询突然变慢，这种变慢可能不是一直发生，只是偶尔随机出现几次，不过这也需要引起我们的注意。

MySQL服务器用锁和事务来处理对表的并发访问。当线程请求数据集的时候就会加锁。在MySQL中，这可以是表，行，页或者元数据。当线程结束处理特定的数据集之后，它就会释放锁。

数据库事务是处理一致性和可靠性工作的最小单位，这使得用户可以避免与其他事务交互时可能产生的风险。

锁

MySQL有4种类型的锁：表锁，行锁，页锁，元数据锁。

1：表锁

顾名思义，表锁会锁住整个表，因此没有人可以访问表中任何行，直到持有锁的线程解锁该表。

如果设置的是读锁，那么写访问是禁止的，如果设置的是写锁，那么读访问和写访问都是禁止的。

当访问表并且该表所使用的的存储引擎支持表锁的时候，即会产生表锁，比如MyISAM引擎，也可以在任何引擎上显式调用LOCK TABLES来产生表锁。

2：行锁

行锁的粒度更细一些，仅会锁住一行或者正在被线程访问的任何几行，因此同一个表中的其他行可以被其他并发线程访问。

3：页锁

页锁会锁住一页，不过页锁仅在比较少见的BDB存储引擎中存在。

4：元数据锁

元数据锁是MySQL 5.5版本中的新特性，该锁仅对表中的元数据启用，当有线程开始使用表的时候，元数据锁会锁住表的所有元数据。

元数据是DDL（数据定义语言或叫数据描述语言）语句的更改信息，如CREATE,DROP,ALTER等修改语句。

在老版本中的MySQL中引入元数据锁是为了解决线程可以在其他线程中的并发事务使用相同表的情况下修改表定义或者删除表的问题。

下面我们用一个行锁和一个表锁的案例来分析下。

案例1：行锁

mysql> show create table student;
+———+————————–+
| Table | Create Table |
+———+————————–+
| student | CREATE TABLE student (
id int NOT NULL AUTO_INCREMENT,
user_name varchar(16) NOT NULL DEFAULT ‘0’,
age tinyint unsigned NOT NULL DEFAULT ‘0’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+———+—————————+

mysql> select * from student;
+—-+———–+—–+
| id | user_name | age |
+—-+———–+—–+
| 1 | zhangsan | 22 |
| 2 | lisi | 23 |
| 3 | wangwu | 24 |
| 4 | zhaoliu | 24 |
| 5 | xiaoming | 24 |
+—-+———–+—–+
5 rows in set (0.00 sec)

客户端A：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update student set user_name=’test1′ where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

客户端B：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update student set user_name=’test22222′ where id=1;

这时候我们可以发现这条sql一直在等待，那么我们排查一下到底是什么问题导致的。那我们开启另外一个客户端进行排查

客户端C：

step1：

我们使用show full processlist。此语句的结果表示服务器中执行的一组线程当前正在执行的操作：

mysql> show full processlist\G
* **********************************1. row * *********************************************
Id: 5
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 875280
State: Waiting on empty queue
Info: NULL
* **********************************2. row * *********************************************
Id: 11
User: root
Host: localhost
db: performance_schema
Command: Query
Time: 0
State: init
Info: show full processlist
* **********************************3. row * *********************************************
Id: 13
User: root
Host: localhost
db: study_go
Command: Sleep
Time: 6351
State:
Info: NULL
* **********************************4. row * *********************************************
Id: 14
User: root
Host: localhost
db: study_go
Command: Query
Time: 25
State: updating
Info: update student set user_name=’test22222′ where id=1
* **********************************5. row * *********************************************
Id: 15
User: root
Host: localhost
db: study_go
Command: Sleep
Time: 493
State:
Info: NULL
5 rows in set (0.00 sec)

输出中的字段解释如下：

Id：MySQL服务器中运行的连接的线程ID
User，Host，db：客户端连接到服务器时使用的连接选项
Command：线程中当前执行的命令
Time：从线程开始执行命令到现在消耗的时间
State：线程的内部状态
Info：表明线程当前正在进行的工作，如果展示的是查询语句，表明该语句正在执行，如果值为NULL，表明线程正在休眠，并等待下一条命令。

我们可以看到这四行的State是updating，这个updating的意思是线程正在搜索要更新的行并更新它们。

其他State的值可以参考https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

step2：

为了确定在InnoDB中一个请求是否阻塞，我们来执行以下命令：SHOW ENGINE INNODB STATUS

mysql> SHOW ENGINE INNODB STATUS\G
* 1. row *
Type: InnoDB
Name:
Status:

TRANSACTIONS

Trx id counter 1132594
Purge done for trx’s n:o < 1132592 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 421627580635040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421627580634184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421627580631616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421627580630760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

—TRANSACTION 1132593, ACTIVE 1088 sec starting index read

mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 14, OS thread handle 140152575280896, query id 681 localhost root updating
update student set user_name=’test22222′ where id=1
——- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 80 index PRIMARY of table study_go.student trx id 1132593 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000114830; asc H0;;
2: len 7; hex 010000017c11a4; asc | ;;
3: len 5; hex 7465737431; asc test1;;
4: len 1; hex 16; asc ;;

—TRANSACTION 1132592, ACTIVE 1106 sec

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 140152575575808, query id 664 localhost root

下面我们开始分析：从上面的信息我们可以看到

TRANSACTION 1132593：这是事务的ID
mysql tables in use 1, locked 1：表示有1个表在使用，一个表被锁住。
MySQL thread id 14, OS thread handle 140152575280896, query id 681 localhost root updating：MySQL线程对应的相关信息，包括：线程ID，查询ID，用户凭证和MySQL状态。
update student set user_name=’test22222′ where id=1：当前执行的查询。

我们接着查看一下执行锁住行的查询的事务的信息

—TRANSACTION 1132592, ACTIVE 1106 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 140152575575808, query id 664 localhost root

说明线程ID为15的持有行锁，但我们没办法确定它持有的锁是线程ID为14所需要的。所以我们需要借助下面的sql来查出功能更精确的信息。

step3：

注意：我们当前的mysql版本是8.0，如果是5.7的话，应该是information_schema库的innodb_locks,innodb_lock_waits，对应到8.0就是performance_schema.data_locks和performance_schema.data_lock_waits表

mysql> select trx.trx_mysql_thread_id,
       locks.OBJECT_SCHEMA,
       locks.OBJECT_NAME,
       trx.trx_query,
       locks.LOCK_TYPE,
       locks.LOCK_MODE,
       locks.LOCK_STATUS,
       locks.LOCK_DATA
from performance_schema.data_lock_waits as waits
         join performance_schema.data_locks as locks
              on waits.BLOCKING_ENGINE_TRANSACTION_ID = locks.ENGINE_TRANSACTION_ID
         join information_schema.innodb_trx as trx on waits.BLOCKING_ENGINE_TRANSACTION_ID = trx.trx_id
union
select trx.trx_mysql_thread_id,
       locks.OBJECT_SCHEMA,
       locks.OBJECT_NAME,
       trx.trx_query,
       locks.LOCK_TYPE,
       locks.LOCK_MODE,
       locks.LOCK_STATUS,
       locks.LOCK_DATA
from performance_schema.data_lock_waits as waits
         join performance_schema.data_locks as locks
              on waits.REQUESTING_ENGINE_TRANSACTION_ID = locks.ENGINE_TRANSACTION_ID
         join information_schema.innodb_trx as trx on waits.REQUESTING_ENGINE_TRANSACTION_ID = trx.trx_id;

* **************************************1. row * *************************************
trx_mysql_thread_id: 15
OBJECT_SCHEMA: study_go
OBJECT_NAME: student
trx_query: NULL
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
* **************************************2. row * *************************************
trx_mysql_thread_id: 15
OBJECT_SCHEMA: study_go
OBJECT_NAME: student
trx_query: NULL
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
* *************************************3. row * **************************************
trx_mysql_thread_id: 14
OBJECT_SCHEMA: study_go
OBJECT_NAME: student
trx_query: update student set user_name=’test22222′ where id=1
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
* *************************************4. row * ****************************************
trx_mysql_thread_id: 14
OBJECT_SCHEMA: study_go
OBJECT_NAME: student
trx_query: update student set user_name=’test22222′ where id=1
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 1
4 rows in set (0.00 sec)

LOCK_MODE是IX或IS的，表示是意向锁，我们可以先忽略，那么就上面的结果，我们可以先忽略第1行和第3行。

我们可以看到：

第2行的LOCK_STATUS=GRANTED，LOCK_TYPE=RECORD，LOCK_DATA=1，说明线程ID为15的持有行锁，并且锁定的行数为一行。

第4行的LOCK_STATUS=WAITING，LOCK_TYPE=RECORD，LOCK_DATA=1说明在等待行锁。并且从OBJECT_SCHEMA和OBJECT_NAME这两个字段可以看出线程ID为15持有线程ID为14的记录锁。

既然我们知道了MySQL线程的ID，我们就可以对该阻塞的事务做任何想要的操作：继续等待完成还是终止该事务均可。

如果我们在应用程序中执行该命令，我们还可以分析是什么导致了此类锁定问题，并可以进行修正以避免今后再发生问题。

案例2：表锁

客户端A：

mysql> lock table student read;
Query OK, 0 rows affected (0.00 sec)

客户端B：

mysql> insert into student select 6,’xiaogang’,33;

这时候我们发现insert into语句一直卡住，我们开启另一个客户端来进行排查问题。

客户端C：

mysql> show full processlist\G
* ****************************************1. row *************************************
Id: 5
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 877263
State: Waiting on empty queue
Info: NULL
* ****************************************2. row *************************************
Id: 11
User: root
Host: localhost
db: performance_schema
Command: Query
Time: 0
State: init
Info: show full processlist
* ****************************************3. row *************************************
Id: 13
User: root
Host: localhost
db: study_go
Command: Sleep
Time: 8334
State:
Info: NULL
* ****************************************4. row *************************************
Id: 14
User: root
Host: localhost
db: study_go
Command: Query
Time: 10
State: Waiting for table metadata lock
Info: insert into student select 6,’xiaogang’,33
* ****************************************15 row *************************************
Id: 15
User: root
Host: localhost
db: study_go
Command: Sleep
Time: 119
State:
Info: NULL
* ****************************************6. row *************************************
Id: 16
User: root
Host: localhost
db: study_go
Command: Sleep
Time: 15
State:
Info: NULL
6 rows in set (0.00 sec)

我们可以看到第4行的State是Waiting for table metadata lock，表示服务器正在等待从元数据锁定子系统获得一个metadata锁：

mysql> SHOW ENGINE INNODB STATUS\G
**********************************1. row *************************************
Type: InnoDB
Name:
Status:

TRANSACTIONS

Trx id counter 1132599
Purge done for trx’s n:o < 1132599 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 421627580635896, not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421627580632472, not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421627580633328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421627580635040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421627580634184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421627580631616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421627580630760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

有用的信息就一个mysql tables in use 1, locked 1，但其他更具体的信息就没有了，接下来我们通过下面的sql查询下，看下能否有结果。

mysql> select trx.trx_mysql_thread_id,locks.OBJECT_SCHEMA,locks.OBJECT_NAME,trx.trx_query,locks.LOCK_TYPE,locks.LOCK_MODE,locks.LOCK_STATUS,locks.LOCK_DATA from performance_schema.data_lock_waits as waits join performance_schema.data_locks as locks on waits.BLOCKING_ENGINE_TRANSACTION_ID=locks.ENGINE_TRANSACTION_ID join information_schema.innodb_trx as trx on waits.BLOCKING_ENGINE_TRANSACTION_ID=trx.trx_id union select trx.trx_mysql_thread_id,locks.OBJECT_SCHEMA,locks.OBJECT_NAME,trx.trx_query,locks.LOCK_TYPE,locks.LOCK_MODE,locks.LOCK_STATUS,locks.LOCK_DATA from performance_schema.data_lock_waits as waits join performance_schema.data_locks as locks on waits.REQUESTING_ENGINE_TRANSACTION_ID=locks.ENGINE_TRANSACTION_ID join information_schema.innodb_trx as trx on waits.REQUESTING_ENGINE_TRANSACTION_ID=trx.trx_id\G
Empty set (0.00 sec)

结果还是为空，那这是怎么回事呢？上面我们从show full processlist可以看到State是Waiting for table metadata lock，说明在等待元数据锁，那我们可以尝试着查另外一张表。

MySQL在5.7开始提供一个performance_schema.metadata_locks显示各种Server层的锁信息（包括全局读锁和DML锁信息等）。

SQL语句中， owner_thread_id != sys.ps_thread_id(connection_id())表示非本连接的其他连接。

mysql> select * from performance_schema.metadata_locks where owner_thread_id != sys.ps_thread_id(connection_id())\G
* ********************************1. row **********************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: study_go
OBJECT_NAME: student
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140150699713328
LOCK_TYPE: SHARED_READ_ONLY
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5761
OWNER_THREAD_ID: 57
OWNER_EVENT_ID: 11
* ********************************2. row **********************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140150487143984
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:3029
OWNER_THREAD_ID: 54
OWNER_EVENT_ID: 29
* ********************************3. row **********************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: study_go
OBJECT_NAME: student
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140150487182480
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_parse.cc:5761
OWNER_THREAD_ID: 54
OWNER_EVENT_ID: 29
3 rows in set (0.00 sec)

输出中的字段解释如下：

OBJECT_TYPE：元数据锁子系统中使用的锁类型。取值为GLOBAL、SCHEMA、TABLE、FUNCTION、PROCEDURE、TRIGGER(目前未使用)、EVENT、COMMIT、USER LEVEL LOCK、TABLESPACE或LOCKING SERVICE。
OBJECT_SCHEMA：对应的数据库
OBJECT_NAME：对应的表
COLUMN_NAME：对应的列
OBJECT_INSTANCE_BEGIN：被检测对象在内存中的地址。
LOCK_TYPE：元数据锁子系统的锁类型。取值为:INTENTION_EXCLUSIVE、SHARED、SHARED_HIGH_PRIO、SHARED_READ、SHARED_WRITE、SHARED_UPGRADABLE、SHARED_NO_WRITE、SHARED_NO_READ_WRITE或EXCLUSIVE。
LOCK_DURATION：来自元数据锁定子系统的锁定时间。取值为STATEMENT、TRANSACTION或EXPLICIT。STATEMENT和TRANSACTION值分别表示在语句或事务结束时隐式释放的锁。EXPLICIT值表示在语句或事务结束时仍然存在的锁，并通过显式操作释放，例如FLUSH TABLES with READ LOCK获得的全局锁。
LOCK_STATUS：来自元数据锁定子系统的锁定状态。取值为PENDING、GRANTED、VICTIM、TIMEOUT、KILLED、PRE_ACQUIRE_NOTIFY或POST_RELEASE_NOTIFY。
SOURCE：包含产生事件的插装代码的源文件的名称，以及插装发生时文件中的行号。这使您能够检查源代码，以准确地确定所涉及的代码。
OWNER_THREAD_ID：请求元数据锁的线程。
OWNER_EVENT_ID：请求元数据锁的事件。

LOCK_STATUS说明：

当请求并立即获得元数据锁时，将插入状态为GRANTED的行。
当元数据锁被请求并且没有立即获得时，将插入一个状态为PENDING的行。
当先前请求的元数据锁被授予时，它的行状态被更新为GRANTED。
当元数据锁被释放时，该行将被删除。
当死锁检测器取消挂起的锁请求以打破死锁(ER_LOCK_DEADLOCK)时，它的行状态从PENDING更新为VICTIM。
当一个挂起的锁请求超时时(ER_LOCK_WAIT_TIMEOUT)，它的行状态从PENDING更新为TIMEOUT。
当被授予的锁或挂起的锁请求被杀死时，它的行状态从GRANTED或PENDING更新为KILLED。
VICTIM、TIMEOUT和KILLED状态值很简短，表示即将删除锁行。
PRE_ACQUIRE_NOTIFY 和 POST_RELEASE_NOTIFY 状态值很简短，表示元数据锁定子系统在进入锁定获取操作或离开锁定释放操作时通知感兴趣的存储引擎。

从客户端B中，我们知道是student表的插入被卡住了，所以我们只需要查看OBJECT_NAME是student的记录。

我们可以看到OWNER_THREAD_ID为57的LOCK_STATUS为GRANTED,且LOCK_TYPE为SHARED_READ_ONLY，OWNER_THREAD_ID为54的LOCK_STATUS为PENDING，且LOCK_TYPE为SHARED_WRITE。我们可以判断出结果OWNER_THREAD_ID为57的持有metadata lock。

我们查出根据内部线程号查出对应的processlist_id：

mysql> select a.thread_id,b.processlist_id,a.SQL_text from performance_schema.events_statements_current a join performance_schema.threads b on a.thread_id=b.thread_id where a.thread_id in (57,54)\G
* ********************************1. row **********************************
thread_id: 54
processlist_id: 14
SQL_text: insert into student select 6,’xiaogang’,33
* ********************************2. row **********************************
thread_id: 57
processlist_id: 17
SQL_text: lock table student read
2 rows in set (0.00 sec)

到这里，我们知道了持有锁的线程的ID是17，那么接下来按照自己的情况做对应的处理就可以了。

参考资料

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-metadata-locks-table.html

https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-metadata-locks-table.html

https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-lock-waits-table.html

《MySQL排错指南》

sql卡住不动，如何排查？

锁

1：表锁

2：行锁

3：页锁

4：元数据锁

案例1：行锁

客户端A：

客户端B：

客户端C：

step1：

step2：

step3：

案例2：表锁

客户端A：

客户端B：

客户端C：

相关文章

衡量SQL查询的三个指标

如何删除大表

备份与恢复

字符集设置

数据类型：JSON

发表评论取消回复

锁

1：表锁

2：行锁

3：页锁

4：元数据锁

案例1： 行锁

客户端A：

客户端B：

客户端C：

step1：

step2：

step3：

案例2：表锁

客户端A：

客户端B：

客户端C：

相关文章

衡量SQL查询的三个指标

如何删除大表

备份与恢复

字符集设置

数据类型：JSON

发表评论 取消回复

案例1：行锁

发表评论取消回复