ITPub博客

首页 > 数据库 > 数据库开发技术 > MySQL的repeatable read

MySQL的repeatable read

原创 数据库开发技术 作者:zchbaby2000 时间:2020-08-16 12:58:40 0 删除 编辑

####在 REPEATABLE-READ隔离级别下, session 1
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

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

mysql> select * from tb2;
+------+------+
| id   | c1   |
+------+------+
|   20 |    0 |
|   30 |    0 |
|   10 |    0 |
+------+------+
3 rows in set (0.00 sec)

####在 REPEATABLE-READ隔离级别下, session 2执行下列操作
mysql> START TRANSACTION;
mysql> insert into tb2 values(40,0);
mysql> commit;

或者
mysql> START TRANSACTION;
mysql> delete from tb2 where id=20;
mysql> commit;

或者
mysql> START TRANSACTION;
mysql> update tb2 set c1=111 where id=10;
mysql> commit;

####在 REPEATABLE-READ隔离级别下, session 1
mysql> select * from tb2;
+------+------+
| id   | c1   |
+------+------+
|   20 |    0 |
|   30 |    0 |
|   10 |    0 |
+------+------+
3 rows in set (0.00 sec)

在session 1的同一个transaction中,两次相同查询得到的结果一样,称之为: repeatable read
==========================================================================
但是在RR隔离级别下,locking read(SELECT with FOR UPDATE or LOCK IN SHARE MODE) 或更新,删除时是会看到已提交的修改的,包括新插入的行。

####在 REPEATABLE-READ隔离级别下, session 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)

mysql> select id,c1 from tb2 where id=10;
+------+------+
| id   | c1   |
+------+------+
|   10 |    0 |
+------+------+
1 row in set (0.00 sec)

mysql>

####session 2:
mysql> update tb2 set c1=101 where id =10;


####session 1:
mysql> select id,c1 from tb2 where id=10;
+------+------+
| id   | c1   |
+------+------+
|   10 |    0 |
+------+------+
1 row in set (0.00 sec)

mysql> select id,c1 from tb2 where id=10 LOCK IN SHARE MODE;
+------+------+
| id   | c1   |
+------+------+
|   10 |  101 |
+------+------+
1 row in set (0.00 sec)

mysql> update tb2 set c1=c1+1000 where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,c1 from tb2 where id=10;
+------+------+
| id   | c1   |
+------+------+
|   10 | 1101 |
+------+------+
1 row in set (0.00 sec)

mysql> select id,c1 from tb2 where id=10 LOCK IN SHARE MODE;
+------+------+
| id   | c1   |
+------+------+
|   10 | 1101 |
+------+------+
1 row in set (0.00 sec)

mysql>
===========================================================================
在RR的隔离级别下,默认采用Next-Key Locks(Record lock和gap lock的结合),它既锁住记录本身,也锁住索引之间的间隙,所以这个gap lock机制默认打开,并不会产生幻行
在MySQL 5.6.3之前,可以使用innodb_locks_unsafe_for_binlog参数可以禁用gap lock。
innodb_locks_unsafe_for_binlog was deprecated in MySQL 5.6.3. The READ COMMITTED isolation level provides similar functionality.

以下设置成RC隔离级别,模拟幻读
==========================================================================
####Session 1: 设置隔离级别
mysql> set global transaction isolation level read committed;
mysql> set session transaction isolation level read committed;
mysql> select @@global.transaction_isolation,@@transaction_isolation;

####Session 1: 发起一个事务,查看数据
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id>2 for update;
+------+------+
| id   | name |
+------+------+
|    3 | VV   |
+------+------+
1 row in set (0.00 sec)

####Session 2: 插入一行数据
mysql> insert into t values(4,'YY');
Query OK, 1 row affected (0.01 sec)

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

####Session 1: 再次查询,出现幻读
mysql> select * from t where id>2 for update;
+------+------+
| id   | name |
+------+------+
|    3 | VV   |
|    4 | YY   |
+------+------+
2 rows in set (0.00 sec)
========================================================================

gap锁的出现主要是为了避免幻读,gap锁只会阻塞insert操作, 数据库施加gap lock的条件:
1 事务隔离级别为REPEATABLE-READ,且sql走的索引为非唯一索引
或者
2 事务隔离级别为REPEATABLE-READ,且sql是一个范围的当前读操作,这时即使不是非唯一索引也会加gap lock

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

下一篇: MySQL表锁
请登录后发表评论 登录
全部评论
喜欢各种数据库相关的开发,架构设计,运维和性能优化
  • 博文量
    244
  • 访问量
    92851