day08-MySQL事务

乎语百科 339 0

MySQL事务

先来看一个例子

有一张balance表:

需求：将tom的100块钱转到King账户中

执行的操作是：

 update balance set money = money -100 where id = 100
 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.start transaction --开始一个事务
2.savepoint 保存点名 -- 设置保存点
3.rollback to 保存点名 -- 回退事务
4.rollback -- 回退全部事务
5.commit -- 提交事务，所有的操作生效，不能回退

细节：

  1. 没有设置保存点
  2. 多个保存点
  3. 存储引擎
  4. 开始事务方式
  • 回退事务

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

当执行回退事务时，通过指定保存点可以回退到指定的点

  • 提交事务

使用commit语句可以提交事务。当执行了commit语句之后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。

当使用了commit语句结束事务之后，其它会话[其他连接] 可以查看到事务变化后的新数据 [所有的数据正式生效]

例子

-- 事务的演示操作
-- 1.创建一张测试表
CREATE TABLE t27(
	id INT ,
	`name` VARCHAR(32)
);

SELECT * FROM t27;
-- 2.开始事务
START TRANSACTION;

-- 3.设置保存点
SAVEPOINT a;

-- 4.执行dml操作1
INSERT INTO t27 VALUES(100,'tom');

-- 设置保存点b
SAVEPOINT b;

-- 执行dml操作2
INSERT INTO t27 VALUES(200,'jack');

-- 回退到b
ROLLBACK TO b

-- 继续回退a
ROLLBACK TO a

-- 如果是rollback，表示直接回退到事务开始的状态
ROLLBACK

COMMIT

2.事务注意事项

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

例子

-- 讨论事务细节
-- 1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
INSERT INTO t27 VALUES(300,'milan'); -- 自动提交 commit
SELECT * FROM t27;

-- 2. 如果开始一个事务，你没有创建保存点，也可以执行rollback，
-- 默认就是回到事务开始的状态
START TRANSACTION
INSERT INTO t27 VALUES(400,'king');
INSERT INTO t27 VALUES(500,'scott');
ROLLBACK -- 表示直接回退到事务开始的状态
COMMIT
-- 3. 可以在事务中（还没有提交时），创建多个保存点。
-- 比如：savepoint aaa；执行dml，savepoint bbb；
-- 4. 可以在事务没有提交前，选择回退到哪个保存点
-- 5. innodb的存储引擎支持事务，myisam不支持

-- 6. 开始一个事务的方式 start transaction或者set autocommit = off；
SET autocommit = off

3.事务的四种隔离级别

  • 事务隔离级别介绍
  1. 多个连接开启各自的事务，操作数据库中的数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据是的准确性。
  2. 如果不考虑隔离性，可能会引发如下问题：
    • 脏读（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）

  1. 开启两个mysql的控制台

  2. 查看当前mysql的隔离级别，均为可重复读

    mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

  3. 将其中一个连接的隔离级别设置为 read uncommitted（读未提交）

    -- 把其中一个控制台的隔离级别设置为read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    此时的情况：左边的隔离级别为读未提交；右边的隔离级别为可重复读

  4. 然后两边都开启事务

  5. 在两个连接控制台中选择数据库，在隔离级别为 可重复读 的连接中 创建表account

  6. 再在隔离级别为 可重复读 的连接中插入一条数据（但未提交）

    在另一个连接（隔离级别为 读未提交 READ-UNCOMMITTED）中查询该表，发现可以查询到另一事务尚未提交的插入的数据，这时就发生了脏读

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

  1. 在隔离级别为 可重复读 的连接中更新一条数据，同时插入一条数据，然后提交commit

    此时在另一个连接（隔离级别为 读未提交）中，查询同一张表，可以看到在这个（隔离级别为 读未提交的）连接中，已经可以看到另一个事务中提交的数据

    即一个事务的操作影响了另一个事务的查询，这时候就发生了不可重复读和幻读

    这将会导致，当有多个连接开启事务时，某一连接的事务的查询会受到其他所有连接的事务的影响，这无疑将会导致混乱

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

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

3.2读已提交（read committed）

例子

  1. 在上个例子开启的两个连接中，将其中一个连接的隔离级别修改为 读已提交，

另一个保持隔离级别为可重复读

  1. 两边都开启事务

  1. 在隔离级别为可重复读的连接中插入一条数据

    然后在隔离级别为读已提交的连接中 插询表account，可以看到查询到的数据还是本连接开启事务时的数据

    即，读已提交的隔离级别不会出现脏读现象

  2. 在隔离级别为可重复读的连接中更新一条数据

    ​ 然后在隔离级别为读已提交的连接中 插询表account，可以看到查询到的数据变成了其他连接的事务提交的 数据，说明，在隔离级别为读已提交下，出现了不可重复读和幻读

3.3可重复读（repeatable read）

  1. 重新开启两个连接，两个连接的隔离级别均为可重复读

  1. 然后两边均开启事务

  1. 在一个连接中选择数据库，然后在account表中插入一条数据，再更新一条数据（未提交）

    此时该连接中的表情况为：

    在另一个连接中选择数据库，查询表account，可以看到查询到的表数据依旧是开启事务时的样子，没有受到其他事务的影响，即没有产生脏读

  2. 在原先修改数据的连接中输入commit提交

    在另一个连接中再查询表account，可以看到数据依旧是开启事务的时刻的样子

    即，没有产生不可重复读和幻读

综上，隔离级别为可重复读的情况下 既不会出现脏读，也不会出现不可重复读和幻读

3.4可串行化（serializable）

  1. 将上面两个连接其中一个重新启动，将新连接设置隔离级别为可串行化（serializable）

    此时两个连接的隔离级别分别为 可重复读 和可串行化（serializable）

  2. 这时分别在两个连接中均开启事务

  3. 在隔离级别为可重复读的连接中分别插入、更新数据（未提交）

    ​ 在另一个隔离级别为可串行化的连接中选择数据库。然后查询表account，回车时会发现卡住了，这是因为 可串行化会加锁

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

    ​ 这时，在隔离级别为可重复读的连接中提交事务

​ 可以看到可串行化级别的连接中可以成功操作表了

​

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

4.设置隔离

标签： # MySQL

留言评论

  • 这篇文章还没有收到评论，赶紧来抢沙发吧~