MySQL事务隔离级别以及Mybatis代码演示(一)

最新推荐文章于 2024-01-31 16:53:45 发布

Young~^_^

最新推荐文章于 2024-01-31 16:53:45 发布

阅读量2.7w

点赞数 4

分类专栏： mysql

本文链接：https://blog.csdn.net/liuyanglglg/article/details/100519528

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、事务隔离级别

1. 读未提交(read uncommitted)

2. 读已提交(read committed)

3. 可重复读(reaptable read)

4. 串行化(serializable)

二、演示

1. 初始化表

CREATE TABLE `goods` (
  `id` int(11) NOT NULL COMMENT '自增主键',
  `name` varchar(32) NOT NULL COMMENT '名称',
  `price` decimal(10,2) NOT NULL COMMENT '价格',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `learn`.`goods`(`id`, `name`, `price`)
values (1, '花生', 5.00);
insert into `learn`.`goods`(`id`, `name`, `price`)
values (2, '土豆', 1.80);
insert into `learn`.`goods`(`id`, `name`, `price`)
values (3, '牛肉', 48.00);

2. 读未提交(read uncommitted)

我们打开两个MySQL连接(两个不同session)，这里为了区分分别叫Session1简称S1，Session2简称S2。

步骤1

首先在S1中执行：

select * from goods;

结果如下：

mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  5.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

步骤2

这时我们给花生涨价1块钱，在S2中执行:

start transaction;
update goods set price=price + 1.0 where id = 1;
select * from goods;

结果如下：

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

mysql> update goods set price=price + 1.0 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  6.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

步骤3

切换至S1, 执行：

select * from goods;

结果如下：

mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  5.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

步骤4

我们再S1看到花生的价格仍然是5块。原因是S1现在是MySQL的默认隔离级别，我们执行如下SQL：

select @@tx_isolation;

结果如下，默认事务隔离级别是可重复读

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

这时我们手动设置事务的隔离级别为读未提交，执行如下SQL：

#设置事务隔离级别为读未提交
set session transaction isolation level read uncommitted;
select * from goods;

结果如下：

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

mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  6.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

可以看到，我们花生已经涨价了。

步骤5

这时，我们回滚S2的事务，执行SQL：

rollback;

步骤6

我们再在S1中执行查询语句：

select * from goods;

结果如下：

mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  5.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

在S2事务回滚后，花生的价格又降到了5块。

导致的问题

读未提交(read uncommitted) 会导致Session1读到Session2一个未提交事务对DB操作导致的数据变更。一旦Session2事务回滚，数据被置为事务回滚之前的值，Session1读到的数据便不可信。这种情况称之为脏读。

3. 读已提交(read committed)

解决的问题

在2. 读未提交的演示当中，我们发现当S1使用默认mysql隔离级别时，没有读到未提交事务的脏数据，当把S1的事务隔离级别设置为read uncommitted时，便读到了脏数据。其实我们把S1的事务隔离级别设置到read committed即可，默认隔离级别repeatable read(可重复读)是大于read committed，所以read committed以及其以上的级别可以解决脏读问题。

接下来我们演示读已提交会出现的问题。

步骤1

我们先把数据还原：

update goods set price= 5 where id = 1;

步骤2

在S2中我们给花生涨价1块钱，执行：

start transaction;
update goods set price=price + 1.0 where id = 1;
select * from goods;

结果如下：

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

mysql> update goods set price=price + 1.0 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  6.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

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

步骤3

在S1设置事务隔离级别为读已提交(read committed)，执行：

set session transaction isolation level read committed;
start transaction;
select * from goods;

结果如下：

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

mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  5.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.01 sec)

步骤4

在S2中提交事务，执行：

commit;

步骤5

在S1中再次查询，执行：

select * from goods

结果如下：

mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  6.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

导致的问题

我们观察到，S2事务提交前后，在S1一次事务中的两次查询到结果却是不一致的，这种情况称之为：不可重复读，读已提交(read committed) 以及其以下的隔离级别会导致不可重复读。

4. 可重复读(repeatable read)

接下来我们把S1的事务隔离级别提升至可重复读(repeatable read)。重复上述步骤1~5，只变更步骤3

步骤2-3

把上述步骤3中的隔离级别设置为可重复读(repeatable read)，SQL如下：

set session transaction isolation level repeatable read;
start transaction;
select * from goods;

步骤2-5

这里贴出步骤5中的执行结果:

mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  5.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

解决的问题

