sql事务

事务

事务是并发控制的单位，是用户定义的一个操作序列。

MySQL 事务主要用于处理操作量大，复杂度高的数据。

在 MySQL 中，事务是一组SQL语句的执行，它们被视为一个单独的工作单元。

但是在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

1. 四大特性【ACID】

事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

2. 事务隔离性

事务的隔离性有4个隔离级别

• 读未提交：read uncommitted
• 读已提交：read committed
• 可重复读：repeatable read
• 串行化：serializable

mysql中，默认的事务隔离级别是可重复读（repeatable-read），为了解决不可重复读，innodb采用了MVCC（多版本并发控制）来解决这一问题。 MVCC是利用在每条数据后面加了隐藏的两列（创建版本号和删除版本号），每个事务在开始的时候都会有一个递增的版本号,用来和查询到的每行记录的版本号进行比较。

2.1 mysql的事务隔离级别

docker的mysql退出后会重置

查询下mysql本身的事务隔离级别：可重复读【解决了脏读、不可重复读、幻读】

SELECT @@global.transaction_isolation,@@transaction_isolation;

2.2 事务控制语句：

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
• 直接用 SET 来改变 MySQL 的自动提交模式:
  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

2.3 脏读、不可重复读、幻读

2.3.1 READ-UNCOMMITTED（读未提交、脏读）

A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。就好像原本的数据比较干净、纯粹，此时由于B事务更改了它，这个数据变得不再纯粹。这个时候A事务立即读取了这个脏数据，但事务B良心发现，又用回滚把数据恢复成原来干净、纯粹的样子，而事务A却什么都不知道，最终结果就是事务A读取了此次的脏数据，称为脏读。

这种情况常发生于转账与取款操作中，操作顺序如下

顺序序号	付款方	收款方
1	开启事务
2		开启事务
3	给收款方转账100
4		读自己账户
5	回滚

设置隔离级别为READ UNCOMMITTED

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

付款方代码：

START TRANSACTION;
UPDATE account SET account_balance = account_balance + 100 WHERE account_id = 1;
ROLLBACK;

收款方代码：

START TRANSACTION;
SELECT * FROM account;

TIP：这里其实还有一个问题，当收款方在第5次操作之后在读取数据，会发现数据不一致了，这就是不可重复读，所以脏读问题一定会出现不可重复读。

2.3.2 READ-COMMIT（读已提交、不可重复读）

前后多次读取，数据内容不一致

事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的余额为100，事务B执行更改操作，花了30元，此时事务A第二次读取到余额时，发现余额只剩70元，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

顺序序号	事务A	事务B
1	开启事务
2	第一次查询余额100
3		开启事务
4	其他操作
5		花了30
6	第二次查询余额70

设置隔离级别为READ COMMITTED

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

第2步

第5步：先不提交，此时去查询还是111.11，说明read-committed解决了脏读问题

第6步，数据不一致了

2.3.3 READ-COMMIT（幻读）

前后多次读取，数据总量不一致

事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。

重置mysql的事务级别

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

顺序序号	事务A	事务B
1	开启事务
2	第一次查询数据2条
3		开启事务
4	其他操作
5		新增1条数据
6		提交事务
7	第二次查询数据3条
备注	按照正常逻辑，数据总量应该不变

tips:不可重复读和幻读到底有什么区别?

• 不可重复读是读取了其他事务更改的数据，针对update操作

解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

• 幻读是读取了其他事务新增的数据，针对insert和delete操作

解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

mysql解决幻读的方案

select …. for update语句

原理（mvvc:多版本并发控制): 每一行中都冗余了两个字段。一个是行的创建版本，一个是行的删除（过期）版本。版本号随着每次事务的开启自增。事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。

next-key 锁包含两部分：记录锁（行锁）、间隙锁。
记录锁是加在索引上的锁，间隙锁是加在索引之间的。
原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的。

• 明确指定主键，并且有此记录，行级锁
• 明确指定主键/索引，若查无此记录，无锁
• 无主键/索引，表级锁
• 主键/索引不明确，表级锁