概要:在数据库操作中,为了保证并发读取数据的正确性,一致性,提出的事务隔离级别,隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。
一、脏读、不可重复读、幻读
-
脏读
事务A读取事务B尚未提交的数据,如果事务B执行回滚操作,那么A事务读取到的数据就是脏数据。 -
不可重复读
在一个事务中,对同一行数据重复读取两次,但是却得到了不同的结果。 -
幻读
事 务在操作过程中进行两次查询,两次查询的数据记录数不匹配。
二、事务的隔离级别
MySQL事务有四个级别:未提交读(read uncommitted)、已提交读(read committed)、可重复读(repeatable read)、串行化(serializable)
-
默认事务隔离级别
mysql> SELECT @@tx_isolation; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 138591 Current database: FA +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+
-
未提交读(read uncommitted)
在事务A中可以读取到事务B未提交的数据,但是可能产生 脏读, 不可重复读 和 幻读 。测试脏读:
mysql> set global transaction_isolation ='READ-UNCOMMITTED'; Query OK, 0 rows affected (0.00 sec) +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set, 1 warning (0.00 sec)
1、事务A:
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM INFO; +----+------+------+ | ID | NAME | AGE | +----+------+------+ | 1 | lisi | 18 | +----+------+------+ 1 row in set (0.00 sec)
2、事务B
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO INFO(NAME, AGE) VALUES('wangwu', 12); Query OK, 1 row affected (0.00 sec)
3、事务A
mysql> SELECT * FROM INFO; +----+--------+------+ | ID | NAME | AGE | +----+--------+------+ | 1 | lisi | 18 | | 2 | wangwu | 12 | +----+--------+------+ 2 rows in set (0.00 sec)
4、事务B
mysql> ROLLBACK; Query OK, 0 rows affected (0.01 sec)
5、事务A
mysql> SELECT * FROM INFO; +----+------+------+ | ID | NAME | AGE | +----+------+------+ | 1 | lisi | 18 | +----+------+------+ 1 row in set (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)
由上可知:事务B在执行完步骤2后未提交,此时事务A执行查询,查询到B未提交的数据,然后B因某原因回滚了数据,此时事务A读取的两条数据中有一条是不存在的,是脏数据。
-
已提交读(read committed)
读取数据的事务允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其他事务访问该数据行。可以避免脏读,但可能产生不可重复读和幻读测试不可重复读:
mysql> set global transaction_isolation ='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-COMMITTED | +------------------+ 1 row in set, 1 warning (0.00 sec)
1、事务A
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM INFO; +----+------+------+ | ID | NAME | AGE | +----+------+------+ | 1 | lisi | 18 | +----+------+------+ 1 row in set (0.00 sec)
2、事务B
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE INFO SET NAME='maliu' WHERE ID='1'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
3、事务A
mysql> SELECT * FROM INFO; +----+------+------+ | ID | NAME | AGE | +----+------+------+ | 1 | lisi | 18 | +----+------+------+ 1 row in set (0.00 sec)
4、事务B
mysql> COMMIT; Query OK, 0 rows affected (0.01 sec)
5、事务A
mysql> SELECT * FROM INFO; +----+-------+------+ | ID | NAME | AGE | +----+-------+------+ | 1 | maliu | 18 | +----+-------+------+ 1 row in set (0.00 sec)
由上可知:当事务B修改数据后,但未提交,此时事务A是读取不到的,所有该隔离级别可以防止脏读;当事务B提交事务后,此时事务A再次查询,发现数据已经被修改,两次读取结果不一致,产生不可重复读。
-
可重复读(repeatable read)
事务A在开启事务期间,所访问的数据是不可修改的,可以避免不可重复读,但是可能产生幻读。mysql> set global transaction_isolation ='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)
1、事务A
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM INFO; +----+-------+------+ | ID | NAME | AGE | +----+-------+------+ | 1 | maliu | 18 | +----+-------+------+ 1 row in set (0.00 sec)
2、事务B
mysql> UPDATE INFO SET NAME='adam' 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.01 sec)
3、事务A
mysql> SELECT * FROM INFO; +----+-------+------+ | ID | NAME | AGE | +----+-------+------+ | 1 | maliu | 18 | +----+-------+------+ 1 row in set (0.00 sec)
4、事务A
mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM INFO; +----+------+------+ | ID | NAME | AGE | +----+------+------+ | 1 | adam | 18 | +----+------+------+ 1 row in set (0.00 sec)
由上可知:当事务隔离级别为可重复读时,在事务A未提交前,是看不到其他事务提交的数据,可以避免不可重复读,但是可能产生幻读。
当SQL增加FOR UPDATE时,是禁止其他事务对NAME=‘adam’的数据进行操作(新增删除)
SELECT * FROM INFO WHERE NAME = 'adam' FOR UPDATE;
FOR UPDATE是一种行级锁,又叫排它锁。可以解决可重复读级别下的幻读现象。
-
串行化(serializable)
提供严格的事务隔离。它要求事务串行化执行,事务只能一个接着一个地执行,不能并发执行。可以解决脏读,不可重复读,幻读现象,但大大降低了并发性。