MySQL常见加锁场景分析一 |
您所在的位置：网站首页 › 什么情况下读iz › MySQL常见加锁场景分析一 |
MySQL常见加锁场景分析一
|
前言
在《MySQL中到底有哪些锁》一文中，描述了MySQL中存在的各种各样的锁，也大致说了一下各种锁出现的场景，但是整个文章通读下来，大家可能知道有这些锁了，或者啥也没有记住。今天这篇文章会找些实际的例子，来具体说说SQL加了什么锁。这样也好让大家加深理解，以便更好的解决真实开发中遇到的死锁问题。 一个SQL对记录加什么锁，这个里面其实有很多的道道的，不是一句话两句话就能说的清楚。（那你就长话短说呗）那么废话不多说，就先来说一说SQL为记录加锁到底有哪些条件约束。 事务隔离级别：前面文章也说到过，间隙锁在一般情况下，只有在不小于可重复读的事务隔离级别下出现，但是特殊情况下，读已提交的隔离级别下也会有。这就很明确的告诉我们，SQL给记录加什么锁是受到事务隔离级别的影响的。 SQL执行中是否使用使用索引和索引的类型：我们都知道索引可以分为普通索引、唯一索引、主键索引、联合索引和全文索引等待。MySQL的锁也是基于索引实现的，SQL用到不同的索引和没有到索引，记录上加的锁肯定是不一样的。 查询的方式：SQL是精确查询、模糊查询还是范围查询。 SQL语句的类型：SELECT、INSERT、UPDATE、DELETE语句对记录加锁是不一样的。 知识补充 锁定读：指的是以下四种语句： SELECT …… LOCK IN SHARE MODE SELECT …… FOR UPDATE UPDATE DELETE快照读： 在读未提交的级别下，每次都读取记录的最新版本。 在读已提交的级别下，每次都读取已经提交的版本。 事务隔离级别对加锁的影响MySQL 的事务隔离等级对加什么锁有很大的影响，所以在分析具体的加锁场景时，首先要做的就是确定当前的是事务隔离级别。 读未提交（Read Uncommitted 后续简称 RU）：读未提交，该级别下脏读、不可重复读、幻读都可能发生。基本没有该隔离级别应用的业务场景，所以直接忽略。 读已提交（Read Committed 后续简称 RC）：该级别下不可重复读、幻读都可能发生，脏读不允许发生。对当前读获取的数据加记录锁。 可重复读（Repeatable Read 后续简称 RR）：该级别下幻读都可能发生，脏读、不可重复读不可能发生，对当前读获取的数据加记录锁，同时对涉及的范围加间隙锁，防止新的数据插入导致幻读。 序列化（Serializable）：不在使用 MVCC 并发控制，直接基于锁的并发控制，不存在快照读，都是当前读，并发效率急剧下降。该事物隔离级别不建议使用，所以也不是本文重点。 读未提交和序列化两个隔离级别因为各自的问题在实际的业务场景用的不多，本文就不对他俩再添笔墨，我们将重点关注读已提交和可重复读。 对于这两个隔离级别，你们项目是选择了哪个？那又为什么选择某个级别，你有想过么？ 在这里给出个结论：一般情况下，在读已提交事务隔离级别下，加锁的基本单位是记录锁；在可重复读事务隔离级别下，加锁的基本单位是next key锁。另外，无论是在哪个隔离级别下，只要是唯一性搜索，并且读取的记录没有被标记删除，就为读取到的记录加记录锁。 满足以下四个条件就可以称之为唯一性搜索： 匹配模式为精确匹配 使用的索引为主键或者唯一二级索引 如果使用的索引是唯一二级索引，那么该索引必须不能存NULL值 如果唯一二级索引包含多个列，那每个列都要被用到（注意联合索引的最左匹配原则）那接下来我们就来举例说明不同的SQL到底加了啥锁？ 实验准备在具体举例说明之前，我们先准备一些实验材料，例如创建个表test，具体机构如下： CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL COMMENT '名称', `no` varchar(32) NOT NULL COMMENT '编号', `city` varchar(255) NOT NULL COMMENT '所在城市', `gender` char(1) NOT NULL COMMENT '性别', `remark` varchar(32) DEFAULT NULL COMMENT '备注', PRIMARY KEY (`id`), UNIQUE KEY `uk_no` (`no`) USING BTREE, KEY `idx_name` (`name`) USING BTREE, KEY `idx_city_gender` (`city`,`gender`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;往表test里插入一些数据： INSERT INTO `test` VALUES ('5', '张三', '133132', 'n南京', '男', 'epbujch'); INSERT INTO `test` VALUES ('9', '王五', '123009', 'h合肥', '男', 'epbujchuf1k'); INSERT INTO `test` VALUES ('10', '李翔', '123000', 'n南京', '男', '3roa0'); INSERT INTO `test` VALUES ('15', '李建', '123132', 'b北京', '男', 'g3ruwrnoa0'); INSERT INTO `test` VALUES ('16', '李四', '123001', 's上海', '男', 'ufuwrn1kg3roa0'); INSERT INTO `test` VALUES ('17', '李丽', '123002', 'g广东', '女', 'ejchufuwrn1kg'); 在读已提交的事务隔离级别下SQL到底加什么锁由于涉及到很多条件，所以这里先限定事务隔离级别：读已提交。 普通的SELECT语句一般的情况下，系统里都是读多写少，那么我们就先看看普通的SELECT语句会加什么锁。例如下面的SQL： SELECT * FROM test WHERE id = 10;上面的SQL中where条件id=10，其实不管他是否是主键、其他的索引还是没有用到索引，他都不会对任何数据加锁，他只是一个普通的SQL语句，只进行快照读，只是在不同的事务隔离级别下读取的快照不同： 在读已提交（READ COMMITTED）隔离级别下，在每次执行普通的SELECT语句是都会生成一个ReadView，这里理解成执行的时候生成一个快照。执行时不加锁。 在可重复读（REPEATABLE READ）隔离级别下，只在第一次执行普通的SELECT语句时生成一个ReadView，之后的SELECT操作都复用这个ReadView。执行时不加锁。结论： 普通SELECT语句不加锁。 加锁的流程MySQL中读取符合条件的记录大概过程如下： 首先要定位到符合边界条件(即用到的索引)的第一条记录，把该记录作为当前记录。 给当前记录加锁。 不同的事务隔离级别加锁的类型不同，在读已提交级别下加记录锁；在可重复读的隔离级别下加next key 锁。 判断索引下推的条件是否成立。就是把查询中使用到的二级索引相关的条件下推到存储引擎中去判断。如果当前记录符合下推到引擎层的条件，那么跳到步骤4继续执行。若是不符合直接获取当前记录的下一条记录，作为第一条记录，并跳回步骤2，然后继续执行。前面在判断是否符合下推的条件的同时，还会判断记录是否符合边界条件和是否是最后一条记录，若不符合或者是最后一条记录则直接向server层返回“查询完毕”的信息。另外，该步骤中，不管记录是否符合所有的条件，都不释放步骤2中加的锁。 执行回表操作。如读取的是二级索引记录，则需要进行回表操作，获取到记录对应的聚簇索引记录并给改聚簇索引记录加记录锁。 判断边界条件是否成立。记录符合边界条件，则跳到步骤6继续执行，否则在读已提交事务隔离级别下，释放掉加的锁；在可重复读的隔离级别下，不释放锁；并且向server层返回“查询完毕”的信息。 server层判断其余的条件是否成立。除去索引下推的条件，其余的条件server层要判断条件是否成立。成立则将记录发送到客户端，不成立则在读已提交事务隔离级别下，释放掉加的锁；在可重复读的隔离级别下，不释放锁。 获取刚刚操作的记录所在的单向链表的下一条记录，当做第一条记录跳到第二步，然后重复上述的步骤。通过主键到聚餐索引中获取完整的记录的过程就叫做回表，因为二级索引下面携带的主键信息，他不是相邻的id，即携带的主键是无序的，所以这个回表的过程是一个随机耗时的io操作。 索引下推（Index Condition PushDown，ICP），其实就是在查询过程中使用到的二级索引与之相关的搜索条件下推到存储引擎中判断，不是像之前返回到server层再判断。索引下推是一个使用到二级索引时一个简单但是有效的优化措施，他减少了回表次数。他适用于二级索引中的联合索引，且只适合SELECT语句，其他类型的SQL无效。 使用主键进行等值查询的场景 使用SELECT ... LOCK IN SHARE MODE的场景假设有以下的SQL： begin; SELECT * FROM test WHERE id=5 LOCK IN SHARE MODE;为啥要在事务中使用SELECT ... LOCK IN SHARE MODE，因为在MySQL中，如果不显示的开启事务，那么每一条语句均为一个独立的事务。这样“SELECT * FROM test LOCK IN SHARE MODE;”的语句，一瞬间就执行完毕了，看不出加锁的效果。 **结论：**上面那个语句执行时需要访问聚餐索引中id=5的记录，给该记录加S型记录锁。如果id=5不存在则不加锁。 使用SELECT ... FOR UPDATE的场景假设有以下的SQL： begin; SELECT * FROM test WHERE id=5 FOR UPDATE;**结论：**上面那个语句执行时需要访问聚簇索引中id=5的记录，给该记录加X型记录锁。如果id=5不存在则不加锁。 使用 UPDATE 的场景使用UPDATE的场景里面还需要区分两种情况，一种是更新了索引，另外一种是没有更新索引的。 情况1： UPDATE test SET remark = 'xx' WHERE id = 5;这里更新的字段没有涉及到索引列，所以加锁情况和使用SELECT ... FOR UPDATE的场景相同，就是给主键加了 一个X型排他锁。 情况2： UPDATE test SET no = '133132' WHERE id = 5;上面的UPDATE的语句更新了二级索引列，他其实是用id = 5条件先定位到具体的聚簇索引位置，给该条记录加X型记录锁，然后由于他要更新二级索引列，所以也要给 索引 uk_no对应的记录加锁，也加上X型记录锁。 结论： 先给主键加X型记录锁再给对应的二级索引加上X型记录锁。 使用 DELETE 的场景 DELETE FROM test WHERE id = 5;DELETE不用多说，这种肯定也是加X型排他锁的情况。他的执行过程其实就是先在B+树中定位到这条记录的位置，然后获取这个记录的X锁，最后执行delete mark操作。（删除一条记录只是打一个标记，并不是马上删除的）如果有涉及到二级索引的，肯定是在获取主键的X型记录锁之后，再获取二级索引对应记录的X型记录锁。 结论： 先给主键加X型记录锁，若有涉及到二级索引的，再给二级索引对应记录的X型记录锁。看这个结论其实和前面 UPDATE的第二种情况的加锁结论相同。 使用主键进行范围查询的场景 使用SELECT ... LOCK IN SHARE MODE的场景假设有以下的SQL： begin; SELECT * FROM test WHERE id |
【本文地址】
今日新闻 |
推荐新闻 |