可以看到步骤五，即使S2事务已经提交，数据已经变更。S1中同一个事务当中，两次读到的结果仍然是一致的，解决了不可重复读的问题。

注意：读未提交只是为了演示，该隔离级别会读到未提交事务的DB操作导致的数据变更，在S1 中无需加事务，但是演示读已提交(read committed)，在S1查询必须加入事务，不加事务的话，S2一旦事务提交，读到数据变化是理所当然的事。演示解决不可重复读问题，S1最好新建连接，或者保证演示之前的事务提交，不要开始一个事务，实际上一个仍未commit, 出现预期之外的结果。

为了演示可重复读(repeatable read) 会出现的问题，我们在原有表结构中加入库存，以方便我们更好的理解该问题。

步骤1-1

我们增加库存stock字段，并设置花生库存为1；

步骤1-2

在S2中执行：

set session transaction isolation level repeatable read;
start transaction;
select * from goods;
update goods set stock=stock-1 where id=1;
select * from goods;

结果如下：

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

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

mysql> select * from goods;
+----+--------+-------+-------+
| id | name   | price | stock |
+----+--------+-------+-------+
|  1 | 花生   |  6.00 |     1 |
|  2 | 土豆   |  1.80 |    20 |
|  3 | 牛肉   | 48.00 |    30 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

mysql> update goods set stock=stock-1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from goods;
+----+--------+-------+-------+
| id | name   | price | stock |
+----+--------+-------+-------+
|  1 | 花生   |  6.00 |     0 |
|  2 | 土豆   |  1.80 |    20 |
|  3 | 牛肉   | 48.00 |    30 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

步骤1-3

在S1中执行步骤1-2中同样的SQL：
结果如下：

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

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

mysql> select * from goods;
+----+--------+-------+-------+
| id | name   | price | stock |
+----+--------+-------+-------+
|  1 | 花生   |  6.00 |     1 |
|  2 | 土豆   |  1.80 |    20 |
|  3 | 牛肉   | 48.00 |    30 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

mysql> update goods set stock=stock-1 where id=1;
#执行到update时，被阻塞了...

我们发现这里update被阻塞了无法执行。

步骤1-4

在S2中执行commit：
我们发现S1被阻塞的SQL执行了，结果如下：

