MySQL 事务隔离与锁的测试

于 2022-06-15 10:27:46 发布
阅读量155 收藏 2
点赞数 1
分类专栏： 笔记 分享 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fl_zxf/article/details/125292077
版权
  • 最新使用 MySQL 8.0.29 测试

修改事务隔离级别

  • 注：Windows 上的测试最好用自带的 mysql.exe 客户端
    • 不要用 GUI，GUI 可能会使语句单独执行
    • 使用 BEGIN; 和 COMMIT; 就可以测试
  • 改变全局事务隔离级别时，当前 session 需退出重进才有效
    • 设置 SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    • 级别 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
  • 改当前级别 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;；要加 SESSION 关键字，否则不生效
  • 查看当前级别 SHOW VARIABLES LIKE 'transaction_isolation';

表创建

CREATE TABLE `test` (
 `id` INT NOT NULL, 
 `age` INT DEFAULT NULL, 
 `enname` VARCHAR(255) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;

顺序读

  1. 用 Next-Key 锁 实现

  2. T1 先执行读，给相关区间加读锁；后续的事务可以读，但 CUD 的时候会被阻塞

T1T2Main
–-SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
–-TRUNCATE TABLE test;
–-INSERT INTO test VALUES(1, 10, 'a'), (5, 20, 'b'), (8, 30, 'c'), (10, 40, 'd');
START TRANSACTION;START TRANSACTION;-
SELECT * FROM test WHERE id <= 5; // 锁 (-∞, 5]--
–C: INSERT INTO test VALUES(6, 35, 'c'); // 成功-
–C: INSERT INTO test VALUES(4, 25, 'c'); // 阻塞-
–U: UPDATE test SET enname = 'a' WHERE id = 8; // 成功-
–U: UPDATE test SET enname = 'a' WHERE id = 5; // 阻塞-
–D: DELETE FROM test WHERE id = 8; // 成功-
–D: DELETE FROM test WHERE id = 5; // 阻塞-
–T: COMMIT; // 提交数据-
SELECT * FROM test WHERE id <= 5; // 不存在幻读--
  1. T1 先执行 CUD，给相关区间加写锁；后续的事务 CRUD 的时候都会被阻塞
T1T2Main
–-SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
–-TRUNCATE TABLE test;
–-INSERT INTO test VALUES(1, 10, 'a'), (5, 20, 'b'), (8, 30, 'c'), (10, 40, 'd');
START TRANSACTION;START TRANSACTION;-
UPDATE test SET enname = 'a' WHERE id <= 5; // 锁 (-∞, 5]--
–R: SELECT * FROM test WHERE id > 5; // 成功-
–R: SELECT * FROM test WHERE id >= 5; // 阻塞-
–C: INSERT INTO test VALUES(6, 35, 'c'); // 成功-
–C: INSERT INTO test VALUES(4, 25, 'c'); // 阻塞-
–U: UPDATE test SET enname = 'a' WHERE id = 8; // 成功-
–U: UPDATE test SET enname = 'a' WHERE id = 5; // 阻塞-
–D: DELETE FROM test WHERE id = 8; // 成功-
–D: DELETE FROM test WHERE id = 5; // 阻塞-

可重复读

  1. 用 Next-Key 锁 实现

  2. T1 先执行读，并没有给相关区间加读锁，只是生成快照；后续的事务可以 CRUD，并不会被阻塞

T1T2Main
–-SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
–-TRUNCATE TABLE test;
–-INSERT INTO test VALUES(1, 10, 'a'), (5, 20, 'b'), (8, 30, 'c'), (10, 40, 'd');
START TRANSACTION;START TRANSACTION;-
SELECT * FROM test WHERE id <= 5; // 不加锁--
–C: INSERT INTO test VALUES(4, 15, 'c'); // 成功-
–U: UPDATE test SET enname = 'a' WHERE id = 5; // 成功-
–D: DELETE FROM test WHERE id = 5; // 成功-
–T: COMMIT; // 提交数据-
SELECT * FROM test WHERE id <= 5; // 不存在幻读--
  1. T1 先执行 CUR，给相关区间加写锁；后续的事务 CUD 的时候都会被阻塞，但读不加锁
T1T2Main
–-SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
–-TRUNCATE TABLE test;
–-INSERT INTO test VALUES(1, 10, 'a'), (5, 20, 'b'), (8, 30, 'c'), (10, 40, 'd');
START TRANSACTION;START TRANSACTION;-
UPDATE test SET enname = 'a' WHERE id <= 5; // 锁 (-∞, 5]--
–R: SELECT * FROM test; // 成功-
–C: INSERT INTO test VALUES(6, 35, 'c'); // 成功-
–C: INSERT INTO test VALUES(4, 25, 'c'); // 阻塞-
–U: UPDATE test SET enname='a' WHERE id = 8; // 成功-
–U: UPDATE test SET enname='a' WHERE id = 5; // 阻塞-
–D: DELETE FROM test WHERE id = 8; // 成功-
–D: DELETE FROM test WHERE id = 5; // 阻塞-

提交读

  1. 用记录锁实现

  2. T1 先执行读，并不会给相关记录加读锁；后续的事务可以 CRUD，并不会被阻塞

T1T2Main
–-SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
–-TRUNCATE TABLE test;
–-INSERT INTO test VALUES(1, 10, 'a'), (5, 20, 'b'), (8, 30, 'c'), (10, 40, 'd');
START TRANSACTION;START TRANSACTION;-
SELECT * FROM test WHERE id <= 5; // 不加锁--
–C: INSERT INTO test VALUES(4, 15, 'c'); // 成功-
–U: UPDATE test SET enname = 'a' WHERE id = 5; // 成功-
–D: DELETE FROM test WHERE id = 5; // 成功-
–T: COMMIT; // 提交数据-
SELECT * FROM test WHERE id <= 5; // 存在不可重复读、幻读--
  1. T1 先执行 CUR，给相关行记录加写锁；后续的事务 CUD 的时候都会被阻塞，但读不加锁
T1T2Main
–-SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
–-TRUNCATE TABLE test;
–-INSERT INTO test VALUES(1, 10, 'a'), (5, 20, 'b'), (8, 30, 'c'), (10, 40, 'd');
START TRANSACTION;START TRANSACTION;-
UPDATE test SET enname = 'a' WHERE id <= 5; // 锁第1行和第5行记录--
–R: SELECT * FROM test; // 成功-
–C: INSERT INTO test VALUES(4, 15, 'c'); // 成功-
–U: UPDATE test SET enname = 'a' WHERE id = 8; // 成功-
–U: UPDATE test SET enname = 'a' WHERE id = 5; // 阻塞-
–D: DELETE FROM test WHERE id = 8; // 成功-
–D: DELETE FROM test WHERE id = 5; // 阻塞-

未提交读

  1. 用记录锁实现

  2. T1 先执行读，并不会给相关记录加读锁；后续的事务可以 CRUD，并不会被阻塞

T1T2Main
–-SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
–-TRUNCATE TABLE test;
–-INSERT INTO test VALUES(1, 10, 'a'), (5, 20, 'b'), (8, 30, 'c'), (10, 40, 'd');
START TRANSACTION;START TRANSACTION;-
SELECT * FROM test WHERE id <= 5; // 不加锁--
–C: INSERT INTO test VALUES(4, 15, 'c'); // 成功-
SELECT * FROM test WHERE id <= 5; // 存在幻读--
–U: UPDATE test SET enname = 'a' WHERE id = 5; // 成功-
–D: DELETE FROM test WHERE id = 5; // 成功-
–T: -- COMMIT; // 不提交数据-
SELECT * FROM test WHERE id <= 5; // 存在脏读、不重复读、幻读--
  1. T1 先执行 CUR，给相关行记录加写锁；后续的事务 CUD 的时候都会被阻塞，但读不加锁
T1T2Main
–-SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
–-TRUNCATE TABLE test;
–-INSERT INTO test VALUES(1, 10, 'a'), (5, 20, 'b'), (8, 30, 'c'), (10, 40, 'd');
START TRANSACTION;START TRANSACTION;-
UPDATE test SET enname = 'a' WHERE id <= 5; // 锁第1行和第5行记录--
–R: SELECT * FROM test; // 成功-
–C: INSERT INTO test VALUES(4, 15, 'c'); // 成功-
–U: UPDATE test SET enname = 'a' WHERE id = 8; // 成功-
–U: UPDATE test SET enname = 'a' WHERE id = 5; // 阻塞-
–D: DELETE FROM test WHERE id = 8; // 成功-
–D: DELETE FROM test WHERE id = 5; // 阻塞-

总结

隔离级别RW - 先读RW - 后 CUR]WR - 先 CURWR - 后读
顺序读给区间加 Next-Key 读锁读任意执行，CUD 阻塞给区间加 Next-Key 写锁CURD 都阻塞
可重复读不加锁任意执行加 Next-Key 锁读任意执行，CUD 阻塞
提交读不加锁任意执行给行加写锁（记录锁）读任意执行，CUD 阻塞
未提交读不加锁任意执行给行加写锁（记录锁）读任意执行，CUD 阻塞

