MySQL：锁机制和隔离事务级别

 

在mysql中的锁看起来是很复杂的，因为有一大堆的东西和名词：排它锁，共享锁，表锁，页锁，间隙锁，意向排它锁，意向共享锁，行锁，读锁，写锁，乐观锁，悲观锁，死锁。这些名词有的博客又直接写锁的英文的简写--->X锁，S锁，IS锁，IX锁，MMVC...

1.1 为什么需要学习数据库锁知识

即使我们不会这些锁知识，我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了

• 对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)
• MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁

只会在某些特定的场景下才需要手动加锁，学习数据库锁知识就是为了:

• 能让我们在特定的场景下派得上用场
• 更好把控自己写的程序
• 在跟别人聊数据库技术的时候可以搭上几句话
• 构建自己的知识库体系！在面试的时候不虚

2.2表锁简单介绍

首先，从锁的粒度，我们可以分成两大类：

• 表锁
  • 开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
• 行锁
  • 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

不同的存储引擎支持的锁粒度是不一样的：

• InnoDB行锁和表锁都支持！
• MyISAM只支持表锁！

InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表锁

• 也就是说，InnoDB的行锁是基于索引的！

表锁下又分为两种模式：

• 表读锁（Table Read Lock）
• 表写锁（Table Write Lock）
• 从下图可以清晰看到，在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞！
  • 读读不阻塞：当前用户在读数据，其他的用户也在读数据，不会加锁
  • 读写阻塞：当前用户在读数据，其他的用户不能修改当前用户读的数据，会加锁！
  • 写写阻塞：当前用户在修改数据，其他的用户不能修改当前用户正在修改的数据，会加锁！
  • 写锁和其他锁均布兼容，只有读和读之间兼容

 

从上面已经看到了：读锁和写锁是互斥的，读写操作是串行。

• 如果某个进程想要获取读锁，同时另外一个进程想要获取写锁。在mysql里边，写锁是优先于读锁的！
• 写锁和读锁优先级的问题是可以通过参数调节的：max_write_lock_count和low-priority-updates

值得注意的是：

The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 8.11.3, “Concurrent Inserts”.) However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ

• MyISAM可以支持查询和插入操作的并发进行。可以通过系统变量concurrent_insert来指定哪种模式，在MyISAM中它默认是：如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。
• 但是InnoDB存储引擎是不支持的！

参考资料：

• dev.mysql.com/doc/refman/…--官方手册
• ourmysql.com/archives/56…---几个参数说明

2.2行锁细讲

上边简单讲解了表锁的相关知识，我们使用Mysql一般是使用InnoDB存储引擎的。InnoDB和MyISAM有两个本质的区别：

• InnoDB支持行锁
• InnoDB支持事务

从上面也说了：我们是很少手动加表锁的。表锁对我们程序员来说几乎是透明的，即使InnoDB不走索引，加的表锁也是自动的！

我们应该更加关注行锁的内容，因为InnoDB一大特性就是支持行锁！

InnoDB实现了以下两种类型的行锁。

• 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  • 也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。
• 排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
  • 也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。

看完上面的有没有发现，在一开始所说的：X锁，S锁，读锁，写锁，共享锁，排它锁其实总共就两个锁，只不过它们有多个名字罢了~~~

Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
• 意向锁也是数据库隐式帮我们做了，不需要程序员操心！

参考资料：

• www.zhihu.com/question/51…
• dev.mysql.com/doc/refman/…

2.2.1MVCC和事务的隔离级别

数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别

MVCC(Multi-Version Concurrency Control)多版本并发控制，可以简单地认为：MVCC就是行级锁的一个变种(升级版)。

• 事务的隔离级别就是通过锁的机制来实现，只不过隐藏了加锁细节

在表锁中我们读写是阻塞的，基于提升并发性能的考虑，MVCC一般读写是不阻塞的(所以说MVCC很多情况下避免了加锁的操作)

