MySQL Gap Lock问题

四种隔离级别说明

隔离级别脏读（Dirty Read）不可重复读（NonRepeatable Read）幻读（Phantom Read）
未提交读（Read uncommitted） 可能 可能 可能
已提交读（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（SERIALIZABLE） 不可能 不可能 不可能

脏读 :一个事务读取到另一事务未提交的更新数据

不可重复读 : 在同一事务中,多次读取同一数据返回的结果有所不同, 换句话说, 后续读取可以读到另一事务已提交的更新数据. 相反, “可重复读”在同一事务中多次读取数据时, 能够保证所读数据一样, 也就是后续读取不能读到另一事务已提交的更新数据。

幻读 :一个事务读到另一个事务已提交的insert数据

事务隔离超通俗好懂的的讲解

按照隔离的级别由低到高，越高的隔离，效率越差

　　0）、DEFAULT 默认隔离级别，由数据库的数据隔离级别确定隔离级别
       1）、READ_UNCOMMIYTTED 都未提交的 级别最低
             允许别的事务，去读取这个事务为提交之前的数据
             缺点：可能会造成脏读、幻读、不可重复读。
             例子讲解：店家对1000元商品进行降价500处理，数据更改，但未提交事务；
             然后你查到降价将货物并提交订单； 可是商家由于操作异常，数据回滚，返回原价格1000；
             这样，在店家那边是没有操作成功的，可是在你这里却得到了降价货物订单。
             可理解为：外层更改价格的事务拉长执行，在执行之中出现提交订单的事情，你读了别人正在处理的数据。
               
      2）、READ_COMMITTED 读已提交级别     
             案例讲解：你原本查询商品价格为500，第二次为了证明真的降价了再查却发现并没有降价。

　　　缺点：两次读的都是真的（不脏读） 可是却存在不可重复
                    可以理解为: 你两次查询为一次事务，将此事务拉长；
                    在此事务中间，有更改价格的操作，可能执行了多次，可是在这个多次执行修改中你无法插进去查一次；
                    只能等他更改完（中间事务结束）才能进行下一次查询，你读的期间别人插进来对数据操作了。
　　　　　  两件事务出现了交集。

　　3）、REPEATABLE_READ 可重复读    事务是多次读取，得到的相同的值。

　　　缺点： 会出现幻读
                  即该事务执行期间，不允许其他事务对该事务数据进行操作，保证该事物中多次对数据的查询结果一致。
           　　就是你多次查询的这个事务包含多条数据，为了保证读取的一致性，可重复读（REPEATABLE_READ）将使用的数据锁起来不让别人用。
                   
       4）、SERIALIZABLE 串行化读 将事务排序，逐个执行事务提交了之后才会继续执行下一个事务。

　　　缺点：都隔离开了，效率慢　　
                 就是上面的可重复读（REPEATABLE_READ）是将使用的数据范围锁起来不让别人用，而这里是将涉及数据的表全都锁起来，不允许别人操作。
                 这个事务的执行中，别的事务连在旁边看的机会都没有，完全不会有影响，你读的时候别人看不到，隔离开，单独执行。
　　　　  整个事务排队执行
 
本文为个人学习总结，可能会存在一些理解错误，或误差，还请路过各位指点批评。

 

文章来源：http://blog.chinaunix.net/uid-20726500-id-5749804.html 作者：@小桥河西

初识MySQL的gap，觉得这个设计比较独特，和其他数据库的做法不太一样，所以整理一个简单的memo（虽然关于gap锁，相关资料已经很多了）

一、什么是gap

A place in an InnoDB index data structure where new values could be inserted.

说白了gap就是索引树中插入新记录的空隙。相应的gap lock就是加在gap上的锁，还有一个next-key锁，是记录+记录前面的gap的组合的锁。

二、gap锁或next-key锁的作用

http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row 
locking with gap locking. InnoDB performs row-level locking in such a way that when it searches
 or scans a table index, it sets shared or exclusive locks on the index records it encounters. 
Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on 
an index record also affects the “gap” before that index record. That is, a next-key lock is 
an index-record lock plus a gap lock on the gap preceding the index record. If one session has 
a shared or exclusive lock on record R in an index, another session cannot insert a new index 
record in the gap immediately before R in the index order.

