mysql不同隔离级别下锁的实际运用

本文内容是分析mysql的常见索引类型，隔离级别，锁类型，锁的原理，以及思考如何利用这些理论知识运用到实际中，更好的设计表结构和业务sql。

mysql索引

mysql Innodb索引是一个b+树结构，叶子节点之间是链表，存储的是行数据，其余节点存储索引值，在树中索引值是按照值的顺序的存储的。基于树的排序和查找插入原理，能在三次寻找内找到千万数据。mysql的锁也是基于索引来控制锁的范围，确定哪些数据是存在的锁的。这里顺便说下mysql的存储结构，mysql 自定义存储页，每页大概64kb，按照索引顺序存储行数据，这也是索引查找快的原因。其次索引的值这里说明下，基于utf8或者其他编码，索引的值比较本质上是16进制的比较，因此，每次索引是有序的，这点对于确定加锁范围和力度帮助很大，下面的间隙锁、插入意向锁都是由索引值范围确定的。

隔离级别

常用的隔离级别是rr级别和rc级别，这两种级别优缺点都比较明显，rr级别是mysql推荐的级别，由快照的概念和NEXT-KEY(开启binglog才会有)去解决的，next-key可以防止其他数据更新时获取锁冲突，从而影响当前快照数据的准确性，可以做到读当时读取的快照数据，不受其他已提交事务的影响，做到在一次事务内读到的数据都是一样的，当然如果发生了当前读，例如插入事务会触发当前读，mysql利用select for update去锁住当前事务影响的数据行，可以解决该问题。rr级别，就是控制当前事务的数据可读性一致，不会受到其他并发的影响，当然也会存在幻读的的问题，就是update insert操作都是当前读，所以能够读到其他的数据的更改，会覆盖其他数据的修改，这点需要for update去解决。缺点也很明显，对于并发性高的数据行，性能受到很大的影响。

rc级别没有做可读性的控制，只做了读已提交的控制，优缺点和rr级别发过来了，并发性能好但是在一次事务中受到其他并发事务的影响，很可能会产生数据不一致现象，因此常常在业务中可以看到乐观锁对数据行的一致性的保护

mysql常见的锁

1. 行锁、排他锁（X,REC_NOT_GAP）
   行锁就是我们常说的记录锁，当使用主键或者普通索引或者唯一索引更新时，有确切的值时，此时该行记录会加一个锁，可以理解成该锁的标志位是该行的索引值或者该行的主键值，当有其他相同的索引值或主键值更新或者插入是会报冲突的。

2. supremum pseudo record：区间锁的特里，上确界伪记录。即在id索引范围外的数据。

3. 间隙锁(gap)

   mysql rr级别为了解决幻读问题，引入了索引或者主键之间的间隙锁，往右找到第一存在之的索引，来形成锁区间。防止一次事务之间有其他事务DML操作，很明显行锁是和间隙锁冲突的。

4. Next-key

   行锁+间隙锁，具有左开右闭的原则。next-key锁之间实惠互斥的，而间隙锁之间不会。一般rr级别下都会生成next-key lock

5. 读锁（s共享锁）
   在每次对数据行dml操作时都优先会获取读锁，若此时数据行有行锁，则会产生冲突。

6. 插入意向锁（IX）
   在插入数据时，若由于Next-key锁无法插入，事务会转而获取插入意向锁进行锁等待，插入意向锁有点类似间隙锁，只不过用途是作用于插入的事务的，该锁因为具有一个范围而可以增加事务并发性。

7. 意向锁-（IX锁）表锁

   如果存在行锁的情况，想给表加锁，怎么办?遍历查看表有没有行锁，太浪费时间了。此时意向锁登场啦！

   • 意向共享锁(IS锁)：当事务给某行记录增加 S 锁时，同时给表加个 IS 锁。

   • 意向独占锁(IX锁)：当事务给某行记录增加 X 锁时，同时给表加个 IX 锁。

     8.插入意向锁（INSERT_INTENTION）

   

主键、唯一索引、普通索引 实际锁情况

主键和唯一索引本质上都一样，具有唯一性，而普通索引不具有唯一性，主键唯一区别大的是主键索引存放了数据行。

下面看看不同索引情况下锁的实际情况。

CREATE TABLE `lock_test` (
  `id` varchar(64) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index1` (`age`),
  UNIQUE `index2` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

show create TABLE lock_test

INSERT INTO `lock_test`(`id`, `age`, `name`) VALUES ('81fb7903-2614-11ed-adb0-0242ac120001', 10, UUID());
INSERT INTO `lock_test`(`id`, `age`, `name`) VALUES ('81fb7903-2614-11ed-adb0-0242ac120002', 20, UUID());

id是主键，uuid类型， name唯一索引 age是普通索引

rr级别和rc级别主键更新插入锁情况

普通索引更新 和唯一索引更新 以及主键更新锁情况

begin;
set session transaction isolation level repeatable read;
select @@transaction_isolation;
update `lock_test` set name = 'xx1xxs1xx'  where age = '10'; 
select * from performance_schema.data_locks;
COMMIT;

rr级别下，结论 唯一索引和主键更新时，对主键和索引加了行锁，以及对表加了意向锁

普通索引更新时，加了gap锁和主键行锁，以及x锁，这里的x锁的是索引10往前的索引值都会锁住，索引这里锁住的区间有[10,11) 以及对应主键，对于更新或者插没有落到该区间的操作都是可以执行的

需要注意的是，当我们更新或者插入最大的主键id时，rr级别往往会形成一个特殊的间隙锁，[MAX,无穷]，影响后续数据的插入。因为间隙锁和插入意向锁时互斥的。

)

rc级别下由于没有gap锁，只有行锁来控制数据并发。

总结

1.日常操作时尽量用主键或者唯一键去更新，不管在什么级别下锁的范围只停留在行锁，有利于并发性能。

2.在表的设计上，要善于使用唯一索引，给业务字段做一些强校验，同时能够保证数据有效性。

3.尽量不要使用非索引字段去更新表，很容易造成锁表，影响其他并发业务。

4.rc 和rr级别的选择，rc级别锁粒度更小，并发性能好，但是数据可见性比较差，需要自身控制。rr级别可以控制一次事务的数据的一致性，但是要尽量减少幻读的发生，导致数据的篡改，采用主键更新的话，并发性能也还可以。

5.插入的插入意向锁是互补影响的，唯一有冲突的是X锁，插入操作是天然并发的。

参考文章

https://www.51cto.com/article/705170.html