说下 MySQL 中的锁

妖狐艹你老母 2023-09-24 12:52 8阅读 0赞

> 本文中的所有内容基于存储引擎为 InnoDB。

## 全局锁 ##

全局锁就是对整个 MySQL 实例加锁，MySQL 提供的命令为 `Flush tables with read lock`（下文缩写为 FTWRL）。

当需要整个库是只读状态时可以用这个命令。使用了这个命令后，其他线程的以下语句会被阻塞：

1.  数据更新语句（数据增删改）
2.  数据定义语句（建表、改表）
3.  更新类型的事务类提交语句

典型的使用场景是做全库逻辑备份，但是有两个缺点：

1.  主库上操作，备份期间不能更新，业务暂停
2.  从库上操作，备份期间 binlog 没法更新，造成主从延迟

简单说下 MySQL 备份：官方自带的工具是 mysqldump，可以使用参数 –single-transaction 来开启新的事务，拿到最新的视图（只支持有事务机制的引擎，MyISAM 不支持）。

> 不使用 set global readonly=true 的原因：
> 
> 1.  readonly 有其它用途，比如判断主库还是备库
> 2.  FTWRL 会在客户端出异常的时候自动释放，实例会恢复到之前的状态。readonly 不会，除非再次设置，在未重新设置期间全库不可写。

## 表锁 ##

### 表锁 ###

命令是 `lock tables … read/write`，可以使用 `unlock table` 主动释放也可以类似 FTWRL 自动释放。

如果在 A 线程执行 `lock tables t1 read, t2 write`，造成的影响如下：

*  其他线程写 t1、读写 t2 的语句都会被阻塞
 *  A 线程对 t1 只能读、可以对 t2 读写

### MDL 锁 ###

MDL（metadata lock，元数据锁），不需要显式使用，访问的时候会自动加上，事务提交后自动释放。

> 对表数据增删改查的时候，加 MDL 读锁；对表结构进行变更，加 MDL 写锁。

规则是：读锁之间不互斥，读写锁和写锁互斥，保证变更表结构操作的安全性。  
![说下 MySQL 中的锁][MySQL]  
session A 启动后，session B 可以正常读，session C 的 DDL 语句会被阻塞。为什么 session D 语句也会被阻塞呢？这是因为 session C 的被阻塞，之后对表添加的 DML 读锁也会被阻塞。

那么我们怎么给表加字段呢：

1.  首先解决事务问题，从 MySQL 的 information\_schema 库的 innodb\_trx 表中看到执行的事务，kill 掉长事务，然后添加字段；
2.  如果新请求来得快，kill 不掉长事务，可以使用 `ALTER TABLE tbl_name NOWAIT add column` 或 `ALTER TABLE tbl_name WAIT N add column` 语句。

## 行锁 ##

> MyISAM 不支持行锁

行锁指表中一行数据或者一行记录的锁。

在 InnoDB 事务中，行锁是在需要的时候加上，事务结束或者提交才释放（两阶段锁协议）。期间其它事务如果想更新相关行会被阻塞。

启发：如果事务需要锁多行，要把可能造成锁冲突影响并发度的锁往后放。

## Shared and Exclusive Locks ##

## Intention Locks ##

## Gap Locks ##

## Next-Key Locks ##

[MySQL]: https://img-blog.csdnimg.cn/f7a1613dd7044440b682a1a973ba81ce.png