理解MySQL事务隔离级别

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：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