MySQL锁介绍

该文章是对极客时间专栏《MySQL实战45讲》相关部分的总结

全局锁

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

全局锁的典型应用是做全库逻辑备份。注意，在备份过程中整个库完全处于只读状态

用unlock tables 主动释放锁，也可以在客户端断开的时候自动释放

表级锁

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

表锁

表锁的语法是 lock tables T read/write可以用 unlock tables主动释放锁。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

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

注意：表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有 lock tables 这样的语句，你需要追查一下

元数据锁（MDL锁）

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性.

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

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

需要注意的是：事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

注意这个问题，如果SessionA查询表t，对表t加了MDL读锁，这时候SessionB通过alter语句对表t做ddl操作，需要加MDL写锁，由于SessionA还没提交，MDL表锁还没释放，所以SessionB会block，如果sessionA迟迟不提交，那么SessionB就会一直处于block状态，这样就会导致后续对表t的查询语句都block，等于这个表现在完全不可读写了。
基于上面的原因，我们在做修改表结构的操作的时候，要格外小心。

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

但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

mysql 5.6之后支持online ddl，过程大致如下：
1、拿MDL写锁
2、DDL执行准备
3、降级成MDL读锁
4、DDL核心执行（耗时最多的）
5、升级成MDL写锁
6、DDL最终提交
7、释放MDL锁

行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁

锁就是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。因此：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

共享锁和排它锁

共享锁 又称读锁（S锁）,就是多个事务只能读数据不能改数据。

排他锁 又称写锁（X锁），指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。

mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型。
如果加排他锁可以使用select …for update 语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

next-key lock

首先，说下什么是幻读。幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

• 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

• 事务sessionB的修改结果，被事务sessionA之后的select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

即使把所有的记录都加上锁，还是阻止不了新插入的记录，幻读也就无法解决。

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

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

跟行锁有冲突关系的是“另外一个行锁”。跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

间隙锁和 next-key lock 的引入，解决了幻读的问题，但同时可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的

间隙锁是在可重复读隔离级别下才会生效的。如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row

在可重复读的隔离级别下，加锁规则。
加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

• 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

需要注意的是，

1. 加锁是在索引上加的。如果查询使用了lock in share mode并使用到了覆盖索引，那么只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。
2. 锁是一个个加的，扫到哪条记录加哪条

参考链接：
全局锁和表锁 ：给表加个字段怎么有这么多阻碍？
行锁功过：怎么减少行锁对性能的影响？
为什么我只改一行的语句，锁这么多？