mysql45讲学习总结（四）—锁
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。
DML：数据操纵语言(Data Manipulation Language)DDL：数据定义语言(Data Definition Language)MDL：元数据锁(meta data lock)
全局锁
全局锁顾名思义就是对整个数据库实例上锁，让整个数据库处于只读状态，MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock(FTWRL)。使用这个命令之后，其他线程的数据更新语句DML(增删改)、数据定义语句DDL(建表、修改字段信息)和更新类事务的提交语句。
一般来说全局锁的使用场景就是全库的备份，使用FTWRL命令之后，使得整个库处于只读状态之后，进行数据备份。那么有两种情况，如果在主库进行备份，备份期间，主库处于业务停摆；如果在从库备份，从库无法执行主库过来的binlog，导致主从延迟。
那么有没有什么方式可以不影响业务？
先简单还原全库备份如果不上全局锁产生的问题：假设有两张表，一张账户余额表，一张用户课程表；事务内的操作顺序为余额表扣款，课程表添加一条课程记录。假设这个动作是在备份过程中执行，在余额表扣款之前，备份了余额表，在课程表添加记录之后，备份了课程表，导致在备份库中，余额表的金额是原值，课程表却新增了一条记录。从语义上简单说就是钱没有花，但是买到了课。
产生这个问题的原因主要是备份库得到的不是一个逻辑时间点，视图的逻辑是不一致的，所以备份的时候应该要拿到一个时间点的一致性视图。也就是在可重复读隔离级别看开启一个事务，那么在这个事务中的操作，对于每个表来说已经形成了”快照“，对这个”快照“进行备份，从而保证备份库数据逻辑正确。
官方自带的逻辑备份工具mysqldump，使用参数--single-transaction后备份数据会启动一个事务，来确保拿到的是一致性视图，但是要注意，这个参数需要存储引擎支持可重复读这个隔离级别。比如MyISAM不支持，那么只能使用FTWRL进行备份。
当然了还有人说可以使用set global readonly = true的方式让整个库处于只读的状态，但是这种做法存在两个致命的问题：
- 使用
FTWRL命令，当这个线程因为发生异常断开之后，会释放这个全局锁。 - 假设其他应用层框架通过这个
readonly全局参数判断是否是主库，导致一些应用层逻辑出现问题。
业务的更新不只是增删改数据（DML)，还有可能是加字段等修改表结构的操作（DDL）。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。
表锁
MySQL里面的表级别锁有两种：表锁和元数据锁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都不允许，自然不能访问其他表。
一般不使用lock tables来控制并发，这个锁的面积太大。
元数据锁
MDL不需要显示使用，在访问一个表的时候会自动加上，它的作用是保证读写的正确性，这是因为假设一个查询正在遍历表数据，突然另一个线程变更了表结构，那么查询线程获取到的数据结构跟原本对不上。
所以当对一个表做增删改操作时，加DML读锁，当要对表结构做变更时，加DML写锁。
- 读锁之间不互斥，因此你可以多个线程同时对一张表增删改查。
- 读写锁之间、写锁之间是互斥的，一个线程获取了读锁，另一个线程如果要获取写锁就需要进入等待。
尽管这样还是可能会出现一些问题：
在给一个表添加字段、修改字段或者索引的处理，这些操作是需要全表扫描的。假设线程A获取了DML读锁，在对表进行查询操作，线程B想要获取DML写锁，进行修改表字段操作，这时由于线程A持有DML读锁，线程B阻塞进入等待，同时其他线程若想再查这个表数据，需要获取DML读锁，都会因为线程B而进入等待，如果这个表查询比较频繁，且很多客户端是有重试机制，也就是等待超时之后会重启一个session再次请求，那么这个库的线程很快就会满。事务中的DML锁，在语句执行开始时申请，并不是在语句执行完成后就释放，而是在事务提交之后才释放。
所以一般情况下，首先要解决长事务，事务不提交会一直持有DML锁，或者将这个线程kill掉，但是对于热点表来说，可能kill不是那么有效果，可能刚kill掉，请求马上就又来了，所以只能通过为这个变更语句设置等待时间，如果在这个时间之内能获取到DML锁，就执行变更，如果获取不到，也不要阻塞业务，先放弃，之后在重复执行这个命令。
ALTER TABLE tbl_name NOWAIT add column …
ALTER TABLE tbl_name WAIT N add column …
行锁
MySQL的行锁是各个引擎层自己实现，并不是所有存储引擎都支持行锁，比如MyISAM就不支持行锁，也就是锁它的并发控制只能使用表锁，这种颗粒度严重影响了业务并发度。
行锁就是针对于数据表中行记录的锁，锁的是主键索引，比如事务A更新了一行，这时候事务B也要更新同一行，那么事务B进入等待，如果事务B更新的不是同一行，那么事务B无需等待。
两阶段锁
| 事务A | 事务B |
|---|---|
| begin; update t set k=k+1 where id=1; update t set k= k+1 where id=2; |
|
| Begin; update t set k=k+2 wehre id=1; |
|
| commit; |
这种情况下，事务A在执行两条更新语句之后，持有了这两条记录的行锁，但是事务A是在提交之后才会释放行锁，所以事务B的更新语句，要在事务A提交之后才能执行。
在InnoDB事务中，行锁是在执行语句的时候获取的，但并不是语句执行之后就释放，而是在这个事务提交之后才释放，这就是两阶段协议。
间隙锁
间隙锁是用来解决幻读带来的问题，幻读是指事务T1对表中的数据进行了条件判断修改，比如set k=1 where k=2，同时事务T2向表中插入一条k=1的数据，那么事务T1再次查询时会发现，存在未修改行。
创建一个表，这个表除了主键 id 外，还有一个索引 c，初始化语句在表中插入了 6 行数据。
CREATE TABLE
t(idint(11) NOT NULL,cint(11) DEFAULT NULL,dint(11) DEFAULT NULL,
PRIMARY KEY (id),
KEYc(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);
| sessionA | sessionB |
|---|---|
| select * from where d=5 for update | |
| insert into t value(1, 1, 5) | |
| select * from where d=5 for update | |
| commit; |
sessionA第一次查询的时候只返回一条记录，第二次查询的时候由于sessionB已经提交了，所以可以查到两条记录。
也就是说幻读指一个事务内的两次查询，后一次的查询看到了前一次查询没有看到的行。
仔细观察sessionA的查询语句，可以发现，for update命令加上之后，是当前读，当前读就是要读到已经提交的最新值，所以sessionA看到sessionB提交的结果看起来并没有什么问题。
但是，实际上这是有问题的，因为sessionA的第一个查询语句的语义是要锁住所有d=5的行，不允许其他事务进行写操作，如果将sessionB的语句修改为update c=1 where d=5，这时sessionB就会进入等待，等待sessionA提交之后释放锁。
锁的设计是为了在并发时保持数据的一致性，包括数据库内部数据状态的一致性，也包括了日志的一致性。
看下面的例子：
| sessionA | sessionB | sessionC | |
|---|---|---|---|
| begin; select * from where d=5 for update; update t set d=100 where d=5; |
T1 | ||
| update t set d=5 where id=0; update t set c=5 where id = 0; |
T2 | ||
| select * from where d=5 for update; | T3 | ||
| insert into t values(1,1,5); update set c=5 where id=1; |
T4 | ||
| select * from where d=5 for update; | T5 | ||
| commit; | T6 |
当执行完成之后数据库数据结果：
- 经过T1之后，id=5这一行变成(5, 5, 100)，但是这个要在T6之后才正式提交。
- 经过T2之后，id=0这一行变成(0, 5, 5)
- 经过T4之后，表里对了一行(1, 5, 5)
所以binlog中的语句大概是：
1 | update t set d=5 where id=0; /*(0,0,5)*/ |
如果使用这个binlog来备份或者是从库同步数据都会出现数据不一致的情况。
那么是什么导致了这个数据不一致？可以简单的认为是sessionA在T1时未锁住数据导致的，那么如果它在T1时刻锁住了所有扫描到的行又会怎么样？
1 | insert into t values(1,1,5); /*(1,1,5)*/ |
可以看到，就算锁住了所有扫描行，阻塞了sessionB的执行，但是也无法锁住sessionC语句的执行，换句话说就是即使是所有数据都上锁，也还是无法阻止新记录的插入。这也就是为什么幻读需要单独拿出来说的原因。
所以产生幻读的原因是行锁只能锁住数据行，而新插入的数据是插入到数据行之间的间隙，因此为了解决幻读，InnoDB引入了间隙锁(Gap Lock)。
间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，在主键索引上这就产生了 7 个间隙。
所以当执行select * from t where d=5 for update时，不止是给数据库中已有的6个记录上锁，同时也加上了7个间隙锁。这样就可以确保数据无法插入。也就是说在扫描行时不仅给行上锁，也给行两边的间隙上锁。
间隙锁的性质与之前碰到的锁性质有所不同。
比如行锁分为读写锁，读锁之间不冲突，读写、写写之间是冲突。而间隙锁，两个session可以同时对一个间隙上间隙锁，只要不插入数据就不会产生冲突。
| sessionA | sessionB |
|---|---|
| begin select * from t where c=7 lock in share mode; |
|
| begin select * from t where c=7 lock in share mode; |
|
| commit; |
这里的sessionB并不会阻塞，因为表中没有c=7这条记录，对于sessionB来说也是对(5,10)这个范围加间隙锁，它也sessionA锁住的范围是一致的，目标也是一致的，都是为了保护(5,10)这个范围不能插入数据。
间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。
当然了间隙锁的引入也会带来一些问题。
| sessionA | sessionB |
|---|---|
| select * from where id=5 for update; | |
| select * from where id=5 for update; insert into t values(5,1,5); |
|
| insert into t values(5,1,5); |
这个会带来死锁，原因是sessionA为(5,10)加上了间隙锁，sessionB也为(5, 10)加上了间隙锁，但是sessionB的插入语句需要等待sessionA释放间隙锁，sessionA的插入语句也在等待sessionB释放间隙锁，从而造成死锁。
间隙锁实际上是可重复读的隔离级别才会出现，如果隔离级别设置为读提交就不会产生幻读，也就不需要引入间隙锁。在读提交下，一般将日志格式设置为row来解决数据不一致的问题。
小结
- 行锁：对索引记录加锁。
- 间隙锁：锁住某个区间。(可以是两个索引记录之间，也可以是第一个索引之前或者最后一个索引之后的空间)
- next-key锁：行锁和间隙锁的组合。
如果检索条件不是索引的话会全表扫描，这时是表级锁。
案例
如果需要删除一个表里前10000行数据，有一下三种方法可以做到：
- 直接执行
delete from T limit 10000：单个语句占用时间过长，锁的时间比较长，而且大事务还会导致主从延迟。 - 在一个连接中循环执行20次
delete from T limit 500：一般推荐这种方式。 - 在20个连接中同时执行
delete from T limit 500：人为造成锁冲突。但是如果可以加上特定的条件，将这10000天然的分开，或者可以获取到主键ID，那么这种方式也不错。