本文共 2703 字,大约阅读时间需要 9 分钟。
小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交(read-uncommitted) | 是 | 是 | 是 |
不可重复读(read-committed) | 否 | 是 | 是 |
可重复读(repeatable-read) | 否 | 否 | 是 |
串行化(serializable) | 否 | 否 | 否 |
mysql> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set, 1 warning (0.00 sec)
mysql> set session transaction isolation level read uncommitted;Query OK, 0 rows affected (0.00 sec)mysql> select @@tx_isolation;+------------------+| @@tx_isolation |+------------------+| READ-UNCOMMITTED |+------------------+1 row in set, 1 warning (0.00 sec)
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from book;+----+-----------+-----------+| id | name | author |+----+-----------+-----------+| 1 | 红楼梦 | 曹雪芹 || 2 | 2 | 2 || 3 | 3 | 3 |+----+-----------+-----------+3 rows in set (0.00 sec)mysql> insert into book values(4,4,4);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.01 sec)
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from book;+----+-----------+-----------+| id | name | author |+----+-----------+-----------+| 1 | 红楼梦 | 曹雪芹 || 2 | 2 | 2 || 3 | 3 | 3 |+----+-----------+-----------+3 rows in set (0.00 sec)mysql> insert into book values(4,4,4);ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'mysql> select * from book;+----+-----------+-----------+| id | name | author |+----+-----------+-----------+| 1 | 红楼梦 | 曹雪芹 || 2 | 2 | 2 || 3 | 3 | 3 |+----+-----------+-----------+3 rows in set (0.00 sec)
转载地址:http://foorb.baihongyu.com/