day08-MySQL事务

MySQL事务

先来看一个例子

有一张balance表:
需求：将tom的100块钱转到King账户中
执行的操作是：

1. update balance set money = money -100 where id = 100
2. update balance set money = money +100 where id = 200

复制代码

这时，如果第一条语句执行成功，但第二条语句执行失败，就会出现问题。
这里引出一个需求，将多个dml语句（update，insert，delete）当做一个整体，要么全部成功，要么全部失败
--->使用事务来解决
1.什么是事务

• 什么是事务
  

事务用于保证数据的一致性，它由一组相关的dml语句(update，insert，delete)组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性。

• 事务和锁
  

当执行事务操作时（dml语句），mysql会在表上加锁，防止其他用户修改表的数据。这对用户来讲是非常重要的。

• mysql数据库控制台事务的几个重要操作（基本操作）
  

注意：当直接回退到保存点a时，会删除中间的保存点b

1. 1.start transaction --开始一个事务
2. 2.savepoint 保存点名 -- 设置保存点
3. 3.rollback to 保存点名 -- 回退事务
4. 4.rollback -- 回退全部事务
5. 5.commit -- 提交事务，所有的操作生效，不能回退

复制代码

细节：

• 没有设置保存点
  
• 多个保存点
  
• 存储引擎
  
• 开始事务方式
  

• 回退事务
  

在介绍回退事务前，先介绍一下保存点（savepoint）。保存点是事务中的点，用于取消部分事务，当结束事务时（commit），会自动地删除该事务所定义的所有保存点。
当执行回退事务时，通过指定保存点可以回退到指定的点

• 提交事务
  

使用commit语句可以提交事务。当执行了commit语句之后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。
当使用了commit语句结束事务之后，其它会话[其他连接] 可以查看到事务变化后的新数据 [所有的数据正式生效]

例子

1. -- 事务的演示操作
2. -- 1.创建一张测试表
3. CREATE TABLE t27(
4.         id INT ,
5.         `name` VARCHAR(32)
6. );
8. SELECT * FROM t27;
9. -- 2.开始事务
10. START TRANSACTION;
12. -- 3.设置保存点
13. SAVEPOINT a;
15. -- 4.执行dml操作1
16. INSERT INTO t27 VALUES(100,'tom');
18. -- 设置保存点b
19. SAVEPOINT b;
21. -- 执行dml操作2
22. INSERT INTO t27 VALUES(200,'jack');
24. -- 回退到b
25. ROLLBACK TO b
27. -- 继续回退a
28. ROLLBACK TO a
30. -- 如果是rollback，表示直接回退到事务开始的状态
31. ROLLBACK
33. COMMIT

复制代码

2.事务注意事项

• 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
  
• 如果开始一个事务，你没有创建保存点，也可以执行rollback，默认就是回到事务开始的状态
  
• 可以在事务中（还没有提交时），创建多个保存点。比如：savepoint aaa；执行dml，savepoint bbb；
  
• 可以在事务没有提交前，选择回退到哪个保存点
  
• innodb的存储引擎支持事务，myisam不支持
  
• 开始一个事务的方式 start transaction或者set autocommit = off;
  

例子

1. -- 讨论事务细节
2. -- 1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
3. INSERT INTO t27 VALUES(300,'milan'); -- 自动提交 commit
4. SELECT * FROM t27;
6. -- 2. 如果开始一个事务，你没有创建保存点，也可以执行rollback，
7. -- 默认就是回到事务开始的状态
8. START TRANSACTION
9. INSERT INTO t27 VALUES(400,'king');
10. INSERT INTO t27 VALUES(500,'scott');
11. ROLLBACK -- 表示直接回退到事务开始的状态
12. COMMIT
13. -- 3. 可以在事务中（还没有提交时），创建多个保存点。
14. -- 比如：savepoint aaa；执行dml，savepoint bbb；
15. -- 4. 可以在事务没有提交前，选择回退到哪个保存点
16. -- 5. innodb的存储引擎支持事务，myisam不支持
18. -- 6. 开始一个事务的方式 start transaction或者set autocommit = off；
19. SET autocommit = off

复制代码

3.事务的四种隔离级别

• 事务隔离级别介绍
  

• 多个连接开启各自的事务，操作数据库中的数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
  
• 如果不考虑隔离性，可能会引发如下问题：
  
  
  
  • 脏读（dirty read）：当一个事务读取另一个事务尚未提交的改变（delete,insert,update）时，产生脏读
    
  • 不可重复读（nonrepeatable read）：同一个查询在同一事务中多次进行，由于其他已提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读
    
  • 幻读（phantom read）：虚读，同一查询在同一事务中多次进行，由于其他已提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读
    
  
  

• 事务隔离级别
  

