【MySQL】事务管理

事务机制

• 事务由作为一个单独单元的一个或多个SQL语句组成。
• 事务中的每个SQL语句是互相依赖的，而且事务作为一个整体是不可分割的。
• 如果事务中的一个语句不能完成，整个事务就会回滚（撤销），所有影响到的数据将返回到事务开始以前的状态。
• 只有事务中的所有语句都被成功地执行，这个事务才能被成功地执行。

事务的提交和回滚

在MySQL中，当一个会话开始时，系统变量AUTOCOMMIT的值为1，即自动提交功能是打开的。

执行事务时要首先关闭MySQL的自动提交，使用命令

set autocommit=0;

可以关闭MySQL的自动提交

事务提交

当MySQL关闭自动提交后，可以使用COMMIT命令来完成事务的提交。COMMIT命令使得从事务开始以来所执行的所有数据修改成为数据库的永久组成部分，也标志着一个事务的结束。

开始一个事务使用

start transaction;

同时也会隐式地关闭MySQL自动提交。

例1 事务的提交

-- 会话1
set autocommit=0;
delete from account where name="Tom";
select * from account;
uid		name	amount
2		Bob		3000
3		Amy		5000

-- 会话2
use bank;
SELECT * FROM account;
uid		name	amount
1       Tom     1000
2		Bob		3000
3		Amy		5000

-- 会话1
commit;

-- 会话2
select * from account;

事务的回滚

使用rollback命令可以完成事务的回滚，事务的回滚可以撤销未提交的事务所做的各种修改操作，并结束当前这个事务。

• savepoint 保存点名; 设置保存点
• rollback to savepoint 保存点名; 将事务回滚到保存点状态

例2 事务的回滚

-- 开启一个事务
START TRANSACTION;

-- 插入一条记录
INSERT INTO account VALUES(1,'Tom',1000);
SELECT * FROM account WHERE NAME='Tom';

-- 设置保存点
SAVEPOINT p1;

-- Tom存1000
UPDATE account SET amount=amount+1000 WHERE NAME='Tom';
SELECT * FROM account

-- 回滚到p1保存点
ROLLBACK TO SAVEPOINT p1;
SELECT * FROM account

-- 回滚全部
ROLLBACK;
SELECT * FROM account WHERE NAME='Tom';

事务的四大特性和隔离级别

事务是一个单独的逻辑单元，事务中的所有更新操作要么都执行，要么都不执行。事务保证了一系列更新操作的原子性。如果事务与事务之间存在并发操作，则可以通过事务之间的隔离级别来实现事务的隔离，从而保证事务间数据的并发访问。

事务的四大特性（ACID）

1. 原子性（Atomicity）

例3 事务的原子性

USE bank;
START TRANSACTION;
-- Tom给Bob转账2500
UPDATE account SET amount=amount+2500 WHERE NAME='Bob';
SELECT * FROM account;
UPDATE account SET amount=amount-2500 WHERE NAME='Tom';
-- 插入失败 执行回滚
ROLLBACK;
SELECT * FROM account;

2. 一致性（Consistency）

事务的一致性保证了事务完成后数据库能够处于一致性状态。如果事务执行过程中出现错误，那么数据库中的所有变化将自动回滚到另一种一致性状态。在MySQL中一致性主要由MySQL的日志机制处理，它记录了数据库的所有变化，为事务恢复提供了跟踪记录。如果系统在事务处理过程中发生错误，MySQL恢复过程将使用这些日志来发现事务是否已经完全成功地被执行，是否需要返回。一致性保证了数据库从不返回一个未处理完的事务。

3. 隔离性（Isolation）

事务的隔离性确保多个事务并发访问数据时，各个事务不能相互干扰。系统中的每个事务在自己的空间执行，并且事务的执行结果只有在事务执行完成后才能看到。即使系统中同时执行多个事务，事务在完全执行完之前，其他事务是看不到结果的。在多数事务系统中，可以使用页级锁定或行级锁定来隔离不同事务的执行。

例4 事务的隔离性

-- 事务1
USE bank;
START TRANSACTION;
	-- Tom取走500
UPDATE account SET amount=amount-500 WHERE NAME='Tom';

-- 事务2
USE bank;
	-- Tom再取走500