• MVCC实现的读写不阻塞正如其名：多版本并发控制--->通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。

快照有两个级别：

• 语句级
  • 针对于Read committed隔离级别
• 事务级别
  • 针对于Repeatable read隔离级别

我们在初学的时候已经知道，事务的隔离级别有4种：

• Read uncommitted
  • 会出现脏读，不可重复读，幻读
• Read committed
  • 会出现不可重复读，幻读
• Repeatable read
  • 会出现幻读(但在Mysql实现的Repeatable read配合gap锁不会出现幻读！)
• Serializable
  • 串行，避免以上的情况！

---

Read uncommitted会出现的现象--->脏读：一个事务读取到另外一个事务未提交的数据

• 例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。
• 出现脏读的本质就是因为操作(修改)完该数据就立马释放掉锁，导致读的数据就变成了无用的或者是错误的数据。

---

Read committed避免脏读的做法其实很简单：

• 就是把释放锁的位置调整到事务提交之后，此时在事务提交前，其他进程是无法对该行数据进行读取的，包括任何操作

但Read committed出现的现象--->不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改

• 注：A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样【危害：A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了】

---

上面也说了，Read committed是语句级别的快照！每次读取的都是当前最新的版本！

Repeatable read避免不可重复读是事务级别的快照！每次读取的都是当前事务的版本，即使被修改了，也只会读取当前事务版本的数据。

呃...如果还是不太清楚，我们来看看InnoDB的MVCC是怎么样的吧(摘抄《高性能MySQL》)

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现的，这两个列一个保存了行的创建时间，一个保存了行的过期（删除）时间。当然存储的并不是真正的时间值，而是系统版本号。每开始一个新的事务，系统版本号会自动递增，事务开始的时候的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

 select

　　InnoDB 会根据以下两个条件检查每行记录：

　　　　a. InnoDB 只查找版本早于当前事务版本的数据行，这样可以确保事务读取到的数据，要么是在事务开始前就存在的，要么是事务自身插入或更新的

　　　　b. 行的删除版本要么未定义要么大于当前事务版本号，确保了事务读取到的行，在事务开始前未被删除

---

 

至于虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

• 注：和不可重复读类似，但虚读(幻读)会读到其他事务的插入的数据，导致前后读取不一致
• MySQL的Repeatable read隔离级别加上GAP间隙锁已经处理了幻读了。

参考资料：

• www.jianshu.com/p/cb97f76a9…
• www.zhihu.com/question/26…

扩展阅读：

• www.zhihu.com/question/67…

1.3乐观锁和悲观锁

无论是Read committed还是Repeatable read隔离级别，都是为了解决读写冲突的问题。

单纯在Repeatable read隔离级别下我们来考虑一个问题：

 

此时，用户李四的操作就丢失掉了：

• 丢失更新：一个事务的更新覆盖了其它事务的更新结果。

(ps:暂时没有想到比较好的例子来说明更新丢失的问题，虽然上面的例子也是更新丢失，但一定程度上是可接受的..不知道有没有人能想到不可接受的更新丢失例子呢...)

解决的方法：

• 使用Serializable隔离级别，事务是串行执行的！
• 乐观锁
• 悲观锁

1. 乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁，等到更新的时候再判断是否可以更新。
2. 悲观锁是数据库层面加锁，都会阻塞去等待锁。

2.3.1悲观锁

所以，按照上面的例子。我们使用悲观锁的话其实很简单(手动加行锁就行了)：

• select * from xxxx for update

在select 语句后边加了 for update相当于加了排它锁(写锁)，加了写锁以后，其他的事务就不能对它修改了！需要等待当前事务修改完之后才可以修改.

• 也就是说，如果张三使用select ... for update，李四就无法对该条记录修改了~

2.3.2乐观锁

乐观锁不是数据库层面上的锁，是需要自己手动去加的锁。一般我们添加一个版本字段来实现：

具体过程是这样的：

张三select * from table --->会查询出记录出来，同时会有一个version字段

