美文网首页程序员
MySQL 事务隔离级别解析和实战

MySQL 事务隔离级别解析和实战

作者: 非凡公社 | 来源:发表于2019-03-13 18:00 被阅读5次

    MySQL 事务隔离级别解析和实战

    1、MySQL 隔离界别查看

    • 查看回话隔离级别
    SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
    • 查看系统隔离级别
    SELECT @@global.tx_isolation;

    2、MySQL 隔离级别修改

    • MySQL 默认的隔离级别是可重复读（ REPEATABLE READ）
    • 在 my.inf 文件中修改隔离级别
    transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}
    image.png
    • 用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。语法如下：
    SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
    image.png

    3、MySQL 四种隔离级别

    隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
    未提交读（Read uncommitted） 可能 可能 可能
    已提交读（Read committed） 不可能 可能 可能
    可重复读（Repeatable read） 不可能 不可能 可能
    可串行化（Serializable ） 不可能 不可能 不可能

    3.1、未提交读

    • 允许脏读，也就是说一个事务有可能读到另一个事务未提交的数据

    3.2、已提交读

    • 只能读到已经提交的数据，Oracle等多数数据库的默认隔离级别

    3.3、可重复读

    • 存在幻读

    3.4、可串行化

    • 完全串行化，每次读都需要获得表级共享锁，读写阻塞

    4、实例操作

    • 新建一个表用来测试
    CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试';

    4.1、脏读

    • 当一个事务访问一个数据，并且进行了修改。另一个事务读到了被修改的数据，并且使用了这个数据。
    • sessoin1 （插入数据但不提交事务）
    mysql> SELECT @@session.tx_isolation; // 查询会话隔离级别可重复读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set, 1 warning (0.04 sec)

mysql> SELECT @@tx_isolation; //查询系统隔离级别为可重复读
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> start transaction; //开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(`name`) values("qiu"); //插入数据成功，此时事务还没有提交
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | qiu  |
+----+------+
1 row in set (0.00 sec)
    • sessoin2（可重复读，证明不会出现脏读）
    mysql> SELECT @@session.tx_isolation; //会话隔离级别为可重复读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT @@tx_isolation; //系统隔离级别为可重复读
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from test; //查询不到 sessoin1 未提交的数据，不会出现脏读现象
Empty set (0.00 sec)
    • sessoin3（为提交读出现脏读现象）
    mysql> SELECT @@session.tx_isolation;//会话隔离级别为未提交读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |   --------读到了 session1 未提交的数据，出现脏读现象
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from test;//读到了 session1 未提交的数据，此为脏读
+----+------+
| id | name |
+----+------+
|  1 | qiu  |
+----+------+
1 row in set (0.00 sec)

    4.2、不可重复读

    • 在同一个事务内，多次读取同一个数据，此时事务还没有完成。另一个事务在前一个事务两次读取之间修改了数据，由于修改了数据，前一个事务读到的数据不一样，因此称为不可重复读。

    • sessoin1（事务内第一次读）

    mysql> SELECT @@session.tx_isolation; //隔离级别为提交读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> start transaction; //开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test; //开启事务内的第一次查询
+----+------+
| id | name |
+----+------+
|  2 | qiu  |
+----+------+
1 row in set (0.01 sec)
    • sessoin2
    mysql> SELECT @@session.tx_isolation;//隔离级别为可重复读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> start transaction; //开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  2 | qiu  |
+----+------+
1 row in set (0.01 sec)

mysql> insert into test(`name`) values ("hello"); //在sessoin1第一次查询后修改了数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |
|  3 | hello |
+----+-------+
2 rows in set (0.00 sec)

mysql> commit; //提交事务
Query OK, 0 rows affected (0.01 sec)
    • sessoin1（事务内第二次读）
    mysql> select * from test; //在事务内第二次读，读到了 sessoin2 提交的数据
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |   ---------------READ-COMMITTED级别出现不可重复读现象
|  3 | hello |
+----+-------+
2 rows in set (0.00 sec)

    4.3、可重复读

    • 验证 REPEATABLE-READ 级别下的可重复读
    • sessoin1（事务内第一次读）
    mysql> SELECT @@session.tx_isolation;//隔离级别为可重复读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> start transaction; //开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |
|  3 | hello |
+----+-------+
2 rows in set (0.00 sec)
    • sessoin2
    mysql> SELECT @@session.tx_isolation; //隔离级别为可重复读
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set, 1 warning (0.00 sec)

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

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |
|  3 | hello |
+----+-------+
2 rows in set (0.00 sec)

mysql> insert into test (`name`) values ("hi"); //sessoin1 第一次读之后改变数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |
|  3 | hello |
|  4 | hi    |
+----+-------+
3 rows in set (0.00 sec)