概念：MySQL隔离级别定义了事务与事务之间的隔离程度
MySQL隔离级别（4种）脏读不可重复读幻读加锁读读未提交（read  uncommitted）会出现会出现会出现不加锁读已提交（read  committed）不会出现会出现会出现不加锁可重复读（repeatable read）不会出现不会出现不会出现不加锁可串行化（serializable）不会出现不会出现不会出现加锁可重复读实际上会发生幻读？
3.1读未提交（read  uncommitted）

MySQL的事务隔离级别--案例

我们举例一个案例来说明mysql的事务隔离级别。以account表进行操作为例。（id、name、money）

• 开启两个mysql的控制台
  
• 查看当前mysql的隔离级别，均为可重复读
  1. mysql> select @@tx_isolation;
  2. +-----------------+
  3. | @@tx_isolation  |
  4. +-----------------+
  5. | REPEATABLE-READ |
  6. +-----------------+
  7. 1 row in set (0.00 sec)

  复制代码
  
  
• 将其中一个连接的隔离级别设置为 read  uncommitted（读未提交）
  1. -- 把其中一个控制台的隔离级别设置为read uncommitted
  2. SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  复制代码
  此时的情况：左边的隔离级别为读未提交；右边的隔离级别为可重复读
  
  
• 然后两边都开启事务
  
  
• 在两个连接控制台中选择数据库，在隔离级别为 可重复读 的连接中 创建表account
  
  
• 再在隔离级别为 可重复读 的连接中插入一条数据（但未提交）
  在另一个连接（隔离级别为 读未提交 READ-UNCOMMITTED）中查询该表，发现可以查询到另一事务尚未提交的插入的数据，这时就发生了脏读
  

  脏读：当一个事务读取另一个事务尚未提交的改变（delete,insert,update）时，产生脏读
  

  
  
• 在隔离级别为 可重复读 的连接中更新一条数据，同时插入一条数据，然后提交commit
  此时在另一个连接（隔离级别为 读未提交）中，查询同一张表，可以看到在这个（隔离级别为 读未提交的）连接中，已经可以看到另一个事务中提交的数据
  即一个事务的操作影响了另一个事务的查询，这时候就发生了不可重复读和幻读
  这将会导致，当有多个连接开启事务时，某一连接的事务的查询会受到其他所有连接的事务的影响，这无疑将会导致混乱
  

  最佳情况应该是：一个连接 连接到数据库，操作account表的时候，希望看到的数据应该是，开启事务的这一时刻的数据
  

  
  
• 在连接（隔离级别为 读未提交）中提交commit结束一个事务，此时两个连接中的事务均已结束
  

3.2读已提交（read  committed）

例子

• 在上个例子开启的两个连接中，将其中一个连接的隔离级别修改为 读已提交，
  另一个保持隔离级别为可重复读
  
  
• 两边都开启事务
  
  
• 在隔离级别为可重复读的连接中插入一条数据
  然后在隔离级别为读已提交的连接中 插询表account，可以看到查询到的数据还是本连接开启事务时的数据
  即，读已提交的隔离级别不会出现脏读现象
  
  
• 在隔离级别为可重复读的连接中更新一条数据
  ​        然后在隔离级别为读已提交的连接中 插询表account，可以看到查询到的数据变成了其他连接的事务提交的        数据，说明，在隔离级别为读已提交下，出现了不可重复读和幻读
  
  

3.3可重复读（repeatable read）

• 重新开启两个连接，两个连接的隔离级别均为可重复读
  
  
• 然后两边均开启事务
  
  
• 在一个连接中选择数据库，然后在account表中插入一条数据，再更新一条数据（未提交）
  此时该连接中的表情况为：
  在另一个连接中选择数据库，查询表account，可以看到查询到的表数据依旧是开启事务时的样子，没有受到其他事务的影响，即没有产生脏读
  
  
• 在原先修改数据的连接中输入commit提交
  在另一个连接中再查询表account，可以看到数据依旧是开启事务的时刻的样子
  即，没有产生不可重复读和幻读
  
  

综上，隔离级别为可重复读的情况下 既不会出现脏读，也不会出现不可重复读和幻读
3.4可串行化（serializable）

• 将上面两个连接其中一个重新启动，将新连接设置隔离级别为可串行化（serializable）
  此时两个连接的隔离级别分别为 可重复读 和可串行化（serializable）
  
  
• 这时分别在两个连接中均开启事务
  
  
• 在隔离级别为可重复读的连接中分别插入、更新数据（未提交）
  ​        在另一个隔离级别为可串行化的连接中选择数据库。然后查询表account，回车时会发现卡住了，这是因为        可串行化会加锁
  

  A连接在操作表的时候，事务还没有结束，这时B连接也尝试操作该表，此时将会检查A的事务有没有结束，如果没有结束，B连接的操作就会进行等待，直到A连接的事务提交
  

  ​                这时，在隔离级别为可重复读的连接中提交事务
  ​                        可以看到可串行化级别的连接中可以成功操作表了
  ​                       
  

综上说明，可串行化级别下，不仅不会出现脏读、不可重复读、幻读，还会加锁读
4.设置隔离


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！