李四select * from table --->会查询出记录出来，同时会有一个version字 

李四对这条记录做修改：update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}，判断之前查询到的version与现在的数据的version进行比较，同时会更新version字段

此时数据库记录如下：

 

张三也对这条记录修改：update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}，但失败了！因为当前数据库中的版本跟查询出来的版本不一致！

 

 

参考资料：

• zhuanlan.zhihu.com/p/31537871---什么是悲观锁和乐观锁
• www.zhihu.com/question/27…---乐观锁和 MVCC 的区别？

2.4间隙锁GAP

当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

值得注意的是：间隙锁只会在Repeatable read隔离级别下使用~

例子：假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101

Select * from  emp where empid > 100 for update;

复制代码

上面是一个范围查询，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的有两个：

• 为了防止幻读(上面也说了，Repeatable read隔离级别下再通过GAP锁即可避免了幻读)
• 满足恢复和复制的需要
  • MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读

2.5死锁

并发的问题就少不了死锁，在MySQL中同样会存在死锁的问题。

但一般来说MySQL通过回滚帮我们解决了不少死锁的问题了，但死锁是无法完全避免的，可以通过以下的经验参考，来尽可能少遇到死锁：

• 1）以固定的顺序访问表和行。比如对两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；将两个事务的sql顺序调整为一致，也能避免死锁。
• 2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
• 3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
• 4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
• 5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

参考资料：

• hedengcheng.com/?p=771#_Toc…
• www.cnblogs.com/LBSer/p/518…

行锁的三种算法：

1.Record Lock：单个行记录上的锁；

2、Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身；

3.Next-key Lock：Gap Lock+Record Lock 锁定一个范围，并且锁定记录本身；

默认使用 next-key Lock 技术。

next-key lock 只有在查询的列是唯一索引的情况下，才会降级为 record lock。即聚集索引上加的是 record lock'，而辅助索引上加的是 next-key lock

对于唯一键值的锁定，next-key record 会降级为 record-key 仅存在于查询所有的唯一索引列。如果唯一索引列有多个列组成，仅查询其中的一列，并不会降级，仍然是 next-key record

 

Gap lock 的作用是为了阻止多个事务将记录插入到同一个范围中，而这会导致 Phantom Problem 的问题产生。

Read Committed 除了外键约束和唯一性检查需要Gap lock，其他均使用 record lock。但是破坏了 ACID的 I，对于 replication ，可能会导致主从不一致。

next-key record 如果锁定了表中辅助索引的某个记录，即表示它已经锁定了 该记录所在的某个范围，访问索引中该范围的记录仍然会被阻塞。

Phantom Problem幻读：同一事务下，连续两次执行同样的 SQL语句会导致不同的结果。（个人理解：重复读读取的是事务开始前的快照，可提交读读的是最新的快照信息。）

锁问题：

通过锁机制可以实现事务的隔离性要求，但是并发性可能会带来潜在的问题，以下一一介绍：

1. 脏读：

   脏页：在缓冲池里已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页与磁盘中的页的数据是不一致的。当然在刷新到磁盘之前，日志都已经被写入到了重做日志文件中。

   脏数据：事务对缓存池中行记录的修改，并且还没有被提交。

   脏页是因为数据库实例内存和磁盘的异步造成的，不影响数据的一致性，但最终会达到一致性，即脏页都会刷到磁盘。脏数据指的是一个事务可以读到另一个事务中未提交的数据，显然违反了数据库的隔离性。

   ReadCommitted 会出现该问题，InnoDB默认为重复读的隔离级别。但是Microsoft SQL 和 Oracle 都是可提交读。当查询不需要特别精确的时候可以设置为可提交读。

