MySQL事务详解

MySQL事务详解

概念

  • 在讲概念之前，我们先举一个栗子——银行转账
    a -> -100
update user set money=money-100 where name='a';
b -> +100
update user set money=money+100 where name='a';
    • 转账涉及两个部分，一个是从A这里扣钱，一个是给B加钱
    • 扣钱和加钱是两个不同的业务，一旦第一个业务成功了，但是在执行第二个业务之前突然出现了错误，导第二个业务无法执行，那么就会出现A被扣了钱，但是B却没有加钱的情况
    • 所以我们希望，这多条语句能够成为一个整体，一荣俱荣，一损俱损
    • 这就是事务
  • 概念： 事务是一个最小的不可分割的工作单元，事物能够保证一个业务的完整性

基本使用

  • mysql是默认开启事务的(自动提交)

    • 可以使用 select @@autocommit;来查看
       +--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
    • 1代表自动提交，0代表手动提交
    • 可以使用 set autocommit = 0;来设置为手动提交

  • 上文中有提到，自动提交和手动提交，那么什么是提交呢？

    • 在了解提交之前，我们先了解回滚
      • 回滚，命令为 rollback;
      • 作用就是，当回到事务上一次提交时的状态
      • 一般用作出错的时候，或者服务连接断开的时候，再或者就是我们手动调用了
      • 比如说，我们之前提到的银行转账问题，一旦转账中间出现错误，就会执行回滚，恢复到双方钱财都没有变化的状态
    • 在回滚中又提到了一次提交，那么提交到底是什么呢？
      • 提交，命令为 commit;
      • 提交呢，就相当于我们写好了一个自认为很完美的方案（sql语句），在开会的时候侃侃而谈（执行sql），大家也都认可了我们的方案（数据存入数据库中），但是！此时此刻，我们望着拥有一票否决权的董事长，等待着他的决定，如果领导点头了（事务提交，数据真正的留在了数据库中），但是如果摇头了（回滚，数据库中的数据恢复到一开始的样子）
      • 提交又分为自动提交和手动提交
        • 自动提交，就相当于我们的董事长是一个甩手掌柜，除了出错都是点头的状态
        • 手动提交呢，就是我们的董事长亲力亲为，每次都需要手动的提交才能将数据真正的留在数据库中

  • 代码的使用

    • 举例子只是为了理解，要学会还是得写代码
      create database bank;

use bank;

create table user (id int primary key, name varchar(20), money int);

insert into user values (1, 'a', 1000);
select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

//当我们不对是否自动提交进行更改时，我们发现当使用rollback回滚时，数据不变，这便是默认的自动提交
//此时我们设置MySQL自动提交为false

set autocommit = 0;

select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

insert into user values(2,'b',1000);

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

//因为将自动提交设置为false，所以需要我们手动的进行提交，
//如果不进行提交的话，数据只是暂时的存在于数据库中，一旦我们使用回滚，那么数据就会恢复到之前提交的状态

insert into user values(2,'b',1000);

commit；

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

//可以看到，当我们手动提交后，再进行回滚时，数据不再发生改变

  • 在 自动提交的时候，我们也可以使用手动提交

    • 还记得曾经学的全局变量和局部变量吗？
    • 我们之前使用的set autocommit = 0/1;就是一个全局的控制
    • 而 begin; start transaction;就是一个局部的控制

  • 使用代码说明

    begin;

start transaction;

insert into user values(3,'c',1000);

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
|  3 | c    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

insert into user values(3,'c',1000);

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
|  3 | c    |  1000 |
+----+------+-------+
3 rows in set (0.01 sec)

commit;

rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
|  3 | c    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)
    • 可以看到，在不进行提交的时候，数据回滚时，新添加的数据消失了
    • 而手动提交后，再进行数据回滚时，新添加的数据并没有消失

事物的四大特征

  • A
    • 原子性：事务是最小的单位，不可以再分割
  • C
    • 一致性：事务要求，同一事务中的sql语句，必须保证同时成功或者同时失败
  • I
    • 隔离性：事务 1 和事务 2 之间是具有隔离性的
  • 持久性
    • 事务一旦结束(commit,rollback),就不可以返回

