接网站建设 网站设计,搭建舞台,北京企业网站备案需要多久,中国银行网站建设mysql锁机制及原理1.隔离级别2.实践2.1查看事务隔离级别2.2 设置隔离级别2.3 不可重复读2.4 幻读3.幻读怎么解决3.1 Record Lock3.2 Gap Lock3.3 Next-Key Lock引用#xff1a;https://blog.csdn.net/xinyuan_java/article/details/1284932051.隔离级别
SERIALIZABLE(序列化)…
mysql锁机制及原理1.隔离级别2.实践2.1查看事务隔离级别2.2 设置隔离级别2.3 不可重复读2.4 幻读3.幻读怎么解决3.1 Record Lock3.2 Gap Lock3.3 Next-Key Lock引用https://blog.csdn.net/xinyuan_java/article/details/1284932051.隔离级别
SERIALIZABLE(序列化) 如果隔离级别为序列化则用户之间通过一个接一个顺序地执行当前的事务这种隔离级别提供了事务之间最大限度的隔离。 REPEATABLE READ 在可重复读在这一隔离级别上事务不会被看成是一个序列。不过当前正在执行事务的变化仍然不能被外部看到也就是说如果用户在另外一个事务中执行同条 SELECT 语句数次结果总是相同的。因为正在执行的事务所产生的数据变化不能被外部看到。 READ COMMITTED READ COMMITTED 隔离级别的安全性比 REPEATABLE READ 隔离级别的安全性要差。处于 READ COMMITTED 级别的事务可以看到其他事务对数据的修改。也就是说在事务处理期间如果其他事务修改了相应的表那么同一个事务的多个 SELECT 语句可能返回不同的结果。 READ UNCOMMITTED READ UNCOMMITTED 提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外处于这个隔离级的事务可以读到其他事务还没有提交的数据如果这个事务使用其他事务不提交的变化作为计算的基础然后那些未提交的变化被它们的父事务撤销这就导致了大量的数据变化。 在 MySQL 数据库中默认的事务隔离级别是 REPEATABLE READ。
2.实践
2.1查看事务隔离级别
MySQL8 之前使用如下命令查看 MySQL 隔离级别
SELECT GLOBAL.tx_isolation, tx_isolation;MySQL8 开始通过如下命令查看 MySQL 默认隔离级别
SELECT GLOBAL.transaction_isolation, transaction_isolation;2.2 设置隔离级别
通过如下命令可以修改隔离级别建议开发者在修改时修改当前 session 隔离级别即可不用修改全局的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED2.3 不可重复读
不可重复读是指一个事务先后读取同一条记录但两次读取的数据不同称之为不可重复读。具体操作步骤如下操作之前先将两个账户的钱都恢复为1000
首先打开两个查询会话 A 和 B 并且将 A 的数据库事务隔离级别设置为 READ UNCOMMITTED。具体 SQL 参考上文这里不赘述。
接下来执行如下 SQL SQL1 执行结果如下 SQL2 执行结果如下
SQL3 执行结果如下
可以看到在 SessionA 中查询同一条记录多次查询最终的结果可能不一样这就是不可重复读。
和脏读的区别在于脏读是看到了其他事务未提交的数据而不可重复读是看到了其他事务已经提交的数据由于当前 SQL 也是在事务中因此有可能并不想看到其他事务已经提交的数据。
2.4 幻读
幻读和不可重复读非常像看名字就是产生幻觉了。
CREATE TABLE user (id int(11) unsigned NOT NULL AUTO_INCREMENT,username varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,age int(11) NOT NULL,PRIMARY KEY (id),UNIQUE KEY age (age)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;id 是主键age 是唯一非空索引。 表中数据如下 现在我们有两个会话 Session A 和 Session BSession A 隔离级别是 READ UNCOMMITTEDSession B 是默认的隔离级别执行的 SQL 如下 注意在 SQL1 中用了一个当前读按理说它会锁住 age 大于 80 的记录其实也确实锁住了 89 和 99 这样的值但是对于一开始就不存在的 100 就没能锁住了这就导致在 SQL3 执行的时候看到了 SQL2 插入的语句。
幻读幻读专指看到了新插入的行。。
READ COMMITTED 主要解决了脏读的问题对于不可重复读和幻象读则未解决。
REPEATABLE READ 进一步解决了不可重复读的问题对于幻读问题REPEATABLE READ 也有一个自己的方案。
SERIALIZABLE 提供了事务之间最大限度的隔离在这种隔离级别中事务一个接一个顺序的执行不会发生脏读、不可重复读以及幻象读问题最安全。
3.幻读怎么解决
脏读、不可重复读这两个问题通过修改事务的隔离级别就可以解决那么幻读该如何解决呢MySQL 中提出了 Next-Key Lock 来解决幻读问题当然这个方案也只在 REPEATABLE READ 这个隔离级别下生效。要把这个问题理解透你得搞明白三把锁Record Lock、Gap Lock 以及 Next-Key Lock。
3.1 Record Lock
Record Lock 也就是我们所说的记录锁记录锁是对索引记录的锁注意它是针对索引记录即它只锁定记录这一行数据。
例如如下一条 SQL
select * from user where id1 for update;注意id 是索引id 如果不是索引上面这条 SQL 所加的排他锁就不是一个 Record Lock。
我们来看如下一个例子 首先我们将系统变量 innodb_status_output_locks 设置为 ON如下 接下来我们执行如下 SQL锁定一行数据此时会自动为表加上 IX 锁 接下来我们在一个新的会话中执行如下指令来查看 InnoDB 存储引擎的情况
show engine innodb status\G输出的信息很多我们重点关注 TRANSACTIONS如下 可以看到
TABLE LOCK table test08.user trx id 3564804 lock mode IX这句就是说事务 id 为 3564804 的事务为 user 表添加了意向排他锁IX。RECORD LOCKS space id 851 page no 3 n bits 80 index PRIMARY of table test08.user trx id 3564804 lock_mode X locks rec but not gap这个就是一个锁结构的记录这里的索引是 PRIMARY加的锁也是正儿八经的记录锁not gap。
看到了 LOCKS REC BUT NOT GAP就说明这是一个记录锁。
那么这个 Record Lock 和我们之前所讲的 S 锁以及 X 锁有什么区别呢S 锁是共享锁X 锁是排他锁当我们加 S 锁或者 X 锁的时候如果用到了索引锁加在了某一条具体的记录上那么这个锁也是一个记录锁其实记录锁S 锁X 锁概念有一些重复的地方但是描述的重点不一样。
或者也可以理解为记录锁又细分为 S 锁和 X 锁它们之间的兼容性如下图 3.2 Gap Lock
Gap Lock 也叫做间隙锁它的存在可以解决幻读问题另外需要注意Gap Lock 也只在 REPEATABLE READ 隔离级别下有效。先来看看什么是幻读我们来看如下一个表格 有两个会话A 和 B先在会话 A 中开启事务然后查询 age 为 99 的用户总数注意使用当前读因为在默认的隔离级别下默认的快照读并不能读到其他事务提交的数据至于快照读和当前读的区别大家参考S 锁与 X 锁当前读与快照读。当会话 A 中第一次查询过后会话 B 中向数据库添加了一行记录等到会话 A 中第二次查询的时候就查到了和第一次查询不一样的结果这就是幻读注意幻读专指数据插入引起的不一致。
在 MySQL 默认的隔离级别 REPEATABLE READ 下上图所描述的情况无法复现。无法复现的原因在于在 MySQL 的 REPEATABLE READ 隔离级别中它已经帮我们解决了幻读问题解决的方案就是 Gap Lock。
大家想想之所以出现幻读的问题是因为记录之间存在缝隙用户可以往这些缝隙中插入数据这就导致了幻读问题如下图
现在 Gap Lock 间隙锁就是要把这些记录之间的间隙也给锁住间隙锁住了就不用担心幻读问题了这也是 Gap Lock 存在的意义。
给一条记录加 Gap Lock是锁住了这条记录前面的空隙例如给 id 为 1 的记录加 Gap Lock锁住的范围是 (-∞,1)给 id 为 3 的记录加 Gap Lock锁住的范围是 (1,3)那么 id 为 10 后面的空隙怎么锁定呢MySQL 提供了一个 Supremum 表示当前页面中的最大记录所以最后针对 Supremum 锁住的范围就是 (10,∞)这样所有的间隙都被覆盖到了由于锁定的是间隙所以都是开区间。
那么我们怎么样能看到 Gap Lock 呢我给大家举一个简单的例子假设我有如下一张表
CREATE TABLE user (id int(11) unsigned NOT NULL AUTO_INCREMENT,username varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,age int(11) DEFAULT NULL,PRIMARY KEY (id),KEY age (age)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;一个简单的表id 是主键age 是普通索引表中有如下几条记录
接下来我们执行如下 SQL锁定一行数据此时也会产生间隙锁 接下来我们在一个新的会话中执行如下指令来查看 InnoDB 存储引擎的情况
show engine innodb status\G 红色框选中的就是一个间隙锁的加锁记录可以看到在某一个记录之前加了间隙锁。
这就是间隙锁。非常重要的一点需要大家牢记Gap Lock 只在 REPEATABLE READ 隔离级别下有效。
3.3 Next-Key Lock
以下内容都是基于 MySQL 默认的隔离级别 REPEATABLE READ。
如果我们既想锁定一行又想锁定行之间的记录那么就是 Next-Key Lock 了换言之Next-Key Lock 是 Record Lock 和 Gap Lock 的结合体。
正常来说我们加行锁的基本单位就是 Next-Key Lock即既有记录锁又有间隙锁但是有时候 Next-Key Lock 会退化我们通过几个简单的例子来分析一下。
首先我们来看看 Next-Key Lock 的加锁规则
锁的范围是左开右闭。如果是唯一非空索引的等值查询Next-Key Lock 会退化成 Record Lock。普通索引上的等值查询向后遍历时最后一个不满足等值条件的时候Next-Key Lock 会退化成 Gap Lock。
我们通过几个简单的例子来分析下。
2.3.1 唯一非空索引
假设我有一个学生表学生表中有学生的姓名和成绩如下
CREATE TABLE student (id int(11) unsigned NOT NULL AUTO_INCREMENT,name varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,score double NOT NULL,PRIMARY KEY (id),UNIQUE KEY score (score)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;id 是主键score 是成绩其中 score 是唯一非空索引。
现在表中有如下数据 假设我们执行如下 SQL 在这个例子中由于 score 是唯一非空索引所以 Next-Key Lock 会退化成 Record Lock换句话说这行 SQL 只给 score 为 90 的记录加锁不存在 Gap Lock即我们新开一个会话插入一条 score 为 88 的记录也是 OK 的。
不过这里有一个特例如果锁定的是一个不存在的记录那么也会产生间隙锁例如下面这个 由于并不存在 score 为 91 的记录所以这里会产生一个范围为 (90,95) 的间隙锁我们执行如下 SQL 可以验证 可以看到90.1、94.9 都会被阻塞我按了 Ctrl C所以大家看到查询终止。
90、95 则不符合唯一非空索引的条件。
95.1 则可以插入成功。
没问题。
2.3.2 非空索引
现在我们重新开始将 score 索引改为普通索引如下
CREATE TABLE student (id int(11) unsigned NOT NULL AUTO_INCREMENT,name varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,score double NOT NULL,PRIMARY KEY (id),KEY score (score)
) ENGINEInnoDB AUTO_INCREMENT8 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;数据还是跟前面一样此时我们来执行如下 SQL 我们来分析下。
此时要锁定的是 id 为 90 的记录那么首先加间隙锁上一个 score 为 89所以这次加的间隙锁范围是 (89,90)同时要锁定 id 为 90 的记录所以进一步优化为 (89,90]。
同时这里还有一条规则就是满足条件的上一条记录也需要被锁住所以最终的锁范围就是 [89,90]。
由于 score 不是唯一性索引所以还需要继续向后查找找到的下一条记录是 95由于此时 Next-Key Lock 会退化成 Gap Lock所以锁定的范围是 (90,95)。综上最终锁定的范围是 [89,95)。
接下来我们可以新开一个会话我们分别尝试添加如下数据看看是否能够添加成功
可以看到score 为 88 是可以的但是为 89.1 就不行。 score 为 95 也是可以的但是为 94.9 就不行。 再试一下 89 是否可以
说明我们上面分析的加锁范围是正确的。
再来看如下一条 SQL 跟前面的案例相比这次多了 limit 1limit 1 表示只要一条记录所以这次查找到 90 之后就不会再往后查找了那么最终的锁就是间隙锁一个记录锁最终的范围就是 [89,90]。
此时新开一个会话分别插入 score 为 88.9、89、90、91 的 记录验证我们上面所分析的加锁范围 88.9 和 89 的插入结果跟我们预想的一致。 可以看到这里 90 也能插入能插入的原因是因为缺乏 90 往后的间隙锁。 参考https://blog.csdn.net/xinyuan_java/article/details/128493205