2. 不可重复读：

   不可重复读指的是一个事务内多次读取同一个数据集合。在该事务还没有结束之前，另外一个事务对该数据集合进行了 DML 操作，因此，第一个事务的两次读取数据之间，第二个事务对其进行了修改，name两次读取的数据可能是不一样的，称为不可重复读。

   与脏读的区别：脏读是读取未提交的数据，但重复读是已经提交的数据，违反了数据库的一致性原则。

   read Committed 允许不可重复读，InnoDB 使用 next-key lock 来解决该问题，在该算法下，锁住的不仅仅是扫描到的key，而是索引覆盖的范围，因此该范围的插入是不允许的。InnoDB默认为 READ REPEATABLE

3. 丢失更新

   一个事务的更新操作会被另一个事务的更新操作覆盖，从而导致数据的不一致问题。

   串行化，排他锁X

死锁：

死锁是指两个或两个以上的事务在执行过程中，因争夺资源造成的一种互相等待的现象

解决方法：

1. 不要有等待，将等待化为回滚，并事务重新开始。并发性能下降

2. 超时回滚，FIFO顺序进行，若回滚的事务权重较大，耗时长。解决办法：

   1. wait-for-graph:等待图，等待图中包含锁的信息链表，事务等待链表。并构造一张图，如果存在回路，说明存在死锁问题，因此资源间相互等待。主动的死锁检测机制。

死锁概率：

死锁概率与以下几点有关系：

1. 系统中事务的数量：数量越多，概率越大

2. 每个事务操作的数量（r)，每个事务操作的数量越多，发生死锁的概率越大

3. 操作数据的集合（R）,越小，发生死锁概率越大

公式：n2∗r4/4∗R2,nr<<R，因此死锁概率还是很低的。

InnoDB在发生死锁时，会进行回滚，释放原来的持有锁。InnoDB存储引擎会自动在外键上建立索引，避免死锁，且这个列是不允许人为删除的’

2.6锁总结

上面说了一大堆关于MySQL数据库锁的东西，现在来简单总结一下。

表锁其实我们程序员是很少关心它的：

• 在MyISAM存储引擎中，当执行SQL语句的时候是自动加的。
• 在InnoDB存储引擎中，如果没有使用索引，表锁也是自动加的。

现在我们大多数使用MySQL都是使用InnoDB，InnoDB支持行锁：

• 共享锁--读锁--S锁
• 排它锁--写锁--X锁

在默认的情况下，select是不加任何行锁的~事务可以通过以下语句显示给记录集加共享锁或排他锁。

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
• 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

InnoDB基于行锁还实现了MVCC多版本并发控制，MVCC在隔离级别下的Read committed和Repeatable read下工作。MVCC能够实现读写不阻塞！

InnoDB实现的Repeatable read隔离级别配合GAP间隙锁已经避免了幻读！

• 乐观锁其实是一种思想，正如其名：认为不会锁定的情况下去更新数据，如果发现不对劲，才不更新(回滚)。在数据库中往往添加一个version字段来实现。
• 悲观锁用的就是数据库的行锁，认为数据库会发生并发冲突，直接上来就把数据锁住，其他事务不能修改，直至提交了当前事务

参考资料：

• zhuanlan.zhihu.com/p/29150809--Mysql锁总结
• blog.csdn.net/mysteryhaoh…--MySQL学习之——锁(行锁、表锁、页锁、乐观锁、悲观锁等)
• segmentfault.com/a/119000001…--MySQL InnoDB引擎锁的总结

三、总结

本文主要介绍了数据库中的两个比较重要的知识点：索引和锁。他俩可以说息息相关的，锁会涉及到很多关于索引的知识~

我个人比较重视对整体知识点的把控，一些细节的地方可能就没有去编写了。在每一个知识点下都会有很多的内容，有兴趣的同学可以在我给出的链接中继续阅读学习。当然了，如果有比较好的文章和资料也不妨在评论区分享一下哈~

我只是在学习的过程中，把自己遇到的问题写出来，整理出来，希望可以对大家有帮助。如果文章有错的地方，希望大家可以在评论区指正，一起学习交流~

参考资料：

• 《高性能MySQL 第三版》