MySQL GAP锁初步认识

锁，在现实生活中是为我们想要隐藏于外界所使用的一种工具。在计算机中，是协调多个进程或县城并发访问某一资源的一种机制。在数据库当中，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，数据也是一种供许多用户共享访问的资源。如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。从这一角度来说，锁对于数据库而言就显得尤为重要。

MySQL中根据锁粒度分三种锁机制：行锁（锁定粒度小，发生锁冲突的概率低，并发度高），表锁（锁定力度大，发生锁冲突概率高，并发度最低），页锁（锁定粒度介于表锁和行锁之间，并发度一般）。

而常用的InnoDB存储引擎实现了以下两种类型的行锁。

　　共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

　　排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

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

　　意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

    意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

今天我们主要了解下X锁中的gap锁：

MySQL GAP锁存在于MySQL隔离级别为REPEATABLE-READ或更高级别情况下，为了防止幻读，于是有gap锁和next-key锁存在，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁。即锁住其扫描的范围。

下面我们分别做相应的测试：

测试一：非唯一索引

隔离级别：REPEATABLE-READ

表结构：id为非唯一索引

表内容：

mysql>select * from test2;

+------+----------+

| id   | name    |

+------+----------+

|   10 | xbb     |

|   20 | Michael  |

|   30 | bingo   |

+------+----------+

3 rows in set(0.00 sec)

session1：update一条记录（未提交）

mysql> setautocommit =0;

Query OK, 0rows affected (0.00 sec)

mysql>update test2 set name='icey' where id=20;

Query OK, 1 rowaffected (0.00 sec)

Rows matched:1  Changed: 1  Warnings: 0

session2：insert记录

mysql> setautocommit =0;

Query OK, 0rows affected (0.00 sec)

mysql>insert into test2 values(10,'aa');

ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction

mysql>insert into test2 values(16,'aa');

ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insertinto test2 values(22,'aa');

ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction

mysql>insert into test2 values(30,'aa');

Query OK, 1 rowaffected (0.00 sec)

mysql>UPDATE  test2 set name='icey' whereid=10;

Query OK, 1 rowaffected (0.00 sec)

Rows matched:1  Changed: 1  Warnings: 0

 

mysql>UPDATE  test2 set name='icey' whereid=20;

ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction

mysql>UPDATE  test2 set name='icey' whereid=30;

Query OK, 2rows affected (0.00 sec)

Rows matched:2  Changed: 2  Warnings: 0

 

mysql>delete from test2 where id =20;

ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction

mysql>delete from test2 where id =11;

Query OK, 0rows affected (0.00 sec)

 

查看锁内容：

---TRANSACTION50833735, ACTIVE 780 sec inserting

mysql tables inuse 1, locked 1

LOCK WAIT 2lock struct(s), heap size 1184, 1 row lock(s), undo log entries 2

MySQL thread id4230, OS thread handle 0x7f54a3a4d700, query id 17513177 localhost root update

insert intotest2 values(16,'aa')

Trx read viewwill not see trx with id >= 50833736, sees < 50833736

------- TRX HASBEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKSspace id 13379 page no 4 n bits 80 index `idx_id` of table `test`.`test2` trx id50833735 lock_mode Xlocks gap before recinsert intention waiting

---TRANSACTION50835541, ACTIVE 62 sec starting index read

mysql tables inuse 1, locked 1

LOCK WAIT 2lock struct(s), heap size 360, 1 row lock(s)

MySQL thread id4230, OS thread handle 0x7f54a3a4d700, query id 17513933 localhost rootSearching rows for update

update test2set name ='meachel' where id=20

Trx read viewwill not see trx with id >= 50835542, sees < 50835513

------- TRX HASBEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKSspace id 13379 page no 4 n bits 80 index `idx_id` of table `test`.`test2` trxid 50835541 lock_mode Xwaiting

---TRANSACTION50835541, ACTIVE 185 sec starting index read

mysql tables inuse 1, locked 1

LOCK WAIT 2lock struct(s), heap size 360, 1 row lock(s)

MySQL thread id4230, OS thread handle 0x7f54a3a4d700, query id 17514179 localhost rootupdating

delete fromtest2 where id =20

Trx read viewwill not see trx with id >= 50835542, sees < 50835513

------- TRX HASBEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKSspace id 13379 page no 4 n bits 80 index `idx_id` of table `test`.`test2` trxid 50835541 lock_mode X waiting

由此可知：在隔离级别是RR或者更高的情况下，事务一对某记录进行update（select * for update）时，未提交，

当事务二insert表记录的时候将会gap锁，锁的范围是[上条记录，下一条记录)。

当事务二update，delete表记录的时候将只有X锁，即只对事务更新的那条记录加X锁。

当表中无索引，无主键。当事务一进行更新记录，将锁全表，事务二无法进行insert，update，delete操作。

当表中有主键或者唯一索引。Update/delete一条不存在记录，则也会产生gap锁。