MySQL当中基于索引的加锁和死锁问题

    在介绍MySql引擎的时候，只是大概介绍了以下InnerDB支持行级锁、表锁，MyISAM支持表锁，这篇文章主要是来介绍关于数据库什么时候加锁，加什么样的锁，还有死锁的问题。

    在介绍锁的相关内容之前，需要了解下关于MySql数据库的事务级别，MySQL数据库事务的特性和隔离级别

    关于数据库当中锁的分类,及不同类型的锁,他们的特点

    1、行锁：真对数据库当中特定的行加锁，锁定粒度比较小，不容发生锁冲突，加锁慢，会出现死锁

    2、页锁：锁粒度介于行锁和表锁之间，并发粒度一般，会发生死锁；

    3、表锁：加锁快，并发冲突高，锁定粒度大。

    MyISAM存储引擎的锁实现

     MyISAM不支持事务，支持表锁，对于表的锁定分为共享锁（S锁）和排它锁（X锁）。

     当一个事务正在对表进行读取时，其他事务也可以对该表的数据进行读取，但是不能对该表数据写入，当持有共享锁的事务释放掉共享锁之后，其他事务才可以进行写入。

     当一个事务正在进行对表执行写入操作时，其他的事务不能够读取，当写入操作的事务执行结束，释放掉锁，其他的事务才能执行相应的操作。

     关于共享锁和排它锁其实可以参照java当中的ReadWriteReentrantLock读写锁。

    MyISAM锁调度策略

       如果读事务在等待获取锁的时候，有写事务也要获取锁，那么会优先让写事务获取到锁，因为在MyISAM存储引擎当中，写操作优先级是高于读操作的。如果我们对数据库频繁的修改或者插入，就会造成查询效率降低。因此MyISAM存储引擎适合快速查询。当然也可以通过一系列的参数设置来修改读操作的优先级。

     表级别的锁并不会造成死锁。除非在java代码层面，数据层面上，是不会造成死锁。

   InnerDB存储引擎的锁实现

    InnerDB是支持事务的，并且InnerDB的锁根据不同的事务级别有不同的加锁方式，总体上来讲,InnerDB的锁分为行级别锁和全表锁。

    对于InnerDB来说，加行级别锁的前提是，是否可以通过索引定位到行，如果可以就加行锁，如果不可以就加全表锁。当然索引的不同，会导致加锁行的数量不同，而事务级别的不同，会导致加锁的方式不同。下面我们来看下不同事务级别下，不同的加锁方式。

