MySQL事务-基础知识
# 事务基本操作
# 查看事务是否是自动提交 （默认是自动提交）
show variables like 'autocommit';# 查看事务的隔离级别
show variables like 'transaction_isolation';或者
select @@SESSION.transaction_isolation;# 修改事务隔离级别，默认是: REPEATABLE READ, 其他还有 READ COMMITTED, READ UNCOMMITTED, SERIALIZABLE
全局修改
# 修改 my.cnf，在 [mysqld] 下配置 （长期生效，需要重启） transaction-isolation=READ-COMMITTED # 或者直接执行 sql， 热更新（其他正在运行的 SESSION 重连后才生效，SERVER 重启后失效） SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;会话修改
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 或者 SET @@SESSION.transaction_isolation = 'REPEATABLE-READ';仅下一个事务生效 (当前事务正在执行，不可以设置)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 或者 SET @@transaction_isolation = 'REPEATABLE-READ'
# 开启事务
START TRANSACTION或者简写为：BEGIN# 提交事务
COMMIT# 回滚事务
ROLLBACK
# 数据准备
-- DDL
CREATE TABLE `t1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--- 写一段存储过程，插入随机数据
DELIMITER $$
CREATE PROCEDURE generate_data_t1()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE total_rows INT DEFAULT 100000;
SET autocommit=0;
WHILE i < total_rows DO
INSERT INTO t1 (name) VALUES (CONCAT('Name', i));
SET i = i + 1;
END WHILE;
COMMIT;
END $$
DELIMITER ;
--- 删除存储过程
DROP PROCEDURE generate_data_t1;
-- DML
--- 生成随机数据
CALL generate_data_t1();
# 并发事务问题
| 问题 | 描述 |
|---|---|
| 脏读 | 事务 A 读取到了事务 B 尚未提交的数据 |
| 不可重复读 | 在同一事务中，前后两次相同 SQL 查询结果不一致，强调的是数据结果不一致，主要针对修改、删除操作 |
| 幻读 | 在同一事务中，前后两次相同 SQL 查询的结果集不一致，强调的是结果数量不一致，主要针对新增、删除操作 |
# 事务隔离级别
| 级别 | 描述 | 解决问题 |
|---|---|---|
| 读未提交（Read Uncommitted） | 事务 A 可以读取到事务 B 尚未提交的数据 | |
| 读已提交（Read Committed） | 一个事务只能读取到另一个事务已提交的数据，可能有不可重复读和幻读的问题 | 脏读 |
| 可重复读（Repeatable Read） | 一个事务开启后，前后两次数据数据的结果是一致的。 | 不可重复读，幻读（Innodb） |
| 串行化（Serializable） | 每个事务都是顺序化执行，隔离级别最高，性能较差 | 解决以上问题，但性能较差 |
# 分场景数据演示
# 读未提交（脏读）
会话1 和 会话2 都设置事务隔离级别为：读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
会话1 执行第一次查询
-- 关闭事务自动提交 set autocommit = 0; -- 开启事务 begin; -- 查询 id=1 的数据 select * from t1 where id = 1; +----+-------+---------------------+ | id | name | updated_at | +----+-------+---------------------+ | 1 | Name0 | 2023-08-13 12:28:00 | +----+-------+---------------------+会话2 更新数据
-- 关闭事务自动提交 set autocommit = 0; -- 开启事务 begin; -- 更新 id=1 的数据 update t1 set name = 'test_uncommitted' where id = 1;会话1 再次查询，此时查到了会话2 未提交的数据
select * from t1 where id = 1; +----+------------------+---------------------+ | id | name | updated_at | +----+------------------+---------------------+ | 1 | test_uncommitted | 2023-08-13 12:59:27 | +----+------------------+---------------------+当会话2 执行回滚时，同样会话1 再去查询此条数据，得到的结果也将是回滚后的结果
# 读已提交（不可重复读）
会话1 和 会话2 都设置事务隔离级别为：读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
会话1 执行第一次查询
-- 开启事务 begin; -- 查询 id=1 的数据 select * from t1 where id = 1; +----+-------+---------------------+ | id | name | updated_at | +----+-------+---------------------+ | 1 | Name0 | 2023-08-13 13:09:00 | +----+-------+---------------------+会话2 更新数据
-- 开启事务 begin; -- 更新 id=1 的数据 update t1 set name = 'test_read_committed' where id = 1;会话1 再次查询数据，由于 会话2 暂未提交，所以此时查询不到会话2 更新的数据
select * from t1 where id = 1; +----+-------+---------------------+ | id | name | updated_at | +----+-------+---------------------+ | 1 | Name0 | 2023-08-13 13:09:00 | +----+-------+---------------------+会话2 此时提交数据
-- 提交事务 commit;会话1 此时再去查询此条数据时将会查询到会话2 更新的数据，产生了不可重复读的问题
select * from t1 where id = 1; +----+---------------------+---------------------+ | id | name | updated_at | +----+---------------------+---------------------+ | 1 | test_read_committed | 2023-08-13 13:15:52 | +----+---------------------+---------------------+
# 读已提交（幻读）
同样会话1 和 会话2 都设置事务隔离级别为：读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
会话1 执行第一次查询，查询结果集数量
-- 开启事务 begin; select count(*) from t1; -- 此时只有 3 条数据 +----------+ | count(*) | +----------+ | 3 | +----------+会话2 执行插入操作
-- 开启事务 begin; -- 插入一条数据 insert into t1 (id, name) values (4, 'Name4');此时会话1 再去执行查询操作，由于会话2 未提交，所以此时还是只能查询到 3 条数据
select count(*) from t1; +----------+ | count(*) | +----------+ | 3 | +----------+会话2 提交事务
commit;会话1 查询到了会话2 新增的数据，产生了幻读问题
select count(*) from t1; +----------+ | count(*) | +----------+ | 4 | +----------+
# 可重复读
会话1 和 会话2 都设置事务隔离级别为：可重读读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
会话1 开启事务，执行第一次查询操作
-- 开启事务 begin; select * from t1 where id = 1; +----+-------+---------------------+ | id | name | updated_at | +----+-------+---------------------+ | 1 | Name1 | 2023-08-14 14:23:40 | +----+-------+---------------------+会话2 开启事务， 更新此条数据
-- 开启事务 begin; -- 更新数据 update t1 set name = 'test_repeatable_read' where id = 1; -- 查询 select * from t1 where id = 1; -- 会话2 可以查询到数据更新 +----+----------------------+---------------------+ | id | name | updated_at | +----+----------------------+---------------------+ | 1 | test_repeatable_read | 2023-08-14 14:25:47 | +----+----------------------+---------------------+会话1 再次查询此条数据，由于会话2 尚未提交，所以此时查询不到更新的数据
select * from t1 where id = 1; +----+-------+---------------------+ | id | name | updated_at | +----+-------+---------------------+ | 1 | Name1 | 2023-08-14 14:23:40 | +----+-------+---------------------+会话2 提交事务
commit会话1 再一次来查询此条数据，仍然查询不到更新的数据，通过可重复读隔离级别保证了在同一事务中数据的一致性
select * from t1 where id = 1; +----+-------+---------------------+ | id | name | updated_at | +----+-------+---------------------+ | 1 | Name1 | 2023-08-14 14:23:40 | +----+-------+---------------------+
上次更新: 2024/11/05, 03:15:29