你真的了解MySQL中的锁吗

来源：左耳君（ID:qaqzuoer）

作者： Captain

大家在学习mysql的时候，估计也听说过数据库中的锁

锁，大家应该是很熟悉的了吧，就是多个线程同时对共享资源的访问的竞争

那么大家对于mysql中的锁又有多少了解呢

先说一下mysql中的几种语言，SQL语言共分为四大类

数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL：数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE

2 .数据操纵语言DML：数据操纵语言DML主要有三种形式，插入，更新，删除。

3. 数据定义语言DDL：数据定义语言DDL用来创建数据库中的各种对象如：表 视图 索引 同义词 簇。DDL操作是隐性提交的，不能rollback

4. 数据控制语言DCL：数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

锁

锁是并发访问同一个共享资源时的同步机制，Synchronized、ReentrantLock和ReentrantReadWriteLock都用过的吧，都是一样的道理

MySQL的锁是在服务器层或者存储引擎层实现的，保证数据访问的一致性和有序性

按模式分类为：乐观锁与悲观锁。

按粒度分可以分为全局锁、表级锁、页级锁、行级锁。

按属性可以分为：共享锁、排它锁。

按状态分为：意向共享锁、意向排它锁。

按算法分为：间隙锁、临键锁、记录锁。

乐观锁

一种思想，乐观锁建设数据一般情况下不会产生冲突，在数据的操作过程中不会对数据做任何锁定，只有当数据进行提交跟新的时候，才会正式对数据的冲突与否进行检测

如果发生冲突了，则返回错误，调用者决定如何操作，是回滚还是重试

比较适用于读多写少的情况，如果写场景比较多，写冲突的可能性比较高，可能需要不断重试，这样会大大降低系统性能

这种可以通过增加一个数据版本字段Version来实现，读取数据的时候把这个字段一起读出来，数据每更新一次，对Version字段加一，当我们提交更新数据的时候，判断数据库表中的对应记录的版本信息和第一次取出来的Version是否一致，一致则可以更新，不一致则过期

悲观锁

这个也是一种思想，悲观的看法，认为每次去取数据的时候都会有别人去修改，所以在整个数据处理过程中，数据处于锁定状态

适用于并发量不大，写入操作比较频繁，数据一致性比较高的场景，MySQL中，共享锁和排他锁都是属于悲观锁的不同实现

全局锁

对整个数据库实例进行加锁，一般用于全库的逻辑备份

MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。使用这个命令之后，整个库处于只读状态，其它线程的更新语句都会被阻塞

主库备份，需要考虑影响业务系统，从库备份，在备份期间不能执行主库同步过来的binlog，主从同步会有延迟

解决办法，mysqldump使用参数--single-transaction，启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

表级锁

对操作的数据表加锁，MyISAM和InnoDB引擎都支持表级锁定，这里分为两种，一种是表锁，一种是元数据锁，即meat data lock，MDL锁

lock tables 表名 read #该表可以读，不能ddl 和 dml 中增删改，只能读取表数据
lock tables 表名 write # 既不能读，也不能写

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

MDL锁

防止DDL和DML并发的冲突，你想啊，一个查询正在遍历表中数据，而执行期间另一个线程对这个表结构进行了变更，删除了一列，查询线程拿到的结果跟表结构对不上，就乱套了

MDL锁不是显示的，MDL锁是在5.5版本引入的

对一个表做增删改查操作的时候，加MDL读锁，读锁之间不冲突，所以多个线程可以同时对一个表进行增删改查

当要对表结构变更操作的时候，加MDL写锁，读锁和写锁、写锁和写锁都是冲突的，用来保证变更结构操作的安全性

两个线程同时对一个表中加字段，其中一个要等另一个执行完才可以开始。一个线程A先在查询数据，另一个线程B想要加一列数据，需要等A线程执行完才可以执行线程B，就解决了上面的问题

MDL锁是系统默认加的，我们理解了上面的机制之后，一定要注意MDL写锁之后的读锁和写锁都会阻塞，所以在给一些表加字段的时候一定要注意，尽量避开业务系统比较繁忙的时候

即使小表，操作不慎，如果一个表的查询语句频繁，而且客户端有重试机制，也就是超时之后还会再起一个新session，库的线程很容易就慢了，这时系统就崩了

千万不要在长事务中对表结构进行修改，事务不提交会一直占用MDL写锁，那后面的语句就需要一直等待

页级锁

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁。

行级锁

MySQL中只有InnoDB支持行级锁，行级锁分为共享锁和排他锁。

行级锁是粒度最低的锁，锁冲突概率最低。但加锁慢、开销大，容易发生死锁现象。

行锁并不是直接锁记录，而是锁索引

索引分为主键索引和非主键索引，一条sql语句操作了主键索引，MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引

共享锁

共享锁，也就是我们常说的读锁，一个事务对数据加上读锁之后，其它事务只能对该数据加读锁，不能做任何修改，不能加写锁

这样可以更好的支持并发中的读取数据，读取数据的时候，不允许其他事物对当前数据进行修改操作，从而避免不可重复读的问题的出现

select … lock in share mode

排它锁

排它锁，也就是写锁，当对数据加上写锁之后，其它事务不能对该数据读写，这个时候读锁和写锁都不可以加了，也就是全部阻塞了

写锁就是为了解决在数据修改的时候，不允许其它事务对当前数据进行修改和读取操作，从而可以避免脏读问题的产生

共享锁可以避免不可重复读的问题，排它锁可以避免脏读问题的产生

意向共享锁和意向排它锁

意向锁的出现就是为了协调表锁和行锁，支持多粒度的并存

事务A有行锁的时候，MySQL会自动给该表加上意向锁，事务B如果想申请整个表的写锁，就不用遍历去每一行判断是否存在行锁，只需要判断是否存在意向锁，即可决定是否可以加表的写锁

意向锁的互斥性

当然，表格中的共享锁和排他锁都是表锁，即表锁和意向锁的关系

意向锁是不会和行级的共享排他锁互斥的

给大家再解释一下，就是有行级共享锁，那就加上意向共享锁，当需要加表级的共享锁的时候，兼容，即行读表读共存;相反，表级的排他锁加不上，也就是行读表写不共存

相应的行级的排他锁，也就是写锁加上之后，表级的读锁和写锁都是不能加上的了，也就是行写表既不可读也不可写

总结

行读表读共存

行读表写不共存

行写既不可读也不可写

记录锁

记录锁是封锁记录，记录锁也叫行锁

间隙锁

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据

临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，是一个左开右闭区间。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列(包括主键列)上不存在临键锁。