Mysql锁

MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

主库上备份，那么在备份期间都不能执行更新。从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

备份是否可以不加锁？不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

可重复读隔离级别下可以拿到一致性视图。官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。single-transaction 方法只适用于所有的表使用事务引擎的库。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

一、表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表

线程A对T加读锁，限制其它线程写T，限制本线程写T，线程A不能访问其它表。

线程A对T加写锁，限制其它线程读写T，线程A不能访问其它表。

二、MDL锁

另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

• 事务中的MDL锁，在语句执行开始时申请，事务提交后再释放。

相关问题：踩坑：给一个小表加个字段，导致整个库挂了。

执行顺序：

A：begin; select * from t limit 1; (未提交)

B：select * from t limit 1;

C：alter table t add f int; (blocked)

D：select * from t limit 1;(blocked)

sessionA先启动，加MDL读锁，B也是加MDL读锁，不影响B查询。C需要MDL写锁，因为A的MDL读锁还没有释放，所以C会被阻塞。但是之后在表t上申请MDL读锁的请求也会被C阻塞（增删改查）。等于表现在不可读写了

Q：C没有加锁成功为什么会阻塞D？

应该是因为CD均在同一个锁队列中，决定谁先执行，sessionC在等待的时候就开始阻塞后来的请求了。如果表查询语句频繁且客户端有重试，即超时起一个新session请求，库的线程很快会饱满。

Q：如何安全地给小表加字段？

1.首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

2.如果是一个热点表，请求频繁kill不过来。理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。 ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。MyISAM 引擎不支持行锁。

两阶段锁协议：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。 如影院购票，假设有用户余额扣除票价，影院账户余额增加票价，记录日志操作。在事务中，我们就可以把容易冲突的影院账户余额增加票价安排到最后，这样影院账户余额这一行锁时间就最小，最大程度减少了事务的锁等待，提升并发度。

一、读锁 / 共享锁

读锁，也称共享锁Shared Locks，简称S锁

select * from table where id = 1 lock in share mode;

• 若事务 1 对数据对象A加上读锁，事务 1 可以读A也可以修改A，其他事务可以对A加读锁，不能加写锁。

二、写锁 / 排他锁

写锁，也称排他锁Exclusive Locks，简称X锁

select * from table where id = 1 for update;

• 若事务 1 对数据对象A加上写锁，事务 1 可以读A也可以修改A，其他事务不能再对A加任何锁。

• update,delete,insert会自动给涉及到的数据加排他锁

• select * from t where d = 5 for update;会给所有行数据N条记录加排他锁，并且加入了N+1个间隙锁，确保无法插入新记录。（注意：事务1加入间隙锁，锁住的是其他事务往该间隙插入数据的操作）

间隙锁

在Mysql中存在幻读，即事务A两次相同查询，第二次查到了其他事务新增的记录。RR无法解决幻读问题。行锁只能锁住行，而插入操作更新的是记录之间的间隙，所以Innodb引入了间隙锁Gap Lock解决幻读问题。

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

假设数据表如上，初始后存在6个记录，7个间隙（开区间）。执行select * from t where d = 5 for update时候，不止给表已有6个记录加行锁，同时增加了7个间隙锁，这样确保无法插入新的记录。

Next-key Lock

间隙锁和行锁合称next-key lock，前开后闭区间。

执行select * from t for update把整个表记录锁起来，形成了7个next-key lock，(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]

注：间隙锁+插入操作， 在并发情况下可能会出现互相等待间隙锁，产生死锁问题。

参考： 《mysql实战45讲》丁奇