MYSQL事务

一、事务

**事务就是一组原子性的SQL查询，或者说是一个独立的工作单元。**事务内的语句，要么全部执行成功，要么全部执行失败。

例子：

假设一个银行的数据库有两个表：支票（checking） 表和储蓄（savings）表。现在要从 Jane 的支票账户移200美元到她的储蓄账户，那么需要至少

三个步骤：

1. 检查支票账户的余额高于200美元。
2. 从支票账户余额中减去200美元。
3. 在储蓄账户余额中增加200美元。

SQL 如下：

START TRANSACTION;

SELECT balance FROM checking WHERE customer_id=129928;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id=129928;
UPDATE savings SET balance = balance + 200.00 WHERE customer_id=129928;

COMMIT;

一）、ACID

• 原子性（atomicity）

  一个事物必须被视为一个不可分割的最小工作单元，整个事务中所有的操作要么全部提交成功，要么全部失败回滚，对于一个事物来说，不可能只执行其中的一部分操作，这就是事物的原子性。

• 一致性（consistency）

  数据库总是从一个一致性的状态转换到另外一个一致性的状态。

  比如性别约束 男or女；人民币面值不能为负数等等

• 隔离性（isolation）

  通常来说，一个事物所做的修改在最终提交之前，对其他事物是不可见的。

• 持久性（durability）

  一旦事务提交，则其所做的修改就会永久保存到数据库中。这里持久性是个有点模糊的概念，实际上持久性也分为很多不同的级别。

二）、事务的状态有哪些？

三）、事务并发访问的问题

• 脏读

  例子：

  1.两个事务正在并发的执行，事实上最后结果应该是1500才对，时刻5时刻的查询余额为0就是脏数据，事务A读取了事务B中未提交的数据，这就是脏读。

2.如果一个事务（小明）读取到 了另一个未提交事务（小丽） 修改过的数据，就意味着发生 了脏读现象。

• 不可重复读

  例子

  1.两个事务正在并发的执行，结果A两个读取的结果不一样，这是因为两次查询有间隔，期间被其他事物修改并提交了事务，相比脏读的区别是，不可重复度是读取另一事务提交的数据。这种现象也是正常的，是由于事务的隔离级造成的，但是在某些特别的情况下也是不允许的。

2.如果一个事务（小丽） 修改了另一个未提交事 务（小明）读取的数据， 就意味着发生了不可重 复读现象，或者叫模糊 读FuzzyRead

• 幻读

  例子

  1.两个事务正在并发的执行，事务A第一次统计和第二统计的结果不一样，是因为事务B新增了一条数据，和不可重复读一样，但是读取了另外一个事务的数据，不同的是不可重复读查询的是同一条数据，而幻读则是针对批量的数据，或者说不可重复读是A读取了B的更新数据，幻读是A读取了B的新增数据。‘

2.如果一个事务（小明）先根据某些搜索 条件（select … where vip=‘是’）查询了 一些记录，但是在该事务并未提交时， 另一个事务（小丽）写入了一些符合上 面搜索条件的记录（这里的写入可以值 insert、delete、update操作。例如： insert into … values(‘0003’,700,‘是’)）， 就意味着发生了幻读现象

三）、隔离级别

• READ UNCOMMITTED（未提交读）

  在 READ UNCOMMITTED 级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。

• READ COMMITTED（未提交读）

  大多数数据库系统默认的隔离级别都是 READ UNCOMMITTED （但MYSQL不是），一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。

• REPEATABLE READ（可重复读）

  解决了脏读的问题，该级别保证了在同一事务中多次读取同样记录的结果是一致的。但无法解决幻读（Phantom Read）的问题，所谓幻读，指的是当某个事务在读取某个范围记录时，会产生幻行。

• SERIALIZABLE（可串⾏化 几乎不用）

  SERIALIZABLE 是最该的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。

  SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题，实际中很少使用。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

由于无论哪种隔离级别，都不允许藏写的情况发生，所以没有列入表格中。

• 模拟事务

  • 查询事务类别

    #mysql5 版本
    select @@tx_isolation;
    #mysql8 版本
    show variables like 'transaction_isolation';
    

  • 设置事务类别

    例：

    set session transaction isolation level READ UNCOMMITTED;
    set session transaction isolation level READ COMMITTED;
    set session transaction isolation level REPEATABLE READ;
    set session transaction isolation level SERIALIZABLE;
    

四）、练习

#查看自动提交是否关闭
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)
#关闭自动提交
mysql> set session autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
#查看自动提交是否关闭
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

所有模拟为两个客户端。

1.脏读：读未提交

#设置事务
set session transaction isolation level READ UNCOMMITTED;
#客户端：一
select * from test_affair;
#开启事务，一二都开启，二客户端再次查一遍数据做检测
start transaction;
#修改但未提交事务
update test_affair set account=101 where id = 1;
#此时应该客户端二为101，未提交状态。但是查看客户端一时，也是101，出现未提交就已经读取，脏读

2.不可重复读：读已提交

set session transaction isolation level READ COMMITTED;
#修改后，修改的客户端为200，另外一个为101，并未修改，避免脏读
update test_affair set account=200 where id = 1;
#客户端二commit后，客户端一事务也修改为200，造成不可重复读
commit;

3.可重复读

set session transaction isolation level REPEATABLE READ;
#客户端二提交后，客户端一依旧为200
update test_affair set account=300 where id = 1;
commit;

#幻读
#客户端二新增一条，commit后，客户端一事务查询依旧只有300数据。
insert into test_affair(id,account,test_name) values('2','500','xiaohong');
#先查看一边客户端一的数据，在修改第一条
update test_affair set account = 500 where id = 1;
#出现修改了两条，幻读问题
Query OK, 2 row affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0