一些命令
设置是否自动 commit
MySQL 默认事务都是自动提交的
SELECT @@AUTOCOMMIT; SET AUTOCOMMIT = 0;
设置当前会话隔离级别
旧版本:tx_isolation,新版本:transaction_isolation。
ISOLATION_LEVEL
- READ UNCOMMITTED
- READ COMMITTED
- READ REPEATABLE
- SERIALIZABLE
SHOW GLOBAL VARIABLES like '%isolation'; SELECT @@tx_isolation; SELECT @@transaction_isolation; SET SESSION TRANSACTION ISOLATION LEVEL ${ISOLATION_LEVEL};
测试
建表并初始化
DROP TABLE IF EXISTS table1; CREATE TABLE table1( id BIGINT(20) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10) null ); INSERT INTO table1(id,name) VALUES(null,1),(null,2),(null,3),(null,4),(null,5),(null,6),(null,7),(null,8),(null,9),(null,10); SELECT * FROM table1;
赃读问题
脏读:事务 A 读取到事务 B 修改后但回滚的数据,破坏了事务的隔离性
准备
会话隔离级别设置为 READ UNCOMMITED,取消自动提交,并查看是否修改
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET AUTOCOMMIT = 0; SELECT @@TRANSACTION_ISOLATION, @@AUTOCOMMIT;
流程
事务 A | 事务 B | 说明 |
---|---|---|
BEGIN; | 开始一个事务 B | |
UPDATE table1 SET table1.name = '姓名 1' WHERE id = 1; | B 更新记录 | |
SELECT * FROM table1 WHERE id = 1; | 因为 B 后续会发生回滚,所以 A 读取到的是脏数据,发生了赃读 | |
ROLLBACK; | B 回滚,需要手动 commit | |
COMMIT; | B 提交,结束事务 |
不可重读问题
不可重读:事务 A 先后两次读取同一条记录,在此之间事务 B 修改并提交了 A 读取的数据,导致事务 A 两次数据不一致,破坏了一致性
准备
隔离级别:READ COMMITTED,取消自动提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET @@AUTOCOMMIT = 0; SELECT @@TRANSACTION_ISOLATION, @@AUTOCOMMIT;
流程
事务 A | 事务 B | 说明 |
---|---|---|
BEGIN; | 开始一个事务 B | |
SELECT * FROM table1 WHERE id = 1; | A 读取记录,结果为 1,1 | |
UPDATE table1 SET table1.name = '姓名 1' WHERE id = 1; | B 更新记录 | |
SELECT * FROM table1 WHERE id = 1; | A 重复读取记录,结果变为 1,姓名 1,在一个事务中,重复读取同一条数据结果却不一样,发生了不可重读 | |
COMMIT; | B 提交,结束事务 |
幻读
幻读:事务 A 先后两次读取数据集合,在此之间事务 B 插入或删除了记录,导致事务 A 两次读取的集合发生变化,无法进行插入等操作,破坏了一致性
准备
删除 ID7 8 9 的记录
DELETE FROM table1 WHERE id BETWEEN 7 AND 9;
隔离级别:REPEATABLE READ,取消自动提交
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET @@AUTOCOMMIT = 0; SELECT @@TRANSACTION_ISOLATION, @@AUTOCOMMIT;
流程
事务 A | 事务 B | 说明 |
---|---|---|
BEGIN; | 开始一个事务 A | |
SELECT * FROM table1 WHERE id = 7; | A 查询 id 为 7 的数据,发现为空,准备插入一条 id 为 7 的数据 | |
BEGIN; | B 开始一个事务 | |
SELECT * FROM table1 WHERE id = 7; | B 查询 id 为 7 的数据,发现为空,也准备插入一条 id 为 7 的数据 | |
INSERT INTO table1 VALUES(7,7); | B 插入一条 id 为 7 的数据 | |
COMMIT; | B 提交,结束事务 | |
INSERT INTO table1 VALUES(7,7); | A 插入一条 id 为 7 的数据,此时却提示主键重复 | |
SELECT * FROM table1 WHERE id = 7; | A 颇为吃惊,又查了一下 id 为 7 的数据,发现确实为空 | |
INSERT INTO table1 VALUES(7,7); | A 又试图插入,还是失败 | |
COMMIT; | A 骂骂咧咧地结束了事务,今天出现幻觉了 |