mysql> update goods set stock=stock-1 where id=1;
select * from goods;
#这里阻塞了12秒，正常情况下，更新单条记录会很快的
Query OK, 1 row affected (12.24 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from goods;
+----+--------+-------+-------+
| id | name   | price | stock |
+----+--------+-------+-------+
|  1 | 花生   |  6.00 |    -1 |
|  2 | 土豆   |  1.80 |    20 |
|  3 | 牛肉   | 48.00 |    30 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

我们发现库存也确实减了2次，没有什么问题，只不过update语句会被阻塞。

接下来，我们尝试在插入数据

步骤2-1

在S2中执行：

set session transaction isolation level repeatable read;
start transaction;
select * from goods;
insert into `learn`.`goods`(`id`, `name`, `price`)
values (4, '花菜', 2.90);
select * from goods;

结果如下：

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

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

mysql> select * from goods;
+----+--------+-------+-------+
| id | name   | price | stock |
+----+--------+-------+-------+
|  1 | 花生   |  6.00 |     0 |
|  2 | 土豆   |  1.80 |    20 |
|  3 | 牛肉   | 48.00 |    30 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

mysql> insert into `learn`.`goods`(`id`, `name`, `price`)
    -> values (4, '花菜', 2.90);
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods;
+----+--------+-------+-------+
| id | name   | price | stock |
+----+--------+-------+-------+
|  1 | 花生   |  6.00 |     0 |
|  2 | 土豆   |  1.80 |    20 |
|  3 | 牛肉   | 48.00 |    30 |
|  4 | 花菜   |  2.90 |     0 |
+----+--------+-------+-------+
4 rows in set (0.00 sec)

步骤2-2

在S1中执行步骤2-1中同样的SQL：
结果如下：

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

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

mysql> select * from goods;
+----+--------+-------+-------+
| id | name   | price | stock |
+----+--------+-------+-------+
|  1 | 花生   |  6.00 |    -1 |
|  2 | 土豆   |  1.80 |    20 |
|  3 | 牛肉   | 48.00 |    30 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

mysql> insert into `learn`.`goods`(`id`, `name`, `price`)
    -> values (4, '花菜', 2.90);
select * from goods;
#SQL仍然被阻塞了

步骤2-3

在S2中commit，S1结果如下：

mysql> insert into `learn`.`goods`(`id`, `name`, `price`)
    -> values (4, '花菜', 2.90);
select * from goods;
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
mysql> select * from goods;
+----+--------+-------+-------+
| id | name   | price | stock |
+----+--------+-------+-------+
|  1 | 花生   |  6.00 |    -1 |
|  2 | 土豆   |  1.80 |    20 |
|  3 | 牛肉   | 48.00 |    30 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

我们发现，S1中执行insert时，报主键冲突，但是再次查询依然只有三条记录，What host ? 见了鬼。由此延伸。如果上述步骤1-1 ~ 步骤1-4 是对一张订单表更新订单编号，订单编号表中的订单编号必须唯一，两个操作在两个可重复读的事务中对同一订单编号更新（SQL语句一模一样）也会出现上述插入的情况。

导致的问题

通过上述演示，我们发现两个可重复读的事务中，执行相同的更新或插入操作(两个事物执行过程的重叠)，由于主键或字段唯一约束，执行较晚事物查询时看不到新增或更新的数据，但实际上是无法插入成功的。称之为幻读。
面试字节，面试官我上述幻读的解释表示质疑，确实上述对于幻读的解读有误，纠正一下，幻读是只在重复读的隔离级别下，A事务执行过程中，B事务执行，并插入新的数据，提交事务。A在更新数据时，如果where条件包含B事务插入的数据，B事务提交的数据也会被更新，再次进行SELECT查询，会查到B事务提交数据，进行如下演示：

-- 会话A
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  5.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

-- 切到会话B
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  5.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> insert into `goods`(`id`, `name`, `price`) values (4, 'banana', 2.90);
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  5.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
|  4 | banana |  2.90 |
+----+--------+-------+
4 rows in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)
-- 会话B结束
-- 切到会话A，再次查询还是3条数据
mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  5.00 |
|  2 | 土豆   |  1.80 |
|  3 | 牛肉   | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
-- 执行Update语句，但是我们看到4条数据变更
mysql> update goods set price=price + 1.0;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0
-- 再次查询，我们发现有4条数据了
mysql> select * from goods;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | 花生   |  6.00 |
|  2 | 土豆   |  2.80 |
|  3 | 牛肉   | 49.00 |
|  4 | banana |  3.90 |
+----+--------+-------+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)
-- 上述是在同一个事物中，显然受到了会话B事物的影响

5. 串行执行(serializable)

步骤1

我们删除之前添加花菜，SQL就省略了。

步骤2

我们将事物隔离级别替换为串行化，在S2中执行如下SQL：

set session transaction isolation level serializable;
start transaction;
select * from goods;
insert into `learn`.`goods`(`id`, `name`, `price`)
values (4, '花菜', 2.90);
select * from goods;

在S1中执行步骤1中同样的SQL：
结果如下:

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

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

mysql> select * from goods;
#查询被阻塞

我们发现查询操作都被阻塞了。
在S2中commit，S1结果如下：

mysql> select * from goods;
+----+--------+-------+-------+
| id | name   | price | stock |
+----+--------+-------+-------+
|  1 | 花生   |  6.00 |    -1 |
|  2 | 土豆   |  1.80 |    20 |
|  3 | 牛肉   | 48.00 |    30 |
|  4 | 花菜   |  2.90 |     0 |
+----+--------+-------+-------+
4 rows in set (30.13 sec)

mysql> insert into `learn`.`goods`(`id`, `name`, `price`)
    -> values (4, '花菜', 2.90);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

由于serializable隔离级别，必须上一个事务执行完毕下个事务的操作才可以开始，自然可以避免幻读。但是如果存在serializable隔离级别事务没有commit或rollback或超时，其他任务更新、插入、删除等写操作都会被阻塞(加了表级写锁)，在一个业务系统中，对表的写操作是一件很频繁的事，serializable隔离级别，会严重影响执行效率以及可能导致大量的写操作超时失败。

6. 嵌套事务

当出现嵌套的事务时，MySQL会以最外层的事务的隔离级别为准，内层事务隔离级别无效。

代码演示

使用mybatis作为orm进行演示

