MySQL 锁

最新推荐文章于 2025-11-30 16:29:01 发布

转载最新推荐文章于 2025-11-30 16:29:01 发布 · 193 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://zhuanlan.zhihu.com/p/29150809

文章标签：

#mysql

MySQL 专栏收录该内容

18 篇文章

订阅专栏

文章目录

MDL锁（metadata lock）

MySQL5.5版本引入了MDL锁（metadata lock），用于解决或者保证DDL操作与DML操作之间的一致性。例如下面的这种情形：

会话1	会话2
BEGIN;
SELECT * FROM XXX
	DROP TABLE XXX
SELECT * FROM XXX

若没有MDL锁的保护，则会话 2 可以直接执行 DDL 操作，并且导致事务1出错，5.1版本即是如此。5.5 版本加入 MDL 锁就在于保护这种情况的发生，由于事务1开启了查询，那么获得了 MDL 锁，锁的模式为SHARED_READ，事务2要执行DDL，则需获得EXCLUSIVE锁，两者互斥，所以事务2需要等待。
InnoDB层已经有了IS、IX这样的意向锁，有同学觉得可以用来实现上述例子的并发控制。但由于MySQL是Server-Engine架构，所以MDL锁是在Server中实现。另外，MDL锁还能实现其他粒度级别的锁，比如全局锁、库级别的锁、表空间级别的锁，这是InnoDB存储引擎层不能直接实现的锁。

Innodb 中的锁

行锁
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
表锁
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
锁的兼容性
加锁方式
- 意向锁是 InnoDB 自动加的，不需用户干预
- 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB 会自动给涉及数据行加排他锁（X)
- 对于普通 SELECT 语句，InnoDB 不会加任何锁，事务可以通过以下语句显式给记录集加共享锁或排他锁

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE，
          其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁，
          但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE，
          其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。

使用两阶段锁协议
1. 随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁
2. 锁只有在执行 commit 或者 rollback 的时候才会释放，并且所有的锁都是在同一时刻被释放

InnoDB 行锁实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁
不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁
只有执行计划真正使用了索引，才能使用行锁，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁

InnoDB 间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）

不同隔离级别下 InnoDB 锁的使用情况

乐观锁

- 乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁，而是在应用层面实现的。

SQL

# 可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况
mysql> show status like 'innodb_row_lock%'; 
+-------------------------------+-------+ 
| Variable_name | Value | 
+-------------------------------+-------+ 
| InnoDB_row_lock_current_waits | 0 | 
| InnoDB_row_lock_time | 0 | 
| InnoDB_row_lock_time_avg | 0 | 
| InnoDB_row_lock_time_max | 0 | 
| InnoDB_row_lock_waits | 0 | 
+-------------------------------+-------+ 

# LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
# UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时，
SET AUTOCOMMIT=0; 
# 在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则MySQL 不会给表加锁
# 事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES会隐含地提交事务；
# COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用UNLOCK TABLES 释放表锁。
LOCK TABLES t1 WRITE, t2 READ, ...; 
[do something with tables t1 and t2 here]; 
COMMIT; 
UNLOCK TABLES;