MySQL-事务

目录

• mysql-事务介绍
• mysql事务
  • acid
  • 事务的隔离级别
  • 不同的隔离级别可能引发不同的一致性问题
  • 并发事务下可能导致的数据不一致

mysql-事务介绍

标签（空格分隔）： mysql 事务

---

---

mysql事务

acid

1.原子性（atomcity）
一个事务的最小单元，要么全部成功要么全部失败，执行的过程中是不能被打断或者执行其他操作的。
2.一致性（consistent）
事务开始前和结束后，数据库的完整性约束没有被破坏。比如a向b转账，不可能a扣了钱，b却没收到，事务开始前a+b=500，事务结束后a+b不可能!=500。
3.隔离性(isolation)
隔离性表示各个事务之间不会互相影响，数据库一般会提供多种级别的隔离。实际上多个事务是并发执行的，但是他们之间不会互相影响。
4.持久性（durability)
事务提交后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的隔离级别

事务分为以下4个级别

• read uncommitted(可以读取未提交数据)

• read committed(只能读到已提交数据)

• read repeatable(一个事务中重复读取，数据保持一致性)

• serializable(串行执行，不会造成不一致问题，但会影响并发)

不同的隔离级别可能引发不同的一致性问题

隔离级别	脏读	不可重复读	幻读
read uncommitted	y	y	y
read committed	n	y	y
read repeatable	n	n	y
serializable	n	n	n

并发事务下可能导致的数据不一致

1. 脏读（dirty read）> 读取到中间值

事务a开启事务，做更新操作数据由300更新为400，并未commit，此时事务b会读取到a更新但未提交的值400。此时a事务 rollback，但是b事务已经读取到a更新的值，造成脏读。

事例：

• a连接

#设置当前会话事务隔离级别为读未提交
set session transaction isolation level read uncommitted;
#开始执行事务
start transaction;
select user_id,balance from tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#②在事务b第一次查询之后进行更新操作
update tb_user_account set balance = 400 where user_id = 86;
#④发生异常进行回滚
rollback;

• b连接

#查询当前连接事务级别
select @@tx_isolation;
#设置当前会话事务隔离级别为读未提交
set session transaction isolation level read uncommitted;
#开始执行事务
start transaction;
#①在事务a更新之前执行
select * from tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#③在事务a更新之后执行，此时读取到了未提交的数据400
 select user_id,balance from tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     400 |
+---------+---------+
#⑤在事务a回滚之后执行，两次读取到的数据不一致，发生的了脏读
 select user_id,balance from tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+

a=>operation: 事务a 更新为400
bs1=>operation: 事务b 第一次查询到300
bs2=>operation: 事务b 第二次查询到400（中间值）脏读
roll=>operation: 事务a 回滚
bs3=>operation: 事务b 第三次查询到300 （数据不一致）

bs1->a->bs2->roll->bs3

2. 不可重复读（unrepeatable read）> 更新场景，数据不一致

事务a开启事务，做更新操作数据由300更新为400，并未commit，此时事务b查询值为300，解决了脏读问题。此时a提交事务，事务b再次查询值为400,两次查询数据不一致。（不可重复读）
事例：

• a连接

#设置当前会话事务隔离级别为读已提交
set session transaction isolation level read committed;
#开始执行事务
start transaction;
select user_id,balance from tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#②在事务b第一次查询之后进行更新操作
update tb_user_account set balance = 400 where user_id = 86;
#④提交数据
commit;

• b连接

#查询当前连接事务级别
select @@tx_isolation;
#设置当前会话事务隔离级别为读未提交
set session transaction isolation level read committed;
#开始执行事务
start transaction;
#①在事务a提交之前执行
select * from tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#③在事务a提交之后执行，此时读取到的值还是300，解决了脏读问题。
 select user_id,balance from tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#⑤在事务a提交之后执行，两次读取到的数据不一致，发生的了不可重复读。
 select user_id,balance from tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     400 |
+---------+---------+

a=>operation: 事务a 更新为400
bs1=>operation: 事务b 第一次查询到300
bs2=>operation: 事务b 第二次查询到300，保证了不发生脏读
roll=>operation: 事务a 提交
bs3=>operation: 事务b 第三次查询到400 （不可重复读，数据不一致）

bs1->a->bs2->roll->bs3

3. 幻读（phantom read）>插入or删除场景，数据不一致

事务a开启事务，做查询大于0的数据，此时b连接插入一条大于0的数据。a再次查询大于0的数据，查询到b插入的数据，造成（幻读）。
事例：

• a连接

#设置当前会话事务隔离级别为可重复读
set session transaction isolation level repeatable read;
#开始执行事务
start transaction;
#①查询大于0的数据
select count(1) from tb_user_account where balance > 0;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
#③查询大于0的数据,查到新增数据(造成幻读，but mysql，mvvc解决了此问题）
select count(1) from tb_user_account where balance > 0;
+----------+
| count(1) |
+----------+
|        2 |
+----------+

• b连接

#②在事务a提交之前执行
insert into tb_user_account(balance) values(100);

a=>operation: 事务a  查询大于0的数据
bs1=>operation: 事务b 插入一条大于0的数据
as2=>operation: 事务a  查询大于0的数据,查到了新增数据。（幻读，数据不一致）

a->bs1->as2

4. 更新丢失（lost update）>幻读中数据更新丢失

使用 repeatable read隔离级别，事务a开启事务，做更新操作数据增加100，并未commit，此时事务b也做更新操作数据增加100。此时b先提交事务，之后a再提交事务，会造成b事务更新丢失。（mysql的innodb使用了mvcc，在提交b时会锁住行数据，避免此种问题）。
事例：

• a连接

#设置当前会话事务隔离级别为读已提交
set session transaction isolation level read committed;
#开始执行事务
start transaction;
select user_id,balance from tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#①更新操作数据增加100。
update tb_user_account set balance = balance + 100 where user_id = 86;
#④提交数据,此时会造成事务b的更新丢失。
commit;

• b连接

#查询当前连接事务级别
select @@tx_isolation;
#设置当前会话事务隔离级别为读未提交
set session transaction isolation level read committed;
#开始执行事务
start transaction;
#②在事务a提交之前执行，增加100。
update tb_user_account set balance = balance + 100 where user_id = 86;

#③事务b提交更新
commit;

a=>operation: 事务a 增加100
bs1=>operation: 事务b 增加100
bcommit=>operation: 事务b 提交
acommit=>operation: 事务a 提交(a未读取到b的更新，造成更新丢失）


a->bs1->bcommit->acommit