UPDATE account SET amount=amount-500 WHERE NAME='Tom';  -- 会一直等待

-- 事务1
COMMIT;

-- 事务2
SELECT * FROM account; -- Tom少了1000

4. 持久性（Durability）

事务的持久性意味着事务一旦被提交，其改变会永久生效，不能再被撤销。即使系统崩溃，一个提交的事务仍然存在。MySQL通过保存所有行为的日志来保证数据的持久性，数据库日志记录了所有对于表的更新操作。

事务的隔离级别

事务的隔离级别是事务并发控制的整体解决方案，是综合利用各种类型的锁机制来解决并发问题。每个事务都有一个隔离级，它定义了事务彼此之间隔离和交互的程度。MySQL中提供了4种隔离级别。

1. 读取未提交数据（read uncommitted）

提供了事物之间的最小隔离程度，处于这个隔离级别的事务可以读到其他事务还没有提交的数据。

出现：脏读、不可重复读、幻读

2. 读取提交的数据（read committed)

处于这一级别的事务可以看见已经提交的事务所做的改变，这一隔离级别要低于repeatable read。

出现：不可重复读、幻读

3. 可重复读（repeatable read）

这是MySQL默认的事务隔离级别，它能确保在同一事务内相同的查询语句其执行结果总是相同的。

出现：幻读

4. 串行化（serializable）

这是最高级别的隔离，它强制事务排序，使事务一个接一个的顺序执行。

---

解决多用户使用问题

1. 脏读（Dirty Read）

一个事务可以读到另一个事务未提交的数据。

如何避免？将事务的隔离级别设置为read committed

-- 事务A
use bank;

set session transaction isolation level read Uncommitted;
start transaction;

SELECT * FROM account;
uid		name	amount
1		Tom		1000
2		Bob		3000
3		Amy		5000

-- 事务B
USE bank;

START SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE account SET amount=amount+500 WHERE NAME="Tom";

-- 事务A
SELECT * FROM account;
uid		name	amount
1		Tom		1500
2		Bob		3000
3		Amy		5000

-- 关闭事务B（事务B还没提交）

-- 事务A
SELECT * FROM account;
uid		name	amount
1		Tom		1000
2		Bob		3000
3		Amy		5000

2. 不可重复读（Non-repeatable read）

在同一个事务中，两条相同的查询语句其查询结果不一致。当一个事务访问数据时，另一个事务对该数据进行修改并提交，导致第一个事务两次读到的数据不一样。

如何避免？将事务的隔离级别设置为repeatable read，可以避免脏读和不可重复读

脏读时读取了其他事务未提交的数据，而不可重复读是读取了其他事务已经提交的数据

-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM account;
uid		name	amount
1		Tom		1000
2		Bob		3000
3		Amy		5000

-- 事务B
USE bank;
START SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE account SET amount=amount+500 WHERE NAME="Tom";

-- 事务A（查询没有变化，即没有出现脏读）
SELECT * FROM account;

-- 事务B
COMMIT;

-- 事务A
SELECT * FROM account;
uid		name	amount
1		Tom		1500
2		Bob		3000
3		Amy		5000

3. 幻读（Phantom Read）

幻读是指当前事务读不到其他事务已经提交的修改。

如何避免？将事务的隔离级别设置为serializable

-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM account;
uid		name	amount
1		Tom		1500
2		Bob		3000
3		Amy		5000

-- 事务B
SET SESSION TRANSACTION LEVEL REPEATABLE READ;
START TRANSACTION;
DELETE FROM account WHERE NAME="Tom";

-- 事务A
SELECT * FROM account;
uid		name	amount
1		Tom		1500
2		Bob		3000
3		Amy		5000

-- 事务B
COMMIT;

-- 事务A（避免了不可重复度）
SELECT * FROM account;
uid		name	amount
1		Tom		1500
2		Bob		3000
3		Amy		5000

UPDATE account SET amount=amount+1000 WHERE NAME="Tom"; -- 0行受到影响
SELECT * FROM account WHERE NAME="Tom"; -- 还是存在，说明出现幻读
uid		name	amount
1		Tom		1500

COMMIT;
SELECT * FROM account;
uid		name	amount
2		Bob		3000
3		Amy		5000