西丽网站的建设,在百度如何发布作品,wordpress做一个审核程序,浦东区建设工程监督网站想要用好 MySQL#xff0c;索引与锁是一个绕不开的话题。最近一直在维护以前的业务系统#xff0c;线上频繁报出数据库死锁的异常警告#xff0c;为了排查以及规避死锁的问题#xff0c;因此对 MySQL 的锁(Innodb引擎)做了一个比较深入学习#xff0c;顺便加深自己对 MySQ…想要用好 MySQL索引与锁是一个绕不开的话题。最近一直在维护以前的业务系统线上频繁报出数据库死锁的异常警告为了排查以及规避死锁的问题因此对 MySQL 的锁(Innodb引擎)做了一个比较深入学习顺便加深自己对 MySQL 的理解程度。个人感觉 MySQL 中的锁还是非常的杂官网对于锁的介绍也是和盘托出并没有分门别类的罗列出来下面分别从锁的模式与算法来分析。锁的模式MySQL 中有锁的模式这个概念在 Innodb 中锁的模式有共享锁(S锁)、排它锁(X锁)两大类。除了这两大类还有一个意向锁的概念意向锁在锁模式中有意向共享锁(IS锁)、意向排它锁(IX锁)两类。共享锁(行级别)共享锁也叫读锁对于同一个资源大家都可以同时加上共享锁可以理解成非独家的资源大家都可以获取。共享锁与排它锁互斥在 MySQL 中可以使用“lock in share mode”语法显式的加共享锁。1select * from test where id 1 lock in share mode;排它锁(行级别)排它锁也叫写锁排它锁不仅与共享锁互斥排它锁与排它锁也互斥在 MySQL 中可以使用“for update”语法显示的加排它锁。1select * from test where id 1 for update;意向锁(表级别)InnoDB 存储引擎支持多粒度锁定当数据库对行记录进行加锁时首先会在粗粒度的表级别加上意向锁。意向锁的主要目的是为了提升表锁的加锁效率假设没有意向锁数据库在对表进行加锁时需要扫描表中所有的记录查看是否有行锁与当前要加的表锁冲突。意向锁之间是没有冲突的意向锁与普通的表锁之间有冲突。意向共享锁意向共享锁无法通过语法显式的操作当加上行级别的共享锁时意向共享锁就会存在意向共享锁与排它锁(表级)冲突。意向排它锁意向排它锁与上面的意向共享锁类似它与共享锁(表级)、排它锁(表级)都冲突。普通锁(表级别)表级别的锁因为粒度更粗所以在判断是否有冲突时不仅要考虑表级别下的其它锁还要考虑表中的行锁。共享锁除了对行可以加共享锁也可以对表加共享锁通过“lock table read”语法来对表加共享锁。表级别的共享锁不仅与表级别的排它锁冲突而且与行级别的排它锁冲突。1lock table test read;排它锁除了对行可以加排它锁也可以对表加排它锁通过“lock table write”语法来对表加共享锁。表级别的排它锁不仅与表级别的共享锁、排它锁冲突而且与行级别的排它锁也冲突。1lock table test write;锁的算法锁的模式侧重锁的意图锁的算法更加偏重锁的范围例如下面列举的这些锁都是跟锁住的范围区间有关系。为了更好的演示锁住的区间下面给出测试的表结构以及数据方便论证。表结构12345678CREATE TABLE test_info (id int(11) NOT NULL,no int(20) NOT NULL AUTO_INCREMENT,description varchar(255) NOT NULL,PRIMARY KEY (id),UNIQUE KEY idx_no (no) USING BTREE,KEY idx_description (description(191)) USING BTREE);数据12345INSERT INTO test_info(id, no, description) VALUES (1, 10, 100);INSERT INTO test_info(id, no, description) VALUES (2, 20, 200);INSERT INTO test_info(id, no, description) VALUES (5, 50, 500);INSERT INTO test_info(id, no, description) VALUES (10, 100, 1000);INSERT INTO test_info(id, no, description) VALUES (11, 110, 1000);记录锁记录锁作用在精确匹配的唯一索引上它与间隙锁最大的差别在于间隙锁锁住的是不存在的区间。记录锁 SQL 示例主键索引加锁事物一1select * from test_info where id 1 for update;事物二1select * from test_info where id 1 for update;事物二阻塞。唯一索引加锁事物一1select * from test_info where no 10 for update;事物二1select * from test_info where no 10 for update;事物二阻塞普通索引加锁事物一1select * from test_info where description 1000 for update;事物二1select * from test_info where id 10 for update;事物二阻塞结束事物二执行事物三事物三1select * from test_info where id 11 for update;事物三阻塞结束事物三执行事物四事物四1select * from test_info where id 5 for update;事物四未被阻塞。普通索引作为二级索引被加锁时对应的主键索引也是会被加锁的所以可以看到 id 为 10、11 的记录都会被阻塞住。间隙锁间隙锁一般存在于 RR 的隔离级别MySQL 在 RR 隔离级别下可以解决部分幻读的问题依靠的就是间隙锁。间隙锁的上界、下界都是开区间间隙锁存在的目的主要是为了阻塞插入操作这样就不会存在相同的条件下查询的结果集不一致的情况。间隙锁示例(RR隔离级别)普通索引加锁(未命中记录)事物一1select * from test_info where description 300 for update;事物二1INSERT INTO test_info(id, no, description) VALUES (3, 21, 210);事物二会阻塞因为示例中的事物隔离级别为 RR并且在事物一中没有查询到记录为了避免幻读发生所以将 (200,500) 的期间锁住了。上面的介绍中有提到间隙锁一般存在于 RR 的隔离级别中所以上面的示例在 RC 隔离级别下执行事物二是不会被阻塞的。普通索引加锁(命中记录)事物一1select * from test_info where description 200 for update;事物二1select * from test_info where description 210 for update;事物二阻塞结束事物二执行事物三事物三1select * from test_info where description 190 for update;事物三阻塞。如果普通索引命中了记录那么被锁定的区间不仅有 (200,500)还有 (100,200]所以普通索引的前后都会被锁住。唯一索引加锁对于唯一索引如果命中记录那么会加上记录锁(临间锁退化为记录锁记录锁已演示这种场景)如果没有命中记录则会加上间隙锁。事物一1select * from test_info where no 21 for update;事物二1INSERT INTO test_info(id, no, description) VALUES (3, 22, 210);事物二阻塞临键锁临键锁是由记录锁与间隙锁组成的(区间为左开右闭)所以记录锁与间隙锁可以理解成特殊场景下的临键锁。Innodb 引擎行锁默认采用的就是临键锁的算法只是在不同的场景下会退化成记录锁或者间隙锁。特殊场景的锁插入操作插入操作加锁的操作是一个复杂的过程并不会只存在一把锁。插入过程中首先会加插入意向锁接着检查是否有唯一键冲突的问题针对唯一性冲突还有可能会加上 S GAP(RC的隔离级别也会存在)当记录插入成功时还会对该条记录加上 X 的记录锁。事物一123delete from test_info where no 50;insert into test_info(id, no, description) values (5, 50, 500);事物二1insert into test_info(id, no, description) values (3, 30, 300);事物二阻塞
