Minimalist Life

理解MySQL中的锁

2018/04/11 Database

事务ACID

  • 原子性(Atomicity): 事务是数据库的逻辑工作单位，事务中包含的各个操作要么都做，要么都不做。
  • 一致性(Consistency): 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
  • 隔离性(Isolation): 一个事务的执行不能被它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
  • 持久性(Durability): 也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

事务的两个阶段

数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）。

  • 加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
  • 解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。
事务	加锁/解锁处理
begin；
insert into test .....	加insert对应的锁
update test set...	加update对应的锁
delete from test ....	加delete对应的锁
commit;	事务提交时，同时释放insert、update、delete对应的锁

事务的四种隔离级别

隔离级别	                 脏读（Dirty Read）  不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	 可能	               可能	                            可能
已提交读（Read committed）	   可能	               可能	                            可能
可重复读（Repeatable read）	   可能	               可能	                            可能
可串行化（Serializable ）	     可能	               可能	                            可能
  • 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
  • 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
  • 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
  • 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

提交读 Read Committed（不可重复读）

数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。事务A，B。事务A查询得到数据，事务B修改update或delete了这部分数据，事务A内再查询这部分数据，在事务A提交前，两次相同条件查询读取到的数据是不一样的，这就是不可重复读。

可重复读 Repeated Read

事务A，B。事务A查询得到数据，事务B修改update了这部分数据，事务A内再查询这部分数据，在事务A提交前，两次相同条件查询读取到的数据是一样的，这就可重复读。但是，如果事务B是insert了记录，事务A相同条件的查询可能读取到了更多记录的数据，第一次查询的时候没有这些数据，这就是幻读。

不可重复读和幻读的区别

不可重复读重点在于update和delete，而幻读的重点在于insert。 如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。 所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。 上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题

悲观锁和乐观锁

  • 悲观锁: 是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据)。在悲观锁下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其他事务无法修改这些数据。修改删除数据有要加锁，其他事务无法读取这些数据。悲观锁的并发性能很差。

  • 乐观锁: 乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。乐观锁机制在一定程度上解决了这个问题。乐观锁大多是基于数据版本(Version)记录机制实现。数据表增加一个版本标识，一般是通过为数据库表增加一个 version字段来实现。读取数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，提交数据的版本数据与数据库表对应记录的当前版本信息进行对比，如果提交的数据版本号大于等于数据库表该记录当前版本号，则提交成功，予以更新，否则认为是过期数据，已经有其他事务更新过该数据了。

MVCC在MySQL的InnoDB中的实现

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。 在可重读Repeatable reads事务隔离级别下：

  • SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
  • INSERT时，保存当前事务版本号为行的创建版本号
  • DELETE时，保存当前事务版本号为行的删除版本号
  • UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行。

  • 在MySQL的RR级别中，是解决了幻读的读问题的

两种读

  • 快照读(snapshot read): 读取的是历史数据
  • 当前读(current read): 读取数据库当前版本的数据

在MVCC中:

快照读
select * from table ....;

当前读
select * from table where ? lock in share mode;
select * from table where ? for update;
insert ;
update ;
delete ;

MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”，就需要另外的模块来解决了。

Next-Key锁

Next-Key锁是行锁和GAP（间隙锁）的合并。

在MySQL InnoDB中，如果数据是通过查询用到了索引或主键(也是一种索引啦),则相对应的查询结果数据加的是行锁或Gap锁。如果没用到任何索引，则加的是表锁。

表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

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

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL： Select * from emp where empid > 100 for update; 是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。 InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读； 另外一方面，是为了满足其恢复和复制的需要。 很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。 还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。

而组合五，id是主键；组合六，id是unique键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在新插入进来，因此也就避免了GAP锁的使用。其实，针对此问题，还有一个更深入的问题：如果组合五、组合六下，针对SQL：select * from t1 where id = 10 for update; 第一次查询，没有找到满足查询条件的记录，那么GAP锁是否还能够省略？不能省略，Gap锁会锁住这个还不存在的 id = 10的记录,防止insert这条记录。

RR级别中，事务A在update后加锁，事务B无法插入新数据，这样事务A在update前后读的数据保持一致，避免了幻读。这个锁，就是Gap锁。

行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。

同一记录上的更新/删除需要串行执行的约束

在不同的transaction中有多个写操作，有可能发生死锁。死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。(加锁顺序不一致；分析每个SQL的加锁顺序)

参考链接：

https://tech.meituan.com/innodb-lock.html
https://www.jb51.net/article/96179.htm
https://blog.csdn.net/mysteryhaohao/article/details/51669741
http://hedengcheng.com/?p=771

Search

    Table of Contents