SQL - 事务控制

TCL - 事务

**模拟场景：**生活当中转账是转账方账户扣钱，收账方账户加钱。用数据库操作来模拟现实转账。

数据库模拟：

• A账户减1000元

UPDATE account SET MONEY = MONEY-1000 WHERE id=1;

• B账户加1000元

UPDATE account SET MONEY = MONEY+1000 WHERE id=2;

思考: 假设在A账户减钱后，程序出现了异常，那B账户还会执行加钱操作吗？

每条SQL语句都是一个独立的操作，执行成功后对数据库是永久性的影响。

概念:

• 事务是一个原子操作，由一个或多个SQL语句组成。
• 在同一个事务当中，所有的SQL语句要么全部执行成功，要么全部失败。

原理:

• 数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)。
• 一个事务中所有的增删改语句的执行结果都会缓存在回滚段中。
• 当事务中所有SQL语句均正常结束，才提交（commit），才会将回滚段中的数据同步到数据库。
• 否则无论因为哪种原因失败，整个事务将回滚（rollback）。

事务的边界

**开始: **

• 默认连接到数据库，隐式开始事务，每条语句执行完自动提交或回滚。
• 手动使用begin; 或 start transaction; 显示开启事务，不会自动提交或回滚。
• set autocommit=0; #禁止自动提交。

begin 和 set autocommit=0 的区别

结束:

• 提交:
  • 显式提交：commit;
  • 隐式提交：一条DML语句，正常提交（客户端退出连接）。
• 回滚：
  • 显式回滚：rollback;
  • 隐式回滚：非正常退出（断电、宕机）。执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。

事务的特性

1 Atomicity(原子性)

• 表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败。

2 Consistency(一致性)

• 表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态。

3 Isolation(隔离性)

• 事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态。
• 要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

4 Durability(持久性)

• 持久性事务完成之后，它对于系统的影响是永久性的。

事务的应用

基于增删改语句的操作结果，可通过程序逻辑手动控制事务提交或回滚。

应用事务完成转账:

开启事务：
 begin;或start transaction;
 set autocommit=0;#禁止自动提交 ，默认为1
事务内数据操作语句：
 UPDATE ACCOUNT SET MONEY = MONEY-1000 WHERE ID = 1;
 UPDATE ACCOUNT SET MONEY = MONEY+1000 WHERE ID = 2;
事务内语句都成功了，提交：
 commit;
事务内如果出现错误，回滚：
 rollback;

代码演示:

#创建表
create table account(
	id int primary key auto_increment,
	name varchar(20) not null,
	money decimal(12,2)
)charset=utf8mb4
#添加数据
insert into account values(null,'张三',10000),(null,'李四',0);
select * from account;

#显示开启事务 begin; 或 start transaction;
# begin;
set autocommit=0; #禁止自动提交
#转账
# 1 扣钱
update account set money=money-1000 where name='张三';
# 2 加钱
update account set money=money+1000 where name='李四';

#显示提交[成功]
commit;
#显示回滚[失败]
rollback;
set autocommit=1;#开启自动提交

事务隔离等级

操作:

• select @@tx_isolation; #查看隔离级别
• set session transaction isolation level [隔离级别] #修改隔离级别

MySQL支持四种隔离级别

1 Read Uncommitted（读取未提交内容)

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，称之为脏读（Dirty Read）。

2 Read Committed（读取提交内容）

大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别出现不可重复读（Nonrepeatable Read）问题，因为同一事务的其他实例在该实例处理期间可能会有新的commit，所以同一select可能返回不同结果。

3 Repeatable Read （可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻读” 行。InnoDB和Falcon存储引擎通过多版本并发控制机制解决了该问题。

4 Serializable（串读）

最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。效率最低的。

代码演示:

#查看隔离级别, 默认隔离级别: REPEATABLE-READ 可重复读
select @@tx_isolation;
# 1 设置隔离为 read uncommitted 读取未提交, 问题: 脏读
set session transaction isolation level read uncommitted;

# 2 设置隔离级别为 read committed 读取提交, 问题: 不可重复读
set session transaction isolation level read committed;

# 3 设置隔离级别为 repeatable read 可重复读, 问题: 幻读 (MySQL没有)
# 开启事务后不被外界影响
set session transaction isolation level repeatable read;
# 需求: 统计每天的收入情况
BEGIN;
	select * from account where name = '李四'; 
	select * from account where name = '李四'; 
	select * from account where name = '李四'; 
	#入账
COMMIT;

# 4 设置隔离级别为 serializable 串读 没有问题, 但性能低
set session transaction isolation level serializable;