理解MySQL事务隔离级别-dbwatcher-ChinaUnix博客

DbWatcher

首页　| 　博文目录　| 　关于我

dbwatcher

博客访问： 763362
博文数量： 99
博客积分： 0
博客等级：民兵
技术积分： 1163
用户组：普通用户
注册时间： 2016-09-22 09:50

个人简介

一个守望数据库的老菜鸟

文章分类

全部博文（99）

其它（1）
Linux（11）
Python（3）
oracle（39）
NoSQL（6）
MySQL（39）
未分配的博文（0）

文章存档

2019年（21）

2018年（17）

2017年（37）

2016年（24）

我的朋友

相关博文

理解MySQL事务隔离级别

分类： Mysql/postgreSQL

2019-12-17 15:08:05

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5827860.html

事务的特性ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。MySQL在事务隔离性的实现由四种事务级别分别为：
READ-UNCOMMITTED：读未提交事务，也成为"脏读",是指一个事务还没提交时，它做的变更就能被别的事务看到；
READ-COMMITTED：读提交事务是指一个事务提交之后，它做的变更才会被其他事务看到。
REPEATABLE-READ：可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
SERIALIZABLE:串行化事务，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

测试环境如下：

下面通过实验来测试不通事务级别下的V1、V2及V3的值。
实验1：读未提交事务级别
##sessionB
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> #事务B
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:08:23 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> update T set c=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:09:02 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.02 sec)

##sessionA
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> #事务A
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:08:48 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; --V1
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:09:11 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; --V2
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; --V3
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

总结：有上可知：V1是2，V2是2，V3是2
若隔离级别是“读未提交”，则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。

实验2：读提交事务级别
##sessionB
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> #事务B
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:15:02 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> update T set c=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:15:43 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.01 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:16:26 |
+---------------------+
1 row in set (0.00 sec)

sessionA
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> #事务A
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:14:53 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:15:49 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V1
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T; ##V2
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T; ##V3
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
总结：
测试知：V1是1，V2和V3是2.
若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。

实验3：可重复度事务级别
##sessionB
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:24:26 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> update T set c=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:24:43 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:25:08 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.02 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:25:16 |
+---------------------+
1 row in set (0.00 sec)

##SeesionA
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:24:13 |
+---------------------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:24:47 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V1
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:25:05 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:25:19 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V2
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V3
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

由上测试知：V1是1 ，V2是1，V3是2
若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。

实验4：串行化事务级别
##sessionA
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: test
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> #事务A
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.01 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:30:44 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]>
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:34:20 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V1
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

sessionB
(mysql5.5)root@localhost [test]>
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:31:15 |
+---------------------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> update T set c=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

此处表明串行话事务级别，A事务完成B事务执行锁等待超时失败。
只有在A提交后B才可以执行。
调整GLOBAL innodb_lock_wait_timeout=3600在进行测试
sessionB
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> #事务B
(mysql5.5)root@localhost [test]> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 3600 |
+--------------------------+-------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:47:44 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> update T set c=2;
Query OK, 1 row affected (16.21 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:48:33 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:48:43 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.02 sec)

##sessionA
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> #事务A
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:47:35 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]>
(mysql5.5)root@localhost [test]>
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:48:06 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V1
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V2
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit; --提交事务A
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V2
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-17 14:48:40 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V3
+------+
| c |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

由上测试可知：在串行话事务级别下
提交A事务之前：V1为1
提交A事务后，B事务提交前V2为1
提交B事务后，V3值为2

--The end

阅读(1186) | 评论(0) | 转发(0) |

上一篇：MySQL支持hash join的使用和优化

下一篇：没有了

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6