select * from tbl_name where id = 1;
update tbl_name set name = 'abc' where id =1;

 对于上面两条sql在不同的事务，不同的表索引下加锁方式是不同的，下面我们分别讨论

  对于上述的第一条查询操作，属于快照读，读取的是某个历史版本当中的数据，并不加锁，当然也有特殊情况，后面我们会讨论，

   对于更新操作，是需要加锁的， 但是具体如何加锁，我们要根究事务级别来讨论下，关于聚集索引 MySQL索引相关内容汇总

   1、Read Commit 

        1、1 id为主键索引

              InnerDB当中采用的是聚集索引，B+树中叶子节点存储的就是索引数据和对应的行数据，如果当前id是主键索引，那么可以确定根据索引确定具体的某个行，在这个行上面添加排它锁即（X锁）

        1、2id为唯一索引

             如果当前id为唯一索引，name为主键索引，在进行索引查找时需要两个步骤，第一步，查询到索引id对应的主键，第二步，根据主键查询到数据库信息，那么这个加锁过程就需要在第一步锁定的数据和第二步锁定的数据分别加上排它锁。

        1、3 id为普通索引（非唯一索引）

             根据InnerDB存储引擎的实现规则，同样在查询时，需要先找到id对应的主键，加锁，然后找到主键对应的行数据，再次加锁，不同的是，由于id非唯一索引，那么这两步加锁的数据可能不止一行，有可能是多行。

        1、4 id 非索引

            如果id不是索引，那么这是时候就需要进行全表扫描，InnerDB引擎的现实方式就是将整个表加锁，当然MySQL在实现上也有一定的优化，在全表扫描后，对于不满足条件的，会释放掉锁，只对满足条件的数据进行加锁。

    2、Repeatable Read 

         2、1 id为主键索引

               如果是id主键，并且事务是RR级别，同1、1加锁方式

         2、2 id 为唯一索引

               加锁方式同1、2

         2、3 id 为普通索引（非唯一索引）

              在RR级别下，同一个事务内，不管什么时候读，读取到的数据都是一致的，这是RR事务和Read Commit事务的本质区别，为了实现RR事务上的隔离，MySQL会在对应行上面加X锁，同时在对应行直接加Gap锁（即间隔锁）

         当其他事务尝试去插入一条数据时，[id,name]=[10,c] 根据id = 10，会先考虑在当前6和10之间插入，发现6和10之间加入了Gap锁，插入失败，接着尝试往10和10之间插入，发现也是有Gap锁，插入失败。最终发现[10,c]这条数据无法插入，这样就保证了RR级别下不能幻读。所以总结起来就是，对于非唯一索引，会根据查询条件，将查询到的数据行加上排他锁，同时在行之间加上Gap锁，根据聚集索引查询到对应的数据，对行数据再次加上排他锁，行锁。

      2、4 id无索引

         如果id没有索引，那么同样的需要进行全表扫描，这个时候就需要进行全表加锁，其次每条记录的间隙都需要加上Gap锁。可见这个加锁是个浩大的工程，同样MySQL也做了一些优化，在开启semi-consitent read 时，对于不满足条件的行，都会释放行锁和Gap锁。

       3、Serializable串行化

          对于这个级别来说，和Repeatable Read级别一致，非唯一索引时，同样需要加Gap锁。需要注意的是，在这个事务级别下，如果是select * from tbl_name where id = 1 ，也是需要加排它锁的。

关于死锁

   当一个事务A持有了表当中第五行数据的锁，同时又持有第10行的锁，而事务B持有是表当中第10行数据的锁，尝试要持有表当中第五行的锁的时候，就会提示死锁。

  对于死锁二，每个事务只有一条SQL语句，事务A需要持有锁的行号为1,6, 事务B需要持有的锁行号是6,1，当这两个事务同时执行时，就会发生死锁。

死锁三

事务的隔离级别是可重复读

c2为普通索引,下面为伪代码

begin:
a = update T set c1= 1 where c2 =2;
if(a is fail)
   insert into T(c1,c2) values(1,2);
commit;

假如现在有两个事务都执行上述操作，

1、T1 执行update语句，记录不存在不会加锁，但是会加间隙锁，间隙锁锁住（1，+∞）的数据

2、T2执行update 语句，同时也会加上间隙锁（间隙锁可以重复加），锁住表（1，+∞）的数据

3、T1 执行insert语句，试图插入数据，但是被T2的间隙锁阻塞

4、T2执行insert语句，试图插入数据，但是被T1的间隙锁阻塞

解决办法：

    避免更新或者删除不存在的记录，虽然更新存在的数据也会产生间隙锁，但是其范围相对比较小；更新不存在的数据，锁的范围比较大，容易发生死锁。

死锁四：

    c2 为普通索引，事务界别为可重复读

begin
insert into T(c1,c2) values(0,1);
update T set c1=0 where c2 = 1;
commit;

1、当T1事务执行insert时，成功插入一条数据，（1,0,1）主键为1，并对当前这条记录加上排它锁

2、T2执行insert时，成功插入一条数据，（2,0,1）主键为2，并对当前记录添加上了排它锁

3、T1执行update时，需要对所有复合条件的记录添加排它锁，已经持有了id=1的排它锁，等待id=2的排它锁

4、T2执行update时，需要对所有复合条件的记录增加排它锁，已经持有了id=2的排它锁，等待id=1的排他锁

上述情况也会造成死锁，解决办法就是将update语句改成是 update T set c1=0 where c2=1 and id =1;

总结：死锁发生与否不在于事务当中有多少条SQL,而是每个事务当中加锁的顺序不一致造成的！

 

参考：MySQL 加锁处理分析

          mysql死锁问题分析

          MySQL优化系列（八）--锁机制超详细解析（锁分类、事务并发、引擎并发控制）

          MySQL中的锁（表锁、行锁）