mysql repeatable_MySQL的repeatable read

####在 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