简单讲就是防止幻读。通过锁阻止特定条件的新记录的插入，因为插入时也要获取gap锁(Insert Intention Locks)。

三、什么时候会取得gap lock或nextkey lock

这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。

http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

REPEATABLE READ

... For consistent reads, there is an important difference from the READ COMMITTED isolation level:
 All consistent reads within the same transaction read the snapshot established by the first read. ...

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, 
locking depends on whether the statement uses a unique index with a unique search condition, 
or a range-type search condition. For a unique index with a unique search condition, 
InnoDB locks only the index record found, not the gap before it. For other search conditions, 
InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions 
by other sessions into the gaps covered by the range.

locking reads，UPDATE和DELETE时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁。即锁住其扫描的范围。

下面对非唯一索引做个测试。

表定义如下:

mysql> show create table tb2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| tb2   | CREATE TABLE `tb2` (
  `id` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  KEY `tb2_idx1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

表中有3条记录： 10,20,30。

mysql> select * from tb2;
+------+------+
| id   | c1   |
+------+------+
|   10 |    0 |
|   20 |    0 |
|   30 |    0 |
+------+------+
3 rows in set (0.01 sec)

在REPEATABLE READ下，更新一条记录不提交，然后看看能阻塞另外的会话哪些操作。

SESSION 1:

SESSION 1中更新id=20的记录

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb2 set c1=2 where id=20;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SESSION 2:

SESSION 2中，执行插入操作，发现[10,30)范围不能插入数据。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb2 values(9,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb2 values(10,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(19,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(20,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(21,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(29,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(30,4);
Query OK, 1 row affected (0.01 sec)

对于更新操作，仅20这条记录不能更新，因为更新操作不会去获取gap锁。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update tb2 set c1=4 where id=10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update tb2 set c1=4 where id=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update tb2 set c1=4 where id=30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

如果SESSION 1的表扫描没有用到索引，那么gap或next-key锁住的范围是整个表，即任何值都不能插入。

READ COMMITTED

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, 
and DELETE statements, InnoDB locks only index records, not the gaps before them,
 and thus permits the free insertion of new records next to locked records.

只会锁住已有记录，不会加gap锁。

SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain 
SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled.

和REPEATABLE READ的主要区别在于把普通的SELECT变成SELECT … LOCK IN SHARE MODE，即对普通的select都会获取gap锁或next-key锁。

REPEATABLE READ和幻读

在“consistent-read”时，REPEATABLE READ下看到是事务开始时的快照，即使其它事务插入了新行通常也是看不到的，所以在常见的场合可以避免幻读。 但是，”locking read”或更新，删除时是会看到已提交的修改的，包括新插入的行。

http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

If you want to see the “freshest” state of the database, use either the READ COMMITTED 
isolation level or a locking read:

下面看一个例子

SESSION 1:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 |  100 |
+----+------+
1 row in set (0.00 sec)

SESSION 2:

mysql> update tb1 set c1=101 where id =1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SESSION 1:

mysql> select id,c1 from tb1 where id=1 LOCK IN SHARE MODE;
+----+------+
| id | c1   |
+----+------+
|  1 |  101 |
+----+------+
1 row in set (0.00 sec)

mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 |  100 |
+----+------+
1 row in set (0.00 sec)

mysql> update tb1 set c1=c1+1000 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 | 1101 |
+----+------+
1 row in set (0.00 sec)

上面update的行为违反了REPEATABLE READ的承诺，看到了事务开始后其它事务的并发更新。这对应用开发需要特别注意，这种情况下其它数据库通常都是报错的。

其它

RR和RC相比还有一个重要的区别，RC下，扫描过但不匹配的记录不会加锁，或者是先加锁再释放，即semi-consistent read。但RR下扫描过记录都要加锁。这个差别对有全表扫描的更新的场景影响极大。详细参考http://hedengcheng.com/?p=771，关于MySQL的加锁处理，这篇文章讲得很透彻！

参考

posted @ 2018-06-28 16:07  穆穆兔兔  阅读(5279)  评论(0)  编辑  收藏  举报