MySQL中锁的介绍

无论是Oracle、MySQL、PG，锁可能都是一个非常重要的特性，它提供了数据库的很多功能，但如果使用不当，就会到来风险。

关于锁的历史文章如下，《如何定位锁定用户的元凶？》《MySQL快速定位全局锁的途径》
《MySQL锁等待超时的解决路径》《MySQL的MDL锁解惑》《InnoDB快速定位行锁争用会话的过程和操作》《小白学习MySQL - 查询会锁表？》

徐老师写的这篇文章《MySQL的锁》介绍一些MySQL数据库中锁的知识，值得学习借鉴。

MySQL的锁包括服务器级别的锁，存储引擎级别的锁，及互斥锁。服务器级别的锁包括表锁和元数据锁，存储引擎的锁是行级别的锁，由InnoDB引擎控制。互斥锁是低级别的锁，适用于内部的资源，用于同步低级别代码的操作，确保一次只有一个线程能够访问，例如，日志文件、自增列的计数器，及InnoDB buffer pool的互斥。

如何识别锁争用？

用户首先需要了解InnoDB的锁，之后可以通过如下方法识别不同的锁争用：

• 通过SHOW PROCESSLIST 或查询Performance Schema的threads表识别长时运行或阻塞的查询

• 通过查询Performance Schema中同步的指标(/Wait/synch/mutex/*)来识别互斥争用

• 通过查询Performance Schema和Information Schema中的视图，识别阻塞和等待的事务

• 通过查询Performance Schema中的data_locks表，识别当前的锁

• 通过查询Performance Schema中的metadata_locks表，识别当前的元数据锁

InnoDB表锁

InnoDB的表锁包括共享锁S、排他锁X、意向共享锁IS，及意向排他锁。

共享锁S：对表加锁用于读取

排它锁X：对表加锁用于写入

意向共享锁IS：对表加锁，以允许执行行级别的共享锁

意向排它锁IX：对表加锁，以允许执行行级别的排它锁

当一个事务对表加锁时，如果遇到另一个事务的锁，需要两个事务的锁类型兼容才能够获得锁。表锁类型的兼容矩阵如下：

InnoDB的行锁 

一个事务在获得行锁之前必须获得表的意向锁（IS或IX）

• 共享锁S：允许事务读取一行，并允许其他事务获得该行的共享锁

• 排它锁X：允许事务读取并写入一行，但不允许其他事务对该行加锁

假设，事务A对一行具有排他锁，事务B请求对该行加共享锁，由于两个类型的锁冲突，事务B被阻塞执行，等待获得行锁。假设，事务C对一行具有共享锁，事务D同样对该行加共享锁，两个事务的锁不发生冲突，则事务D可以获得锁。

MySQL提供了非锁定读取功能。一个正常的SELECT语句不使用“FOR SHARE”或“FOR UPDATE”时，读取InnoDB的数据不需要获取任何锁，如果使用“FOR SHARE”则要求一个共享锁，使用“FOR UPDATE”将要求一个排他锁。

使用SHOW PROCESSLIST获取锁信息

使用“SHOW PROCESSLIST”在输出的“state”列中显示锁的相关信息：

“State: Waiting for table metadata lock”表示表具有冲突的锁

“State: update”或“State: Searching rows for update ”表示具有InnoDB锁（表锁或行锁）

注意，使用“SHOW PROCESSLIST”无法获得锁的内部信息，用户也可以查询Performance Schema中的threads表获得同样的信息。推荐用户使用Performance Schema，可以减少对服务器性能的影响。

使用Performance Schema和Information Schema监视锁

Performance Schema和Information Schema中的视图包含与事务关联的锁信息，包括正在加锁和被锁的语句：

• INFORMATION_SCHEMA.INNODB_TRX: 事务和锁的通用信息。信息包括事务ID、线程ID、事务开始时间、当前执行的查询、事务状态，及锁的状态

• performance_schema.data_locks: 每个锁和被锁资源的信息。信息包括引擎、引擎锁ID、引擎的内部事务ID、线程ID、事件ID、对象模式、对象名称、索引名称、锁在内存中的地址、锁类型、锁模式、锁状态，及锁数据

• performance_schema.data_lock_waits: 被锁的事务，及被该事务锁的事务。包括存储引擎要求的锁ID、锁请求的引擎内部事务ID、锁请求会话的线程ID、阻塞的锁ID、具有阻塞锁的事务的引擎内部ID、具有阻塞锁会话的线程ID，及会话中阻塞锁的Performance Schema事件ID

• sys.innodb_lock_waits：方便查询加锁和等待锁的语句的视图，组合了上述三个视图的内容

• performance_schema_metadata_locks：当一个事务访问一个表时，InnoDB通过元数据锁阻止在该表上进行的DDL操作。通过该视图的“LOCK_STATUS”列，识别每个锁的状态。当一个元数据锁请求立即获得时，MySQL会插入一个新的行，状态使用“GRANTED”，当请求一个无需立刻获得的元数据锁时，MySQL会插入一个状态为“PENDING”的新行。当元数据锁释放后，会删除该行。

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，

近期更新的文章：《MySQL的root密码如何找回？》
《从Oracle大佬的成长轨迹中学习对我们有所帮助的》《MySQL中关于EXPLAIN的一些新玩法》《MySQL的EXPLAIN功能》《MySQL JDBC连接池最高效的连接检测语句是什么？》
近期的热文：《推荐一篇Oracle RAC Cache Fusion的经典论文》
《"红警"游戏开源代码带给我们的震撼》
文章分类和索引：《公众号1300篇文章分类和索引》