mysql之事务隔离级别

本文小结了mysql中事务隔离级别（InnoDb引擎）。

ANSI SQL标准定义了4中隔离级别：
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

ANSI隔离级别
隔离级别                        脏读         不可重复读       幻像读
READ UNCOMMITTED      允许          允许               允许
READ COMMITTED          不允许       允许               允许
REPEATABLE READ          不允许       不允许           允许
SERIALIZABLE                不允许       不允许           不允许

以下给出测试的例子。

1.READ UNCOMMITTED

session 1:
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

session 2:
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> set tx_isolation = 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

==>隔离级别设置为READ-UNCOMMITTED后，可以看到未提交的数据。

2、READ-COMMITTED

session 1;
mysql> SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2:
mysql>  SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 1:
mysql> insert into t values (2);
Query OK, 1 row affected (0.02 sec)

mysql> update t set id=3 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

session2:
mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

==>session1的数据提交后，session2的事务中能够读到最新的数据。

3、REPEATABLE READ

session1:
mysql> SET tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

session2:
mysql> SET tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

session 1:
mysql> update t set id=1  where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from t where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2:

mysql>  select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

==>这里查出的数据还是事务开始时的数据，而不是最新的数据。
这里幻象读的现象被解决了，是因为InnoDb引擎通过间隙锁（nex-key locking)策略防止幻读。
这里和标准的REPEATABLE READ隔离方式的现象有点出入。

4、SERIALIZABLE 

session 1:
mysql> SET tx_isolation = 'SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2:
mysql> SET tx_isolation = 'SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

==>这里会给表t中的记录加锁（即加锁读），通过下列查询可以看到：

mysql> SELECT trx_id, trx_state, trx_rows_locked, trx_rows_modified FROM information_schema.INNODB_TRX;
+--------+-----------+-----------------+-------------------+
| trx_id | trx_state | trx_rows_locked | trx_rows_modified |
+--------+-----------+-----------------+-------------------+
| 9598   | RUNNING   |               3 |                 0 |
+--------+-----------+-----------------+-------------------+

session 1:
mysql> update t set id=2 where id =1;
Query OK, 1 row affected (27.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

==》这里时间用了27.05 sec，是因为在等待session2释放锁，直到session 1发出commit或者rollback命令后，才能获得锁。

总结：
1、REPEATABLE READ为mysql默认的隔离级别方式,不允许重复读和幻象读。
2、InnoDb引擎通过间隙锁（nex-key locking)策略防止幻读。
3、SERIALIZABLE 隔离方式下会加锁读，类似于select … for update,而其他的隔离级别不会。

end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25923810/viewspace-2142943/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25923810/viewspace-2142943/