注：

  1. 如果是id = 5的操作，只是用行锁
  2. 不同隔离级别之间也是按锁规则执行（如 T1 顺序读，T2 未提交读）
  3. MySQL 默认是隐式添加事务操作，即 T1 事务开启未释放锁，用户 T2 未开启事务时的操作也会阻塞

非索引列上操作

  • 示例：
T1:
  UPDATE test SET enname = 'a' WHERE age <= 10;
T2: 
  INSERT INTO test VALUES(9, 11, 'c'); -- 检验 Next-Key 锁
  INSERT INTO test VALUES(9, 41, 'c'); -- 检验表锁
  UPDATE test SET enname = 'a' WHERE age > 20; -- 检验表锁
  UPDATE test SET enname = 'a' WHERE age < 20; -- 检验行锁
级别锁情况
顺序读锁表
可重复读锁表
提交读锁行
未提交读锁行

注

  • 锁表说明：没索引，不好计算 Next-Key，因此锁表
Java-峰
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql事务的隔离级别与测试
EquinoxPrime的博客
10-10 261
Mysql事务的隔离级别与测试 1、打开mysql的命令行，将自动提交事务给关闭 --查看是否是自动提交 1表示开启，0表示关闭 select @@autocommit; --设置关闭 set autocommit = 0; 2、数据准备 --创建数据库 create database tran; --切换数据库 两个窗口都执行 use tran; --准备数据 create table psn(id int primary key,name varchar(10)) engine=innodb; --
MySQL数据库事务隔离级别详解
09-09
MySQL数据库的事务隔离级别是数据库管理系统中用于控制事务并发处理时如何查看和修改数据的一系列标准。这些级别有助于防止在多事务环境中出现诸如脏读、不可重复读和幻读等并发问题。下面是四个不同的事务隔离级别...
MySQL中的事务和锁简单测试
congbao6525的博客
10-08 127
一直以来，对于MySQL中的事务和锁的内容是浅尝辄止，没有花时间了解过，在一次看同事排查的故障中有个问题引起了我的兴趣，虽然过去了很久，但是现在简单总结一下还是有一些收获。 首先我们初始化数据，事务的隔离级别还是MySQL默认...
mysql事务测试
zy_crazy_code的博客
12-10 177
1、打开mysql的命令行，将自动提交事务给关闭 --查看是否是自动提交 1表示开启，0表示关闭 --设置关闭 mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected
MYSQL 事务测试
weixin_34189116的博客
01-31 99
mysql 事务测试 创建张表 lock1 增加字段 id,name 。 增加两条记录 1,a 2,b 启动第一个会话 BEGIN; update lock1 set name='c' where id=1; update lock1 set name='d' where id=2; 启动第二个会话 BEGIN; update lock1 set name='c' wh...
解读MySQL事务的隔离级别和日志登记模式选择技巧
03-04
MySQL的四种事务隔离级别：Read-uncommitted、Read-committed、Repeatable-read、Seriailizable，相信大家都清楚各自异同，不清楚的朋友可以查看另外一篇技术文章：MySQL_InnoDB之事务与锁详解。但是对于第二类、第...
MySQL四种事务隔离级别详解
01-21
本文实验的测试环境：Windows 10+cmd+MySQL5.6.36+InnoDB 一、事务的基本要素（ACID） 　1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会...
MySQL数据库-事务、锁及SQL优化
最新发布
08-25
MySQL数据库的事务、锁以及SQL优化是数据库管理中至关重要的概念，它们对于数据一致...在实际操作中，应根据业务需求和性能测试结果来调整事务隔离级别、选择合适的锁定策略，并优化SQL语句，以实现最优的数据库性能。
mysql+Spring数据库隔离级别与性能分析
12-15
事务隔离级别与锁机制紧密相关。在不同隔离级别下，数据库可能使用行级锁、页级锁或表级锁来确保数据一致性。例如，`REPEATABLE_READ` 隔离级别下，MySQL 使用 Next-Key 锁来防止幻读，而 `READ_COMMITTED` 只在读取...
MYSQL测试事务
chinoukin的博客
09-13 2083
mysql默认的引擎是InnoDB，是支持事务的，如下： 现在我们演示事务内外的更新与查询操作的问题，有下表：   表中的数据： 开启事务(begin)，执行更新语句，将'chinoukin'更新为'cyq'，不提交(commit)事务： 事物内查询：  可以发现，当条件【where author='chinoukin'】查不到数据，而查到的数据author都是'cyq' ...
[linux Tcmd] sh source
weixin_30907935的博客
11-30 112
chmod +x test.sh ./test.sh source命令： source filepath/filename.sh ./filepath 功能：使当前shell读入路径为filepath的shell文件/filename.sh并依次执行文件中的所有语句 source filepath 与 sh filepath 、./filepath的区别： 当shell脚本具有可执行权限...
测试MySQL的事务加锁等待和锁释放
黑夜开发者的博客
12-20 1530
本文测试的是数据行锁，请确保表存储引擎在InnoDB的状态下，测试的数据库事务隔离级别为可重复度。 1. 初始表结构和数据如下 2. 分别打开两个查询窗口（就是等下会开启两个不同的事务） 在第一个窗口中先执行前两句代码，对第一行数据加锁 执行第二个窗口的代码，会发现下面的更新语句一直在等待 运行第一个窗口的第三行代码，提交第一个事务，可以看到第二个窗口的更新语句才执行完成。 3. 演示部...
测试mysql事物_测试MySQL事务管理
weixin_35545345的博客
01-28 165
1.MySQL 版本mysql> selectversion();+------------+| version() |+------------+| 5.5.37-log |+------------+1 row in set (0.00 sec)2.创建测试表mysql> create table test_trans(id int ,name_ varchar(10));Que...
mysql怎么进行事务测试_如何测试MySQL事务?
weixin_32187037的博客
01-25 441
也许您可以重构第一个示例并使用一些DB访问包装类?在这个包装类中,您可以有一个变量$normalcommit=true;以及设置$normalCommit变量的方法setCommitmode()。还有一个方法commit(),如果($normalCommit==true),它将提交。或者甚至有一个变量$failtransaction,它调用mysql_query(“rollback”);如果您愿意...
MySQL事务
Owen_L_Y的博客
01-06 1495
MySQL事务主要用于处理操作量大、复杂度高的数据（如：在数人员管理系统中，删除某个人的基本信息的同时，也要删除其相关的信息）。这样，有一条条数据库操作语句就构成了事务。 MySQL数据库中只有使用的是 innoDB存储引擎 的表才支持 事务。 事务可用来维护数据库的完整性，sql语句要么都执行完成，要么不执行。 事务用来管理delete、insert、update语句。 一、事务需要满足的4...
MySQL攻略 - MySQL事务详解、事务隔离级别测试
qq_39123467的博客
02-19 705
这个“事务”操作，有点类似于java中的“线程”
MySQL MDL锁深度解析与问题分析
死锁检测机制会识别并解决这些情况，但有时需要手动干预，比如通过调整事务的顺序或设置适当的事务隔离级别来避免死锁。 MDL锁是MySQL并发控制的重要组成部分，对于理解和优化数据库性能至关重要。深入理解MDL锁的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值