网站建设中有关数据库问题,注册电气工程师考试科目,全网营销推广怎么做,舆情系统是什么InnoDB 三种行锁#xff1a;
Record Lock#xff08;记录锁#xff09;#xff1a;锁住某一行记录 Gap Lock#xff08;间隙锁#xff09;#xff1a;锁住一段左开右开的区间 Next-key Lock#xff08;临键锁#xff09;#xff1a;锁住一段左开右闭的区间
哪些语句…InnoDB 三种行锁
Record Lock记录锁锁住某一行记录 Gap Lock间隙锁锁住一段左开右开的区间 Next-key Lock临键锁锁住一段左开右闭的区间
哪些语句上面会加行锁 (1) 对于常见的 DML 语句如 UPDATE、DELETE 和 INSERT InnoDB 会自动给相应的记录行加写锁 (2) 默认情况下对于普通 SELECT 语句InnoDB 不会加任何锁但是在 Serializable 隔离级别下会加行级读锁
上面两种是隐式锁定InnoDB 也支持通过特定的语句进行显式锁定 (3)SELECT * FROM table_name WHERE … FOR UPDATE加行级写锁 (4) SELECT * FROM table_name WHERE … LOCK IN SHARE MODE加行级读锁
加锁规则的两条核心
(1) 查找过程中访问到的对象才会加锁
这句话该怎么理解比如有主键 id 为 1 2 3 4 5 … 10 的10 条记录我们要找到 id 7 的记录。注意查找并不是从第一行开始一行一行地进行遍历而是根据 B 树的特性进行二分查找所以一般存储引擎只会访问到要找的记录行id 7的相邻区间 2加锁的基本单位是 Next-key Lock
下面结合实例帮助大伙分析一条 SQL 语句上面究竟被 InnoDB 自动加上了多少个锁
假设有这么一张 user 表id 为主键唯一索引a 是普通索引非唯一索引b 都是普通的列其上没有任何索引
案例 1唯一索引等值查询
当我们用唯一索引进行等值查询的时候根据查询的记录是否存在加锁的规则会有所不同
当查询的记录是存在的Next-key Lock 会退化成记录锁 当查询的记录是不存在的Next-key Lock 会退化成间隙锁
查询的记录存在
先来看个查询的记录存在的案例
select * from user where id 25 for update;
结合加锁的两条核心查找过程中访问到的对象才会加锁 加锁的基本单位是 Next-key Lock左开右闭我们可以分析出这条语句的加锁范围是 (20, 25]
不过由于这个唯一索引等值查询的记录 id 25 是存在的因此Next-key Lock 会退化成记录锁因此最终的加锁范围是 id 25 这一行
查询的记录不存在
再来看查询的记录不存在的案例
select * from user where id 22 for update; 结合加锁的两条核心查找过程中访问到的对象才会加锁 加锁的基本单位是 Next-key Lock左开右闭我们可以分析出这条语句的加锁范围是 (20, 25]
这里为什么是 (2025] 而不是 (20, 22]因为 id 22 的记录不存在呀InnoDB 先找到 id 20 的记录发现不匹配于是继续往下找发现 id 25因此id 25 的这一行被扫描到了所以整体的加锁范围是 (20, 25]
由于这个唯一索引等值查询的记录 id 22 是不存在的因此Next-key Lock 会退化成间隙锁因此最终在主键 id 上的加锁范围是 Gap Lock (20, 25)
案例 2唯一索引范围查询
唯一索引范围查询的规则和等值查询的规则一样只有一个区别就是唯一索引的范围查询需要一直向右遍历到第一个不满足条件的记录.
下面结合案例来分析
select * from user where id 20 and id 22 for update; 先来看语句查询条件的前半部分 id 20因此这条语句最开始要找的第一行是 id 20结合加锁的两个核心需要加上 Next-key Lock (15,20]。又由于 id 是唯一索引且 id 20 的这行记录是存在的因此会退化成记录锁也就是只会对 id 20 这一行加锁。
再来看语句查询条件的后半部分 id 22由于是范围查找就会继续往后找第一个不满足条件的记录也就是会找到 id 25 这一行停下来然后加 Next-key Lock (20, 25]重点来了但由于 id 25 不满足 id 22因此会退化成间隙锁加锁范围变为 (20, 25)。
所以上述语句在主键 id 上的最终的加锁范围是 Record Lock id 20 以及 Gap Lock (20, 25)
案例 3非唯一索引等值查询
当我们用非唯一索引进行等值查询的时候根据查询的记录是否存在加锁的规则会有所不同
(1) 当查询的记录是存在的除了会加 Next-key Lock 外还会额外加间隙锁规则是向下遍历到第一个不符合条件的值才能停止也就是会加两把锁很好记忆就是要查找记录的左区间加 Next-key Lock右区间加 Gap lock
(2) 当查询的记录是不存在的Next-key Lock 会退化成间隙锁这个规则和唯一索引的等值查询是一样的
查询的记录存在
先来看个查询的记录存在的案例
select * from user where a 16 for update; 结合加锁的两条核心这条语句首先会对普通索引 a 加上 Next-key Lock范围是 (8,16]
又因为是非唯一索引等值查询且查询的记录 a 16 是存在的所以还会加上间隙锁规则是向下遍历到第一个不符合条件的值才能停止因此间隙锁的范围是 (16,32)
所以上述语句在普通索引 a 上的最终加锁范围是 Next-key Lock (8,16] 以及 Gap Lock (16,32)
查询的记录不存在
再来看查询的记录不存在的案例
select * from user where a 18 for update; 结合加锁的两条核心这条语句首先会对普通索引 a 加上 Next-key Lock范围是 (16,32]
但是由于查询的记录 a 18 是不存在的因此 Next-key Lock 会退化为间隙锁即最终在普通索引 a 上的加锁范围是 (16,32)。
案例 4非唯一索引范围查询
范围查询和等值查询的区别在上面唯一索引章节已经介绍过了就是范围查询需要一直向右遍历到第一个不满足条件的记录和唯一索引范围查询不同的是非唯一索引的范围查询并不会退化成 Record Lock 或者 Gap Lock。
select * from user where a 16 and a 18 for update; 先来看语句查询条件的前半部分 a 16因此这条语句最开始要找的第一行是 a 16结合加锁的两个核心需要加上 Next-key Lock (8,16]。虽然非唯一索引 a 16 的这行记录是存在的但此时并不会像唯一索引那样退化成记录锁。
再来看语句查询条件的后半部分 a 18由于是范围查找就会继续往后找第一个不满足条件的记录也就是会找到 id 32 这一行停下来然后加 Next-key Lock (16, 32]。虽然 id 32 不满足 id 18但此时并不会向唯一索引那样退化成间隙锁。
所以上述语句在普通索引 a 上的最终的加锁范围是 Next-key Lock (8, 16] 和 (16, 32]也就是 (8, 32]。
