mysql 事务 serializable 对比 repeatableread 特殊场景的问题记录

2024年 4月 26日 36.2k 0

前置重要知识点:行锁是加在索引上的,如果字段没有索引,或者有索引,没有命中,会变成表锁,所以测试之前先保证对应字段有索引哦,避免测试结果有偏差

事务最高级别: serializable

设置当前会话级别:

set session transaction isolation level serializable
查询当前事务等级
select @@transaction_isolation;

一写一读加锁情况1:只要有session对同一行数据 更新,就会立刻加锁,其它事务等放锁

以下假设session1 先 update:

并行请求:session1
begin;
直接对[当前行]加写锁
update think_user set name = "301" where phone = "xxxxxxxxxxx";


并行请求:session2
begin;
这里被 session1 阻塞, 等 session1 commit 放锁
select * from think_user where phone = "xxxxxxxxxxx";

同时写加锁情况2:先拿锁的,先执行,另外一个等待放锁

以下假设session1 先 update:

并行请求:session1
begin;
直接对[当前行]加写锁
update think_user set name = "301" where phone = "xxxxxxxxxxx";
提交放锁
commit;

并行请求:session2
begin;
阻塞:等待session1 commit 提交放锁
update think_user set name = "301" where phone = "xxxxxxxxxxx";

同时读写加锁情况3:都执行查询,查询不会立刻加锁,会加一个共享读锁。谁先拿锁,谁就有更新权,后拿锁的也想更新当前行就会触发死锁退出(都有读锁,session1 要加写锁等 session2 释放读锁,这个时候如果session2 也要加写锁,就会导致互等对方放读锁,造成死锁退出),谁后拿锁,谁退出,另外一个业务正常继续执行

以下假设session1 先 select, 先update:

并行请求:session1
begin;
上读锁,可查出数据
select * from think_user where phone = "xxxxxxxxxxx";
执行到这 -> 等待,因为被 session2 加了读锁, 写操作不能执行,得等session2放锁
update think_user set name = "301" where phone = "xxxxxxxxxxx";
session2 死锁退出,自动放锁,session1 业务正常往下执行
commit;


并行请求:session2
begin;
上读锁,可查出数据
select * from think_user where phone = "xxxxxxxxxxx";

执行到这 -> 死锁退出,自动回滚并放锁
update think_user set name = "302" where phone = "xxxxxxxxxxx";

表格演示下更清晰:
image.png

先读后写加锁情况4:先拿锁的业务,不受影响,后拿锁的自动死锁退出

以下假设session1 先 select, session12:后update:

并行请求:session1
begin;
上读锁,可查出数据
select * from think_user where phone = "xxxxxxxxxxx";
执行到这 -> 无阻塞,直接成功。 session2 自动死锁退出
update think_user set name = "301" where phone = "xxxxxxxxxxx";
commit;


并行请求:session2
begin;
执行到这 -> 等待 session1 放读锁
update think_user set name = "302" where phone = "xxxxxxxxxxx";
session1 中有写操作,直接让 session2 死锁退出

思考:上面同时写加锁情况2 是排队等待拿锁,这里为什么不是等待放锁而是死锁退出呢?

回复上面思考结论:
同时写没触发死锁是因为他们没有互相等,你等我放,我等你放,是触发死锁的因素
session1 先上读锁, session2 上了写锁,然后阻塞等 session1放读锁。 这时候session1 往下又执行了 写操作上了一把写锁,这时候session1的写锁 又被 session2的写锁阻塞了, 造成你等我放锁,我等你放锁,结果就是 后拿锁的 session2直接被判定死锁退出

总结:serializable安全在哪?select语句会加锁,保证并行场景先查询后更新的业务只有一个能更新。 我们具体到业务场景: 你和女朋用同一个账号在不同设备上计划抢购买东西,账户余额1000买了2个商品, 女朋友付款800块,你付款10块,假设前后2次同时并行快速付款,业务处理逻辑很慢的场景。

image.png

如图,如果让后面覆盖掉前面结果,会导致花了 10块买了2件商品,产生事故
serializable 事务等级:处理这个场景结果就是 session2的购买会失败,死锁退出,重新购买
所以:涉及到钱的我觉得一定要用最高事务等级

来对比一下repeatable-read这种情况会怎么样?

事务级别: repeatable-read

set session transaction isolation level repeatable read;
查询当前事务等级
select @@transaction_isolation;

同时读写serializable 会死锁退出, 而repeatable-read 就会锁等待
image.png

这个案例让repeatable-read 碰到就出问题了,它会后面事务结果覆盖前面结果
image.png

repeatable-read 的查询默认是不上锁的,所以并不会造成死锁,排队顺序拿锁。

可以手动加锁来解决这种问题:for update

image.png

依赖写业务人的约束,如果另外一个业务不加 for update ,依然可能会出问题

image.png

总结:特别重要的数据更新,涉及到钱或其它不能接受一点可能性差错的,最好都用serializable事务等级。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论