事务的隔离性

  • 这个偏概念，还是直接上笔记吧

  • read uncommitted；

    • 读未提交的
    • 如果有事务a和事务b，a事务对数据进行操作，在操作的过程中，事务没有被提交，但是b可以看见a操作的结果
    • 脏读
      • 一个事务读到了另外一个事务没有提交的数据，就叫做脏读

  • 代码演示
    在这里插入图片描述

    • 事务一
    mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

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

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(4, 'd', 1000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
|  3 | c    |  1000 |
|  4 | d    |  1000 |
+----+------+-------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
|  3 | c    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql>
    • 事务二
    mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
|  3 | c    |  1000 |
|  4 | d    |  1000 |
+----+------+-------+
4 rows in set (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
|  3 | c    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql>
    • 从代码中我们可以看到，当设置为读未提交时，另一个事务可以读取到第一个事务未提交的事务，这就相当于某个人买我们的东西然后给我们转账，前脚我们一看，嗯，钱到账了，过了一会他直接rollback，钱就回去了，这时除非我们再去查看否则是不会发现钱已经少了的，这就是脏读

  • read committed

    • 都已经提交的
    • 虽然我只能读到另外一个事务提交的数据，但还是会出现问题，就是读取同一个表的数据发现前后不一致
    • 不可重复读现象：read committed
      • 代码演示在这里插入图片描述
      • 事务一
      mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

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

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into user values(5, 'e', 1000);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
      • 事务二
      mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
|  3 | c    |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
|  3 | c    |  1000 |
|  5 | e    |  1000 |
+----+------+-------+
4 rows in set (0.00 sec)

mysql>
      • 可以看到在commit执行前后，我们在短时间内重复的查询的两次结果是不一样的，这就是不可重复读

  • repeatable read；

    • 可以重复读
    • 幻读
      • 事务a和事务b同时操作一张表，比如此时事务a将表中的数据名称全部改为a，而此时数据b又向数据库中添加了一条名称为e的数据就，事务a在查看时就会发现，明明自己已经将名称全部改为a了，但为什么还有个叫e的，这就是幻读

  • 代码演示
    在这里插入图片描述

    • 事务一
    mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update user set name = 'a';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4  Changed: 3  Warnings: 0

mysql> commmit;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'commmit' at line 1
mysql> commit
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | a    |  1000 |
|  3 | a    |  1000 |
|  5 | a    |  1000 |
|  6 | f    |  1000 |
+----+------+-------+
5 rows in set (0.00 sec)

mysql>
    • 事务二
    mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into user values(6, 'f', 1000);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
    • 可以看到，事务一将名称全部设置为a，但此时事务二又新添加了一条名为f的数据，当事务一查询表的时候，就会发现仍旧有数据的名称没有变成 a，仿若幻觉一样，这就是幻读

  • serializable；

    • 串行化
      • 当user 表被另一个事务操作的时候，其他事务里面的写操作，是不可以进行的，进入排队状态(串行化)，直到另一个事务结束之后，其他事务的写入操作才会执行，在没有等待超时的情况下

  • 性能

    • read-uncommitted > read-committed > repeatable-read > serializable
    • 隔离级别越高，性能越差
    • MySQL默认隔离级别是 repeatable-read

  • 查看数据库的隔离级别

  • //系统级别的
select @@global.transaction_isolation;

//会话级别的
select @@transaction_isolation;

+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

  • 默认隔离级别

    • REPEATABLE-READ

修改隔离级别

  • //当前会话
set session transaction isolation level read committed;

//全局的
set global transaction isolation level read committed;

补充 spring中事务的传播行为

  • REQUIRED：如果当前没有事务，就新建一个事务，如果已经存在一个事务中，加入到这个事务中。一般的选择（默认值）

  • SUPPORTS：支持当前事务，如果当前没有事务，就以非事务方式执行（没有事务）

  • MANDATORY：使用当前的事务，如果当前没有事务，就抛出异常

  • REQUERS_NEW：新建事务，如果当前在事务中，把当前事务挂起。

  • NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起

  • NEVER：以非事务方式运行，如果当前存在事务，抛出异常

  • NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行 REQUIRED 类似的操作

  • 超时时间：默认值是-1，没有超时限制。如果有，以秒为单位进行设置

  • 是否只读：建议查询时设置为只读

版权声明：本文为penanut原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。