mysql> commit; //提交事务
Query OK, 0 rows affected (0.00 sec)
    • sessoin1（事务内第二次读）
    mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  2 | qiu   |
|  3 | hello | -------------sessoin1 没有读到 sessoin2 提交的数据，出现可重复读现象
+----+-------+
2 rows in set (0.00 sec)

    4.4、幻读

    • 第一个事务对表中的所有数据进行修改，第二个事务往表里面插入一条数据。此时第一个事务发现表中还有未修改的数据，好像出现了幻觉一样。

    • 幻读现象1：

    session1:                                                   session2:
mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 2 warnings (0.00 sec)
                                                            mysql> start transaction;     
                                                            Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from test;
Empty set (0.00 sec)

                                                            mysql> insert into test (`id`, `name`) values (1, "hi~~~");
                                                            Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

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

mysql> select * from test;
Empty set (0.00 sec)

mysql> insert into test (`id`, `name`) values (1, "hello");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
//what fuck ？？？刚刚查询，告诉我没有数据。等我插入的时候就告诉我主键冲突了。此乃幻读现象
    • 幻读现象2：
    session1：                               session2：
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | hi~~~ |
+----+-------+
1 row in set (0.00 sec)

                                      mysql> insert into test (`id`, `name`) values (2, "hello~~");
                                      Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | hi~~~ |
+----+-------+
1 row in set (0.04 sec)

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

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | hi~~~ |
+----+-------+
1 row in set (0.00 sec)

mysql> update test set name = "up";
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
//what fuck ？？？刚出查询不是只有一条数据吗？怎么更新了两条。此乃幻读现象

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
|  2 | up   |
+----+------+
2 rows in set (0.00 sec)
    • innodb_locks_unsafe_for_binlog：设定InnoDB是否在搜索和索引扫描中使用间隙锁(gap locking)
    • 当隔离级别是可重复读，且禁用innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描index的时候使用的next-key locks可以避免幻读。

    4.5、加锁

    • 通过加锁来防止幻读
    session1：                                               session2:

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

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

//加锁锁住了 id <= 1 的范围
mysql> select * from test where id <= 1 for update;
+----+------+
| id | name |
+----+------+
|  1 | up   |
+----+------+
1 row in set (0.18 sec)
                                                        //id 不在锁内，允许插入
                                                        mysql> insert into test (`id`, `name`) values (3, "lock");
                                                        Query OK, 1 row affected (0.15 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
|  2 | up   |
+----+------+
2 rows in set (0.01 sec)
                                                        //id = 1 已经加了写锁，事务等待锁释放
                                                        mysql> insert into test(`id`, `name`) values (1, "lock");
                                                        ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
|  2 | up   |
+----+------+
2 rows in set (0.00 sec)

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

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
|  2 | up   |
+----+------+
2 rows in set (0.00 sec)

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

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
|  2 | up   |
|  3 | lock |  ------------session2 插入的数据
+----+------+
4 rows in set (0.00 sec)
    • 通过加锁读来获得其他事务提交的结果
    session1：                               session2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
+----+------+
4 rows in set (0.01 sec)

                                        mysql> insert into test (`id`, `name`) values (7, "hello");
                                        Query OK, 1 row affected (0.00 sec)

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


mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
+----+------+
4 rows in set (0.00 sec)

//读到了 session2 提交的数据
mysql> select * from test lock in share mode;
+----+-------+
| id | name  |
+----+-------+
|  1 | up    |
|  7 | hello |
+----+-------+
5 rows in set (0.00 sec)

//读到了 session2 提交的数据
mysql> select * from test for update;
+----+-------+
| id | name  |
+----+-------+
|  1 | up    |
|  7 | hello |
+----+-------+
5 rows in set (0.00 sec)

//读不到 session2 提交的数据
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | up   |
+----+------+
4 rows in set (0.00 sec)
    • 可重复读和提交读本身就是互相矛盾的。保证了可重复读，就读不到其他事务的提交；保证了提交读，两次读取的数据可能会出现不一致。
    • MySQL 默认的隔离级别是可重复读，可通过加锁读来获取其他事务的提交。
    • MySQL 的可重复读并不能避免幻读，可通过加 Next-Key Lock 来避免幻读现象。
    • Next-Key Lock：锁定一个范围，包括记录本身。

    总结

    • 每种数据库隔离级别都解决了一个问题。数据库隔离级别依次增强，性能也依次变差。大部分环境中使用 READ-COMMITTED 是可行的。

    参考文献

    关注公众号

    • 大家可以关注我的公众号【学霸的一天】，更多有趣、有用的知识等你来发现


      宣传二维码.png

    相关文章

      网友评论

        本文标题：MySQL 事务隔离级别解析和实战

        本文链接：https://www.haomeiwen.com/subject/ocitmqtx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        程序员

        关于我们|服务条款|联系我们|MySQL 事务隔离级别解析和实战|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！