MySQL半一致读实验

参考资料：

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

https://www.yisu.com/zixun/262123.html

1、实验环境

MySQL 5.7
RC隔离级别

共识：非只读事务中，innodb 读取每条记录时都会加锁，RR隔绝级别下不会主动释放行锁，在RC隔离级别下，不符合where条件的记录，会在 Server 层释放行锁。
Using READ COMMITTED has additional effects:

For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.

For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.

2、插入测试数据

MySQL [ceshi]> create table t1(id int primary key auto_increment,name varchar(200),age int);
MySQL [ceshi]> insert into t1 select null,'a',1;

MySQL [ceshi]> insert into t1 select null,'b',2;

MySQL [ceshi]> insert into t1 select null,'c',3;

MySQL [ceshi]> insert into t1 select null,'d',4;

MySQL [ceshi]> insert into t1 select null,'e',5;

3、开始实验

Session1:
开启事务，修改 age=4 这条记录，此时 session1 只会给 age=4 加行锁。
原因：
age 列没有索引，读取数据会全表扫描，innodb 读到的每条记录都会加上行锁， age(1,2,3,5) 这些行并不符合where条件记录，在Innodb 返回数据到Server层的时候，Server层会释放掉这些行锁。

MySQL [ceshi]> begin;
Query OK, 0 rows affected (0.000 sec)

MySQL [ceshi]> update t1 set age=44 where age=4;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Session2:
删除age=5这条记录，遇到锁等待。
原因：
age 列没有索引，读取数据会全表扫描，innodb 读到的每条记录都会加上行锁，读取 age(1,2,3)这些行都没有问题，因为上边没有行锁，在读到第4行时（age=4）时，因为上边有锁，session2也想为这条记录加锁，所以产生了锁等待，最后锁等待超时。

MySQL [ceshi]> delete from t1 where age=5;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MySQL [ceshi]> rollback;

session3:
更新age=5这条记录，执行成功。(半一致读的优化)
原因：
前边逻辑和删除age=5流程一样，区别是在读取age=4这条记录时，Innodb会执行半一致读取，读取age=4的最新提交版本（虽然age=4 被修改为 age=5，但因为没有提交，所以它的最新版本还是age=4），以例MySQL可以确定是否符合 where 条件，不匹配，不加锁。
条件匹配，会再次进入innodb层加锁，或锁等待。

MySQL [ceshi]> update t1 set age=55 where age=5;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0