MySQL查询阻塞的场景

2024年 5月 13日 50.5k 0

本文操作环境为 MySQL 8.0。

1MySQL 表级锁有两种实现

  1. 服务器(SERVER)层:本层的锁定主要是元数据锁(metadata lock,MDL)。
  2. 存储引擎(ENGINE)层:本层不同的存储引擎可能会实现不同的锁定策略。例如 MyISAM 引擎实现了表级锁,InnoDB 存储引擎实现了行级锁和表级锁,其中表级锁是通过意向锁体现的。

元数据锁(MDL)是由 SERVER 层管理,用于锁定数据库对象的元数据信息,如:表结构、索引等。元数据锁可以阻止对表结构的改变,以确保数据定义的一致性。

元数据锁的类型

MySQL查询阻塞的场景-1点击放大

每种锁类型后面会详细介绍。简单来说,对于元数据锁而言,当对一个表进行增删改查操作的时候,会加 元数据读锁。当对表数据结构进行变更的时候会加 元数据写锁。它读写互斥,写写互斥,只有读读不冲突。

意向锁是在存储引擎层实现的,用于协调不同事务对表级锁和行级锁的请求。当一个事务在某个层次(表级或行级)上获取锁时,会首先获取对应层次的意向锁,以提示其他事务该事务在该层次上有锁的意向。这样可以在更高层次上减少锁冲突,提高并发性能。

InnoDB 存储引擎的意向锁种类

  1. 意向共享锁(Intention Shared Lock,IS):事务打算给数据行加共享锁(S 锁)。
  2. 意向排他锁(Intention Exclusive Lock,IX):事务打算给数据行加排他锁(X 锁)。

这样看来,表对象不可读写有种情况可能就是元数据锁互斥所导致的。

2Waiting for table metadata lock

本节中未完成的读写事务,在实际中可能是未完成的大事务,也可能是未显式结束的事务。

元数据锁互斥(未完成的读事务)

会话 1 执行:有未完成的读事务,此时获取了元数据共享读锁。

MDL_SHARED_READ: 这个锁允许会话读取表的数据,并允许其他会话获取 SHARED_READ 或 SHARED_WRITE 锁,但不允许获取 SHARED_NO_READ_WRITE 或 EXCLUSIVE 锁。

MySQL查询阻塞的场景-2

会话 2 执行:ALTER 表结构变更语句,此时 ALTER 语句要获取元数据排它锁。

MDL_EXCLUSIVE: 这个锁允许会话读取和修改表的数据和结构,但不允许其他会话获取任何类型的锁。

MySQL查询阻塞的场景-3

元数据锁互斥等待,之后其他会话对于所涉及表不可读写。

元数据锁互斥(未完成的写事务)

会话 1 执行:有未完成的写事务,此时获取了元数据写锁。

MDL_SHARED_WRITE: 这个锁允许会话读取和修改表的数据,并允许其他会话获取 SHARED_READ 锁,但不允许获取 SHARED_WRITE、SHARED_NO_READ_WRITE 或 EXCLUSIVE 锁。

MySQL查询阻塞的场景-4

会话 2 执行:ALTER 表结构变更语句,此时 ALTER 语句要获取元数据排它锁。

MDL_EXCLUSIVE:这个锁允许会话读取和修改表的数据和结构,但不允许其他会话获取任何类型的锁。

MySQL查询阻塞的场景-5

元数据锁互斥等待,之后其他会话对于所涉及表不可读写。

LOCK TABLES ... READ/WRITE

LOCK TABLES 可以显式获取表锁,需要注意的是会话只能自己获取和释放表锁。UNLOCK TABLES 可以显式释放当前会话的表锁。

LOCK TABLES … READ

会话 1 执行:lock tables db_version read;

MDL_SHARED_READ_ONLY: 这个锁允许会话读取表的数据,并允许其他会话获取 SHARED_READ 锁,但不允许获取 SHARED_WRITE、SHARED_NO_READ_WRITE 或 EXCLUSIVE 锁。

MySQL查询阻塞的场景-6

