## 测试环境
mysql> selectversion();+------------+
| version() |
+------------+
| 5.7.11-log |
+------------+
数据库事务特性 ACID,即
A(Atomicity) -原子性
C(Consistency)- 一致性
I(Isolation) - 隔离性
D(Durability) - 持久性
MySQL 提供了 4 种不同的隔离级别,用来支持多版本并发控制(MVCC,Multi-Version Concurrency Control)。
默认的事务隔离级别是 REPEATABLE-READ(可重读):
mysql> select @@global.tx_isolation, @@session.tx_isolation;+-------------------------+---------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-------------------------+---------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-------------------------+---------------------------+
在该事务级别下,一个事务期间内,该事务不考虑其他提交语句。
0x00、测试准备
1. 创建测试表
mysql> CREATE TABLE`transaction_test` (
`id`int(10) unsigned NOT NULLAUTO_INCREMENT,
`val`varchar(20) NOT NULL,
`created`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2. 开启两个 MySQL 客户端进行测试
0x01、REPEATABLE-READ(可重读)
step 1:
在 Client 1 下开启事务,查询测试表中的数据:
mysql> start transaction;
Query OK,0 rows affected (0.00sec)
mysql> select * fromtransaction_test;
Emptyset (0.00 sec)
step 2:
在 Client 2 下开启事务,并且往测试表中插入数据,但不提交事务:
mysql> start transaction;
Query OK,0 rows affected (0.00sec)
mysql> insert into transaction_test (val) values ('x'),('y'),('z');
Query OK,3 rows affected (0.00sec)
Records:3 Duplicates: 0 Warnings: 0mysql> select * fromtransaction_test;+----+-----+---------------------+
| id | val | created |
+----+-----+---------------------+
| 1 | x | 2017-02-06 00:20:59 |
| 2 | y | 2017-02-06 00:20:59 |
| 3 | z | 2017-02-06 00:20:59 |
+----+-----+---------------------+
3 rows in set (0.00 sec)
step 3:
在 Client 1 下查看表中数据:
mysql> select * fromtransaction_test;
Emptyset (0.00 sec)
仍然是空表。
step 4:
Client 2 提交事务:
mysql> commit;
Query OK,0 rows affected (0.12 sec)
step 5:
Client 1 下查看表中数据:
mysql> select * fromtransaction_test;
Emptyset (0.00 sec)
任然是空表。
step 6:
Client 1 提交事务,查看表中数据:
mysql> commit;
Query OK,0 rows affected (0.00sec)
mysql> select * fromtransaction_test;+----+-----+---------------------+
| id | val | created |
+----+-----+---------------------+
| 1 | x | 2017-02-06 00:20:59 |
| 2 | y | 2017-02-06 00:20:59 |
| 3 | z | 2017-02-06 00:20:59 |
+----+-----+---------------------+
3 rows in set (0.00 sec)
当 Client 1 完成事务后,才能看到其他事务提交的数据。
0x02、READ-COMMITTED(读取提交内容)
step 1:
Client 1 中清空表,改变数据库隔离级别:
mysql> truncate tabletransaction_test;
Query OK,0 rows affected (0.10sec)
mysql> set @@session.tx_isolation = 'READ-COMMITTED';
Query OK,0 rows affected (0.00sec)
mysql> select @@global.tx_isolation, @@session.tx_isolation;+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ | READ-COMMITTED |
+-----------------------+------------------------+
1 row in set (0.00 sec)
step 2:
Client 1 开启事务,查询表中数据:
mysql> start transaction;
Query OK,0 rows affected (0.00sec)
mysql> select * fromtransaction_test;
Emptyset (0.00 sec)
step 3:
Client 2 开启事务,向表中插入数据,但不提交事务:
mysql> start transaction;
Query OK,0 rows affected (0.00sec)
mysql> insert into transaction_test (val) values ('x'),('y'),('z');
Query OK,3 rows affected (0.00sec)
Records:3 Duplicates: 0 Warnings: 0mysql> select * fromtransaction_test;+----+-----+---------------------+
| id | val | created |
+----+-----+---------------------+
| 1 | x | 2017-02-06 00:31:00 |
| 2 | y | 2017-02-06 00:31:00 |
| 3 | z | 2017-02-06 00:31:00 |
+----+-----+---------------------+
3 rows in set (0.00 sec)
step 4:
Client 1 下查看表中数据:
mysql> select * fromtransaction_test;
Emptyset (0.00 sec)
仍然是空表。
step 5:
Client 2 提交事务:
mysql> commit;
Query OK,0 rows affected (0.13 sec)
step 6:
Client 1 下查看表中数据:
mysql> select * fromtransaction_test;+----+-----+---------------------+
| id | val | created |
+----+-----+---------------------+
| 1 | x | 2017-02-06 00:31:00 |
| 2 | y | 2017-02-06 00:31:00 |
| 3 | z | 2017-02-06 00:31:00 |
+----+-----+---------------------+
3 rows in set (0.00 sec)
与 REPEATABLE-READ 不同的是,Client 1 没有结束事务也能看到其他事务提交的数据。
0x03、READ-UNCOMMITTED(读取未提交内容)
step 1:
Client 1 下清空表,设置隔离级别:
mysql> truncate tabletransaction_test;
Query OK,0 rows affected (0.10sec)
mysql> set @@session.tx_isolation = 'READ-UNCOMMITTED';
Query OK,0 rows affected (0.00sec)
mysql> select @@global.tx_isolation, @@session.tx_isolation;+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ | READ-UNCOMMITTED |
+-----------------------+------------------------+
1 row in set (0.00 sec)
step 2:
Client 1 下开启事务,查询表数据:
mysql> start transaction;
Query OK,0 rows affected (0.00sec)
mysql> select * fromtransaction_test;
Emptyset (0.00 sec)
step 3:
Client 2 下开启事务,向表中插入数据,但是不提交事务:
mysql> start transaction;
Query OK,0 rows affected (0.00sec)
mysql> insert into transaction_test (val) values ('x'),('y'),('z');
Query OK,3 rows affected (0.00sec)
Records:3 Duplicates: 0 Warnings: 0mysql> select * fromtransaction_test;+----+-----+---------------------+
| id | val | created |
+----+-----+---------------------+
| 1 | x | 2017-02-06 00:43:59 |
| 2 | y | 2017-02-06 00:43:59 |
| 3 | z | 2017-02-06 00:43:59 |
+----+-----+---------------------+
3 rows in set (0.00 sec)
step 4:
Client 1 中查询数据:
mysql> select * fromtransaction_test;+----+-----+---------------------+
| id | val | created |
+----+-----+---------------------+
| 1 | x | 2017-02-06 00:43:59 |
| 2 | y | 2017-02-06 00:43:59 |
| 3 | z | 2017-02-06 00:43:59 |
+----+-----+---------------------+
3 rows in set (0.00 sec)
与 READ-COMMITTED 不同的是,在 Client 2 不提交事务的情况下,Client 1 也能读到其他事务插入的数据,即脏数据或者说产生了“脏读”。在一个事务期间读到了另一个事务在未提交之前产生的数据,那么第一个事务就读到了脏数据,产生了对第二个事务未提交数据的依赖,如果第二个事务回滚,那么第一个事务读到的数据是错误的脏数据。
“脏读”与“幻读”、“不可重复读”的区别是:幻读是读取结果集条数的对比,一个事务按相同的查询条件查询之前检索过的数据,发现检索出来的结果集条数变多或者减少(由其他事务插入、删除的),类似产生幻觉。
不可重复读是读取的数据本身的对比,一个事务在读取某些数据后的一段时间后,再次读取这个数据,发现其读取出来的数据内容已经发生了改变,就是不可重复读。
step 5:
Client 2 回滚事务:
mysql> rollback;
Query OK,0 rows affected (0.04sec)
mysql> select * fromtransaction_test;
Emptyset (0.00 sec)
step 6:
Client 1 查询表数据:
mysql> select * fromtransaction_test;
Emptyset (0.00 sec)
空表。
0x04、SERIALIZABLE(序列化)
step 1:
Client 1 下清空表,设置隔离级别:
mysql> truncate tabletransaction_test;
Query OK,0 rows affected (0.21sec)
mysql> set @@session.tx_isolation ='SERIALIZABLE';
Query OK,0 rows affected (0.00sec)
mysql> select @@global.tx_isolation, @@session.tx_isolation;+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ | SERIALIZABLE |
+-----------------------+------------------------+
1 row in set (0.00 sec)
step 2:
Client 1 开启事务,查询表:
mysql> start transaction;
Query OK,0 rows affected (0.00sec)
mysql> select * fromtransaction_test;
Emptyset (0.00 sec)
step 3:
Client 2 开启事务,向表中插入数据:
mysql> start transaction;
Query OK,0 rows affected (0.00sec)
mysql> insert into transaction_test (val) values ('x'),('y'),('z');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
此时 Client 2 插入数据(INSERT 操作)会被阻塞,直到第一个(Client 1)事务提交后,Client 2 的插入操作才能完成。
step 4:
Client 1 提交事务:
mysql> commit;
Query OK,0 rows affected (0.00 sec)
step 5:
Client 2 插入数据:
mysql> insert into transaction_test (val) values ('x'),('y'),('z');
Query OK,3 rows affected (0.00sec)
Records:3 Duplicates: 0 Warnings: 0mysql> select * fromtransaction_test;+----+-----+---------------------+
| id | val | created |
+----+-----+---------------------+
| 4 | x | 2017-02-06 00:54:17 |
| 5 | y | 2017-02-06 00:54:17 |
| 6 | z | 2017-02-06 00:54:17 |
+----+-----+---------------------+
3 rows in set (0.00 sec)
参考: