第二课 事务的使用

1 事务的分类

• 隐式事务：事务没有明显的开启和结束的标记，比如INSERT，UPDATE，DELETE语句
  因为系统中存在autocommit常量，默认值为on，表示自动提交默认开启，SHOW VARIABLES LIKE 'autocommit';
• 显式事务：具有明显的开启和结束标志，前提需要关闭自动提交功能，SET autocommit = 0;，只针对当前会话有效

2 事务步骤

• 第0步：关闭自动提交
  SET autocommit = 0;
• 第1步：开启事务
  START TRANSACTION; # 可选
• 第2步：编写事务中的SQL语句（增删改查）
• 第3步：结束事务（需要配合JDBC实现）
  • 如果事务中没有异常或错误，COMMIT; # 提交事务
  • 如果事务中存在异常或错误，ROLLBACK; # 回滚事务

结束条件：

1. COMMIT;或ROLLBACK;语句
2. DDL或DCL语句（自动提交）
3. 用户会话正常结束
4. 系统异常终了

注：一个事务中的SQL语句只是对表中数据进行修改的语句（UPDATE,INSERT,DELETE），不包括（CREATE,DROP,ALTER，不会报错，但是也没有效果）

CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);

INSERT INTO account (username,balance) VALUES ('甲方',500);
INSERT INTO account (username,balance) VALUES ('乙方',500);

# 事务模拟：转账
# 关闭自动提交
SET autocommit = 0;

# 开启事务
START TRANSACTION;
UPDATE account SET balance = 300 WHERE username = '甲方';
UPDATE account SET balance = 700 WHERE username = '乙方';

# 结束事务
COMMIT;

3 数据库的隔离级别（事务并发问题）

3.1 脏读、幻读及不可重复读

• 对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：
  • 脏读：对于两个事务T1和T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容旧是临时且无效的
  • 不可重复读：对于两个事务T1和T2，T1读取了一个字段，然后T2更新了该字段。之后，T1再次读取同一个字段，值不同了
  • 幻读：对于两个事务T1和T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1再次读取同一个表就会多出几行

3.2 数据库事务的隔离性

• 数据库系统必须具有隔离并发运行各个事务的能力，使他们不会互相影响，避免各种并发问题

3.3 隔离级别

• 一个事务与其他事务隔离的程度称为隔离级别，数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性越好，但并发性越弱

隔离级别	描述	解决问题
READ UNCOMMITTED（读未提交数据）	允许事务读取未被其他事务提交的变更	脏读、幻读及不可重复读的问题都会出现
READ COMMITTED（读已提交数据）	只允许事务读取已经被其他事务提交的变更	解决了脏读，但是幻读和不可重复读依旧存在
REPEATABLE READ(可重复读)	确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新	解决了脏读和不可重复读，但是幻读的问题仍然存在
SERIALIZABLE(串行化)	确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作	解决所有并发问题，但是性能十分低下

• Oracle支持两种事务隔离级别：READ COMMITED（默认事务隔离级别）和SERIALIZABLE
• MySQL支持四种事务隔离级别：REPEATABLE READ为默认事务隔离级别

3.4 隔离级别的设置

# 查询当前会话事务隔离级别
SELECT @@tx_isolation;

# 设置当前会话事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

# 设置数据库系统的全局的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

3.5 实际操作

3.5.1 脏读

-- T1 会话 START -- 
-- 脏读：执行顺序_1 START -- 
# 查询当前会话事务隔离级别
SELECT @@tx_isolation;

# 脏读测试
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
# 脏读解决
# SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

# 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 脏读：执行顺序_1 END -- 

-- 脏读：执行顺序_4 START -- 
# 读取T2修改未提交的
SELECT * FROM account;
-- 脏读：执行顺序_4 END -- 

/*
执行结果：T2没有提交，但是T1读取到了T2没有提交的修改后数据
*/

COMMIT;
-- T1 会话 END --

-- T2 会话 START -- 
-- 脏读：执行顺序_2 START -- 
# 查询当前会话事务隔离级别
SELECT @@tx_isolation;

# 脏读测试
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
# 脏读解决
# SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

# 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 脏读：执行顺序_2 END -- 

-- 脏读：执行顺序_3 START -- 
# 修改T2的数据，T1会进行读取
UPDATE account SET balance = '200' WHERE id = 1;
-- 脏读：执行顺序_3 END -- 
 
COMMIT;
-- T2 会话 END -- 

3.5.2 不可重复读

-- T1 会话 START -- 
-- 不可重复读：执行顺序_1 START -- 
# 查询当前会话事务隔离级别
SELECT @@tx_isolation;

# 不可重复读测试
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# 不可重复读解决
# SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 不可重复读：执行顺序_1 END -- 

-- 不可重复读：执行顺序_3 START -- 
SELECT * FROM account -- balance 的值没有改变
-- 不可重复读：执行顺序_3 END -- 

-- 通常有其他操作

-- 不可重复读：执行顺序_5 START -- 
SELECT * FROM account -- balance 的值发生变化
-- 不可重复读：执行顺序_5 END -- 
COMMIT;
-- T1 会话 END --  

-- T2 会话 START -- 
-- 不可重复读：执行顺序_2 START -- 
# 查询当前会话事务隔离级别
SELECT @@tx_isolation;

# 不可重复读测试
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# 不可重复读解决
# SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 不可重复读：执行顺序_2 END -- 

-- 不可重复读：执行顺序_4 START -- 
UPDATE account SET balance = 99;
COMMIT;
-- 不可重复读：执行顺序_4 END -- 

-- T2 会话 END -- 

3.5.3 幻读

-- T1 会话 START -- 
-- 幻读：执行顺序_1 START -- 
# 查询当前会话事务隔离级别
SELECT @@tx_isolation;

# 幻读测试
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# 幻读解决
# SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

# 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 幻读：执行顺序_1 END -- 

-- 幻读：执行顺序_3 START -- 
SELECT * FROM account; -- 没有记录
-- 幻读：执行顺序_3 END -- 

-- 通常有其他操作

-- 幻读：执行顺序_5 START -- 
UPDATE account SET balance = 10; -- 两条记录被更改
COMMIT;
-- 幻读：执行顺序_5 END -- 
-- T1 会话 END --  

-- T2 会话 START -- 
-- 幻读：执行顺序_2 START -- 
# 查询当前会话事务隔离级别
SELECT @@tx_isolation;

# 幻读测试
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# 幻读解决
# SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

# 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 幻读：执行顺序_2 END -- 

-- 幻读：执行顺序_4 START -- 
# 此时发生死锁等待T1完成COMMIT指令
INSERT INTO account VALUES(NULL,'A',123); 
INSERT INTO account VALUES(NULL,'B',456);
-- 幻读：执行顺序_4 END -- 
COMMIT;
-- T2 会话 END -- 

4 回滚点设置

• 设置回滚点

  SAVEPOINT 节点名
  

• 回滚到节点处

  ROLLBACK TO 节点名
  

# 开启事务
SET autocommit = 0;
START TRANSACTION;

# 删除2号数据
DELETE FROM my_employees WHERE id = 2;

# 设置回滚点，从此处开始回滚
SAVEPOINT demo;
DELETE FROM my_employees WHERE id = 3;

# 跳转到回滚点
ROLLBACK TO demo;