说下 MySQL 中的锁

本文中的所有内容基于存储引擎为 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 写锁。

规则是：读锁之间不互斥，读写锁和写锁互斥，保证变更表结构操作的安全性。

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