本文链接地址:http://blog.chinaunix.net/uid-31396856-id-5827860.html
事务的特性ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性)。MySQL在事务隔离性的实现由四种事务级别分别为:
READ-UNCOMMITTED:读未提交事务,也成为"脏读",是指一个事务还没提交时,它做的变更就能被别的事务看到;
READ-COMMITTED:读提交事务是指一个事务提交之后,它做的变更才会被其他事务看到。
REPEATABLE-READ:可重复读是指,一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。
SERIALIZABLE:串行化事务,顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。
测试环境如下:
![理解MySQL事务隔离级别-LMLPHP 理解MySQL事务隔离级别-LMLPHP](https://c1.lmlphp.com/user/master/2020/09/07/son_1/00db9596e76fdd5dfe741ddf6b9a0a23.jpg)
下面通过实验来测试不通事务级别下的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