读未提交

    public GoodsDO risePriceByReadUncommitted(Long id, BigDecimal floatedPrice) {
        SqlSessionFactory sqlSessionFactory = this.sqlSessionFactory();
        //设置事务隔离级别为读未提交
        SqlSession sqlSessionB = sqlSessionFactory.openSession(TransactionIsolationLevel.READ_UNCOMMITTED);
        SqlSession sqlSessionA = sqlSessionFactory.openSession(TransactionIsolationLevel.READ_UNCOMMITTED);

        GoodsMapper goodsMapperB = sqlSessionB.getMapper(GoodsMapper.class);
        GoodsMapper goodsMapperA = sqlSessionA.getMapper(GoodsMapper.class);

        startOperate(OPERATOR_B);
        selectById(goodsMapperB, id, OPERATOR_B);
        risePrice(goodsMapperB, id, floatedPrice, OPERATOR_B);
        selectById(goodsMapperB, id, OPERATOR_B);
        pauseOperate(OPERATOR_B);

        startOperate(OPERATOR_A);
        selectById(goodsMapperA, id, OPERATOR_A);
        pauseOperate(OPERATOR_A);

        clearCache(sqlSessionB);
        restartOperate(OPERATOR_B);
        rollback(sqlSessionB, OPERATOR_B);
        selectById(goodsMapperB, id, OPERATOR_B);
        stopOperator(OPERATOR_B);

        clearCache(sqlSessionA);
        restartOperate(OPERATOR_A);
        selectById(goodsMapperA, id, OPERATOR_A);
        stopOperator(OPERATOR_A);
    }
    private void startOperate(String operator) {
        System.out.println(LINE);
        System.out.println(operator + ", 开始操作: ");
    }

    private void pauseOperate(String operator) {
        System.out.println(operator + ", 暂停操作: ");
        System.out.println(LINE);
    }

    private void restartOperate(String operator) {
        System.out.println(LINE);
        System.out.println(operator + ", 重新开始操作: ");
    }

    private void stopOperator(String operator) {
        System.out.println(operator + ", 终止操作: ");
        System.out.println(LINE);
    }

    private void selectById(GoodsMapper goodsMapper, Long id, String operator) {
        System.out.println(operator + ", 查询结果: ");
        System.out.println(operator + ", " + goodsMapper.selectById(id));
    }

    private void risePrice(GoodsMapper goodsMapper, Long id, BigDecimal floatedPrice, String operator) {
        System.out.println(operator + ", 涨价: " + floatedPrice.toString() + "元");
        System.out.println(operator + ", " + goodsMapper.risePriceById(id, floatedPrice));
    }

    private void commit(SqlSession sqlSession, String operator) {
        System.out.println(operator + ", 提交事务: ");
        sqlSession.commit();
    }

    private void rollback(SqlSession sqlSession, String operator) {
        System.out.println(operator + ", 回滚事务: ");
        sqlSession.rollback(true);
    }

执行结果:

----------------------------------------
操作员B:, 开始操作: 
操作员B:, 查询结果: 
操作员B:, GoodsDO(id=1, code=DRINK_0001, name=可口可乐, price=3.00, stock=1)
操作员B:, 涨价: 1元
操作员B:, 1
操作员B:, 查询结果: 
操作员B:, GoodsDO(id=1, code=DRINK_0001, name=可口可乐, price=4.00, stock=1)
操作员B:, 暂停操作: 
----------------------------------------
----------------------------------------
操作员A:, 开始操作: 
操作员A:, 查询结果: 
操作员A:, GoodsDO(id=1, code=DRINK_0001, name=可口可乐, price=4.00, stock=1)
操作员A:, 暂停操作: 
----------------------------------------
清除MyBatis SqlSession缓存
----------------------------------------
操作员B:, 重新开始操作: 
操作员B:, 回滚事务: 
操作员B:, 查询结果: 
操作员B:, GoodsDO(id=1, code=DRINK_0001, name=可口可乐, price=3.00, stock=1)
操作员B:, 终止操作: 
----------------------------------------
清除MyBatis SqlSession缓存
----------------------------------------
操作员A:, 重新开始操作: 
操作员A:, 查询结果: 
操作员A:, GoodsDO(id=1, code=DRINK_0001, name=可口可乐, price=3.00, stock=1)
操作员A:, 终止操作: 
----------------------------------------

附件

演示代码地址

Young~^_^

关注

4
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
MySQL事务隔离级别以及Mybatis代码演示(一)

MySql事务一、事务隔离级别1. 读未提交(read uncommited)二、演示1. 初始化表2. 读未提交(read uncommited)一、事务隔离级别1. 读未提交(read uncommited)二、演示1. 初始化表CREATE TABLE `goods` ( `id` int(11) NOT NULL COMMENT '自增主键', `name` varcha...
复制链接

扫一扫

专栏目录