SQL 事务隔离级别

最新推荐文章于 2022-10-23 20:59:24 发布

power_to_go

最新推荐文章于 2022-10-23 20:59:24 发布

阅读量295

点赞数

分类专栏： mysql 文章标签： sql

本文链接：https://blog.csdn.net/power_to_go/article/details/100883791

版权

mysql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

Mysql 默认开启自动提交和事务隔离级别 repeatable-read

mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

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

sql 语句的执行

sql 语句的执行是放在 Transaction 中的。sql 语句默认开启事务。也可以手动开启事务。每一个事务都是独立的。多个事务可以并行执行。不同的事务隔离级别会有不同的效果。和事务提交与否，关系很大。

手动开启事务执行sql

start transaction；
执行 sql 语句。
commit；或者 rollback；

事务隔离级别

read uncommitted
read committed
repeatable read
serializable

mysql 手动设置 isolation level

set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

用到的表


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account`  (
  `id` int(11) NOT NULL,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `balance` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

read uncommitted

事务 1 和事务 2 都是 set session transaction isolation level read uncommitted;

事务1

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     350 |
+----+------+---------+
1 row in set (0.00 sec)

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

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

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     300 |
+----+------+---------+
1 row in set (0.00 sec)

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

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     350 |
+----+------+---------+
1 row in set (0.00 sec)

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

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

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

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     300 |
+----+------+---------+
1 row in set (0.00 sec)

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

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     350 |
+----+------+---------+
1 row in set (0.00 sec)

事务2

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     350 |
+----+------+---------+
1 row in set (0.00 sec)

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

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

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     300 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     350 |
+----+------+---------+
1 row in set (0.00 sec)

事务1 和事务2 执行顺序

事务 1 更改了 id 为 1 的记录，此时的 balance = 300。
事务 2 却看到了被事务 1 修改的记录。此时的 balance = 300。
事务 1 rollback 了。此时的 balance = 350；
事务 2 再次查看 balance = 350。
事务 2 两次查看 balance 却不一样。因为事务的隔离级别是 read uncommitted。此时也就是发生了 dirty read。

事务 1 发生了改变，但是事务没有提交。事务 2 却读取到了事务 1 尚未提交的（read uncommitted）修改。想要解决这个问题，那就要用 read committed 这个事务隔离级别。

图解 read-uncommitted

在这里插入图片描述

如上图：把 Transaction1 和 Transaction2 视为两个线程。这其实就是个共享数据 balance = 350 的更新问题。两个都是 read-uncommitted。Transaction1 中对 balance 的改变，Transaction2 都可以查询得到。对于 Transaction2 来说,读到了 Transaction1 还没有提交的数据。发生了 dirty read。

read committed

事务 1 和事务 2 都是 set session transaction isolation level read committed;

事务1

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

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     600 |
+----+------+---------+
1 row in set (0.00 sec)

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

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

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

事务2

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

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     600 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     600 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     700 |
+----+------+---------+
1 row in set (0.00 sec)

事务1 和事务2 执行顺序

事务1 发生了修改，但是没有提交。此时 balance = 700。
事务2 查询出的 balance = 600；
事务1 commit 这个事务。此时 balance = 700；
事务2 查询出的 balance = 700；(read committed)。也就是事务 2 可以查询到事务 1 已经提交的事务的结果。

read committed。这个事务隔离级别可以查到其他提交事务的结果。但是这会导致事务2 两次重复读取 id = 1的记录前后两次查询的结果不一样。这就是所谓的不可重复读的问题。要解决这个问题，可以使用 read repeatable 这个事务隔离级别。

图解 read-committed

在这里插入图片描述

repeatable read

事务1 和事务2 的事务隔离级别是 set session transaction isolation level repeatable read;

事务1

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     800 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> set 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> update account set balance = balance + 100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     900 |
+----+------+---------+
1 row in set (0.00 sec)

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

事务2

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     800 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> set 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 account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     800 |
+----+------+---------+
1 row in set (0.01 sec)

mysql> select * from account where id = 1;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     800 |
+----+------+---------+
1 row in set (0.00 sec)

事务1 和事务2 执行顺序

事务1 修改了 balance ，此时事务没有提交。balance = 900。
事务2 此时读取到的 balance = 800。
事务1 commit 事务。
事务2 此时读取到的 balance 仍然是 800。

以事务1 提交前后为界，事务2两次读取到的 balance 是一样的。不论事务1 提交事务也好，不提交也罢。其实就是这个不受其他事务的影响。事务 1 无论重复读取多少次 id = 1的记录，都是相同的结果。这样称之为可重复读。

图解 repeatable read

在这里插入图片描述

出现的问题

事务1

mysql> set 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> insert into account values(2,'libai',888);
Query OK, 1 row affected (0.01 sec)

事务2

mysql> set 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> insert into account values(2,'dufu',999);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> select * from account; #看不到事务1 添加的 id = 2 的记录
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | liei |     800 |
+----+------+---------+
1 row in set (0.00 sec)

事务 2 看不到事务 1 新插入的数据。也要插入 id = 2 的数据。但是就是报错。要解决这个问题需要用到 serializable 这个事务级别。
同时开启两个事务。
事务1 和事务2 都是 repeatable read。
事务1 插入 id = 2 的记录，然后提交。
事务 2 并看不到插入的 id =2 的记录。(无论怎样都没法插入 id = 2 的记录。真是见了鬼了啊)
这种情况称之为幻读。

serializable

事务 1 和事务 2 的事务隔离级别是 set session transaction isolation level serializable;

事务1

mysql> set 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 account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | liei  |     900 |
|  2 | libai |     888 |
+----+-------+---------+
2 rows in set (0.00 sec)

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

事务2

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


mysql> select * from account;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | liei  |     900 |
|  2 | libai |     888 |
+----+-------+---------+
2 rows in set (0.00 sec)

mysql> insert into account values(3,'baijuyi',999);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

事务级别为 serializable 此时会锁住整张表。只能有一个事务操作这个表。

References

SQL 事务隔离

power_to_go

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL 事务隔离级别

Mysql 默认开启自动提交和事务隔离级别 repeatable-readsql 语句的执行sql 语句的执行是放在 Transaction 中的。sql 语句默认开启事务。也可以用start transaction；手动开启事务。执行 sql 语句。commit；或者 rollback；事务隔离级别read uncommittedread committedread r...
复制链接

扫一扫