Innodb中的锁

  虽然比较擅长的是Oracle，但是公司使用的是MySQL数据库，所以不得不对MySQL数据库多研究一下。今天就谈一下MySQL中的锁。

  谈锁之前，要明白为什么会有锁这种东西。之所以有锁，大部分情况下是为了实现事务(transaction)之间的隔离，那么事务之间有几种隔离方式，各种隔离方式又是为了达到什么效果呢？先来说一下各种读现象。

1. 脏读：即一个事务A读取了事务B还没有提交过的数据；

2. 不可重复读：即事务A在第一次按照某条SQL语句读取后跟第二次按照相同的SQL语句读取数据库，这段时间内，事务B对这条SQL语句覆盖的数据行进行了已提交的修改（update或delete），导致A前后两次读取数据不一样；

3. 幻读：即事务A在第一次按照某条SQL语句读取后跟第二次按照相同的SQL语句读取数据库，这段时间内，事务B插入了满足SQL语句的数据行并提交了（insert），导致A前后两次读取数据不一样；

针对以上几种读现象，就有了read-uncommitted，read-committed，repeatable read，

serializable read，这几种事务隔离级别，下面是各种隔离级别可以防止的读现象：

下面通过例子的形式介绍，切记以begin显式开启事务。虽然mysql使用的是repeatableread的隔离级别，但是为了在一定程度上防止幻读，使用了next key算法（下面将介绍）进行上锁。

  例如使用如下SQL语句建表

   createtable ktest (id int primary key auto_increment, col1 int, col2 int, key(col1));

  其中id列包含主键索引的列，col1为包含非唯一索引的列，col2为不包含任何索引的列。使用如下sql语句对数据表插入数据

   insertinto ktest (col1, col2) values (1,2),(2,3),(2,4),(4,5),(8,9);

  以下是col1上索引的大体情况：

（空白部分表示还没有插入）

  在利用非唯一键进行唯一匹配更新或删除的时候，例如此时事务A执行updatektest set col2 = 10 where col1 = 2;

  那么除了两个col1=2的地方需要上锁之外，为了确保在事务执行过程中不会出现幻读，即不能说刚把这两行数据修改完成但还没有提交事务的情况下，其他事务就能能插入col1=2（例如此时事务B执行insert into ktest values (10,3,5);）的数据行，那么需要在事务A执行update语句之前，在col1为2（右边的）到col1为4之间，加一个“虚拟锁”，保证这段时间内不会有col1=2的数据插入进来，但是这样带来了一个弊端，即col1=3的数据行也不能在A执行的期间插入。这种在两个索引值之间加的“虚拟锁”就是GAP锁，加上原本就应该加上锁的两个col1=2的锁，合起来就叫next key锁。

  当然如果第一个col1不是1，而是0的话也要在col1为0到col1为2（左边的）之间，加上类似的“虚拟锁”。这样也会造成col1=1的数据行无法插入。

  以下是事务A（左）执行上面sql语句时，事务B（右）试图插入col1=3的截图

图一

相反，对于id这样的唯一列，在这上面试图用唯一匹配的方式更新一行数据的时候，由于id已经是唯一列了，因此不需要通过锁的方式防止有相同的id插入，所以只会对匹配的行上锁。例如在上述表中没有其他事务时执行完insert into ktest values (10, 3, 5);

此时如果不采取默认提交（即用begin显示开启事务），同时在两个事务中分别执行

transaction A:update ktest set col2 = 10 where id = 10;

transaction B:insert into ktest values (9, 1, 1);

两个事务之间不会产生任何锁定，截图如下

图二

虽然这种情况下不会产生因为GAP而导致的事务阻塞，但是不代表使用唯一键就不会有GAP锁。例如在两个事务中分别执行

transaction A:update ktest set col2 = 10 where id < 10;

transaction B:insert into ktest values (7, 1, 1);

事务A不仅会跟已有的id（1,2,3,4,5,9）上锁，5与9之间也会有GAP锁，而导致事务B被堵住。当然如果执行insertinto ktest values (17, 1, 1);是没有问题的，因为id=17的情况不会被这种锁机制包含；同理执行insert into ktest values (-17, 1, 1);仍然会被堵住。

当然由于innodb中锁是基于索引实现的，如果在没有索引的col2上执行如下语句，将会锁住所有行

update ktest setcol2 = 10 where col2 =10

虽然GAP锁可以在一定程度上防止幻读，但是有些时候也会引发一些莫名其妙的错误，例如下面表格表示的两个事务，比如在上面的例子中，即使transaction B 多次全表扫描，但是仍然看不到id为7的数据行，可是用户就是没办法在transaction B中插入id为7的数据行。

当通过修改innodb_locks_unsafe_for_binlog参数使其等于1的时候，重启mysql服务，可以禁用GAP锁，下面的两个事务也不会产生堵塞。（先左后右）

图三

总结：

由此可见GAP锁虽然可以控制事务的隔离级别，但是无可避免的降低了事务的并发量，在生产中可以考虑使用read-committed的事务隔离级别（类似Oralce），或者将innodb_locks_unsafe_for_binlog参数设置为1，取消可重复读隔离级别下的GAP锁。

如果对索引结构不太熟悉可以参考之前我写过的关于索引的文章

http://9305967.blog.51cto.com/9295967/1885949

相关图片已经打包上传，大家看不清可以下载

  最后提前祝大家元旦快乐。

                        2016.12.30 

本文出自 “肯草在深圳” 博客，谢绝转载！

Innodb中的锁