MySQL-事务、事务隔离级别

事务：

事务是一组操作的集合，是把所有的操作作为一个整体一起向系统提交或撤销操作请求，这些操作要么同时成功，要么同时失败。

事务的四大特征(ACID)：

1. **原子性(atomicity)：**事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

2. **一致性(consistency)：**事务完成时，必须使所有的数据都保持一致状态。

   拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性

3. **隔离性(isolcation)：**隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

4. 持久性(durability)：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务的提交方式：

• 提交方式

  • 自动提交(MySQL默认为自动提交)
  • 手动提交

查看提交方式

SELECT @@AUTOCOMMIT;  -- 1代表自动提交    0代表手动提交

修改提交方式

SET @@AUTOCOMMIT=0;

操作事务三步骤：

1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败
2. 执行sql语句：执行具体的一条或多条sql语句
3. 结束事务(提交/回滚)
   - 提交：没出现问题，数据进行更新
   - 回滚：出现问题，数据恢复到开启事务时的状态

开启事务：

START TRANSACTION;

回滚事务：

ROLLBACK;

提交事务：

COMMIT;

演示事务数据准备：

-- 创建账户表
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 账户id
	NAME VARCHAR(20),			-- 账户名称
	money DOUBLE				-- 账户余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);

未管理事务演示：张三给李四转账500元

-- 1.张三账户-500
UPDATE account SET money = money-500 WHERE NAME='张三';

-- 2.李四账户+500
手动模拟异常，下面的代码都不会执行了，此时张三已经扣了500元，但是李四的账户余额没有变化，钱被系统吞了...
UPDATE account SET money=money+500 WHERE NAME='李四';

-- 该场景下，这两条sql语句要么同时成功，要么同时失败。就需要被事务所管理！

管理事务演示

-- 开启事务
START TRANSACTION;

-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';

-- 2.李四账户+500
出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';

-- 回滚事务(出现问题)
ROLLBACK;

-- 提交事务(没出现问题)
COMMIT;

并发事务问题：

多个事务同时操作时会引发以下3个常见问题，解决方案需要用到事务的隔离级别。

问题	现象	解决方案
脏读	一个事务读到另一个事务还没提交的数据	表级读锁
不可重复读	一个事务先后读取同一条记录， 但两次查询结果不一致	行级写锁
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻觉一样	表级写锁

事务的隔离级别：

• 多个客户端操作时，各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的。
• 而如果多个事务操作同一批数据时，则需要设置不同的隔离级别，否则就会产生问题 。

四种隔离级别

	隔离级别	名称	出现脏读	出现不可重复读	出现幻读	数据库默认隔离级别
1	read uncommitted	读未提交	是	是	是
2	read committed	读已提交	否	是	是	Oracle / SQL Server
3	repeatable read	可重复读	否	否	是	MySQL
4	serializable	串行化	否	否	否

注意：隔离级别从小到大安全性越来越高，但是性能越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别.

查询数据库隔离级别

SELECT @@transaction_isolation;

修改数据库隔离级别

-- 修改数据库隔离级别为read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;

-- 修改当前会话隔离级别为read uncommitted
SET session TRANSACTION ISOLATION LEVEL read uncommitted;

事务隔离级别演示：

脏读的问题

• 窗口1

-- 查询账户表
select * from account;

-- 设置隔离级别为read uncommitted
set global transaction isolation level read uncommitted;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查询转账结果 ,出现脏读(查询到其他事务未提交的数据)

-- 窗口2查看转账结果后，执行回滚
rollback;

• 窗口2

-- 查询隔离级别
SELECT @@transaction_isolation;

-- 开启事务
start transaction;

-- 查询账户表
select * from account;

解决脏读的问题

• 窗口1

-- 设置隔离级别为read committed
set global transaction isolation level read committed;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查看转账结果，并没有发生变化(脏读问题被解决了)

-- 执行提交事务。
commit;

-- 窗口2查看转账结果，数据发生了变化(出现了不可重复读的问题，读取到其他事务已提交的数据)

• 窗口2

-- 开启事务
start transaction;

-- 查询账户表
select * from account;

解决不可重复读的问题

• 窗口1

-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查看转账结果，并没有发生变化

-- 执行提交事务
commit;

-- 这个时候窗口2只要还在上次事务中，看到的结果都是相同的。只有窗口2结束事务，才能看到变化(不可重复读的问题被解决)

• 窗口2

-- 开启事务
start transaction;

-- 查询账户表
select * from account;

-- 提交事务
commit;

-- 查询账户表
select * from account;

幻读的问题：

• 窗口1

-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;

-- 开启事务
start transaction;

-- 添加一条记录
INSERT INTO account VALUES (3,'王五',1500);

-- 查询账户表，本窗口可以查看到id为3的结果
SELECT * FROM account;

-- 提交事务
COMMIT;

• 窗口2

-- 开启事务
start transaction;

-- 查询账户表，查询不到新添加的id为3的记录
select * from account;

-- 添加id为3的一条数据，发现添加失败。出现了幻读
INSERT INTO account VALUES (3,'测试',200);

-- 提交事务
COMMIT;

-- 查询账户表，查询到了新添加的id为3的记录
select * from account;

解决幻读的问题

• 窗口1

-- 设置隔离级别为serializable
set global transaction isolation level serializable;

-- 开启事务
start transaction;

-- 添加一条记录
INSERT INTO account VALUES (4,'赵六',1600);

-- 查询账户表，本窗口可以查看到id为4的结果
SELECT * FROM account;

-- 提交事务
COMMIT;

• 窗口2

-- 开启事务
start transaction;

-- 查询账户表，发现查询语句无法执行，数据表被锁住！只有窗口1提交事务后，才可以继续操作
select * from account;

-- 添加id为4的一条数据，发现已经存在了，就不会再添加了！幻读的问题被解决
INSERT INTO account VALUES (4,'测试',200);

-- 提交事务
COMMIT;