此时 db_version
 加了元数据共享只读锁。

会话 2 执行:ALTER 表结构变更语句,此时 ALTER 语句要获取元数据排它锁,元数据锁互斥等待。

MySQL查询阻塞的场景-7

之后所涉及表对象将不可读写。

LOCK TABLES … WRITE

会话 1 执行:lock tables db_version write;

MDL_SHARED_NO_READ_WRITE: 这个锁允许当前会话读取和修改表的数据,但不允许其他会话获取任何类型的锁。

MySQL查询阻塞的场景-8

此时 db_version
 加上了独占写锁。只能在 会话 1 读写,它会阻止其他会话获取任何类型的锁。

MySQL查询阻塞的场景-9

因此其他会话既不能读也不能写,当然查询也会被阻塞了。

MySQL查询阻塞的场景-10

需要注意的是,此时 会话 1 对其他表也会不可读写。

3FLUSH TABLES & WAITING FOR TABLE FLUSH

FLUSH TABLES 关闭所有打开的表,强制关闭所有正在使用的表,并刷新准备好的语句缓存。当存在活动的 LOCK TABLES 时,不允许执行 FLUSH TABLES 操作。

FLUSH TABLES

  1. 当 ALTER 表结构时,执行 FLUSH TABLES  阻塞,从而导致表对象不可读写。

MySQL查询阻塞的场景-11

  1. 当  LOCK TABLES  后,执行  FLUSH TABLES  会被阻塞,从而导致表对象不可读写。

  • 会话 1 执行:lock tables db_version read;
  • 会话 2 执行:flush tables;

MySQL查询阻塞的场景-12

此时,会话 2 会被阻塞,其他会话对所涉及表将不可读写。SHOW PROCESSLIST 中会提示 Waiting for table flush

需要说明的是,会话 1  执行完 lock tables...read lock
 后,其他会话执行 DML 增删改语句,虽然会因获取不到元数据锁而阻塞,但不会阻塞其他会话执行 SELECT 查询。

MySQL查询阻塞的场景-13

换言之,执行 lock tables...read
 后,当遇到元数据锁排它锁互斥阻塞(ALTER 语句)或者  FLUSH TABLES   发生阻塞后,才会发生所涉及表对象不可读写。

4处理延伸

如何处理并找到源头 SQL 呢?

对于因元数据锁互斥而导致的表不可读写,一般可以通过 sys
 库下的内置视图来查看。可能会涉及的表:

sys.schema_table_lock_waits: 可直接通过 sys
 下内置视图,看到元数据锁互斥的相关信息。

information_schema.innodb_trx: 找到长时间未提交的事务。

对于因 FLUSH TABLE 等待而导致的表不可读写的场景,通过上述视 图/表 是不一定有数据的。大致会有以下两种情况:

Waiting for table flush: 可以按如下方式寻找源头。这种情况主要出现于因 FLUSH TABLES 而等待后,执行 DML 语句。

SELECT
  b.PROCESSLIST_ID,
  b.THREAD_ID,
  a.OBJECT_NAME,
  a.LOCK_TYPE,
  a.LOCK_STATUS,
  b.PROCESSLIST_STATE 
FROM
  `performance_schema`.metadata_locks a
  LEFT JOIN `performance_schema`.threads b ON a.OWNER_THREAD_ID = b.THREAD_ID 
WHERE
  a.OBJECT_SCHEMA = 'tmp';

也可以通过线程 ID 找到会话最近的 10 条语句进一步判断确认。

select THREAD_ID,event_id,sql_text from 
`performance_schema`.events_statements_history
where THREAD_ID =  14503
order by event_id;

Waiting for table metadata lock: 可以参考元数据锁互斥而导致的表不可读写处理。这种情况主要出现于因 FLUSH TABLES 而等待后,执行 DDL 语句如 ALTER TABLE。

5因此

以下情况会导致表对象不可读写:

  1. 因 Waiting for table metadata lock 而导致的表对象不可读写。
  2. 因 Waiting for table flush 而导致的表对象不可读写。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论