mysql+after+commit_MYSQL下read committed 和repeatable read级别下一致性非锁定读笔记+实测...

14.2.2.2 Consistent Nonlocking Reads

Aconsistent readmeans thatInnoDBuses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, aSELECTsees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

If the transactionisolation levelisREPEATABLE READ(the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

WithREAD COMMITTEDisolation level, each consistent read within a transaction sets and reads its own fresh snapshot

我们在可重复读下做个实验：

同时开启两个事务，打开两个mysql连接，分别begin；

事务A：         begin；

select count(*) from Account where user_id = 'a';  //此时查到一条记录

事务B begin；

此时在事务B中：

update Account set user_id = 'p_1' where user_id = 'a';

此时在事务A中继续执行相同条件查询，结果和上一张图片相同(B未提交，当然看不到了)，之后，B中commit

我们再来A中按照user_id = 'a' 和user_id = 'p_1'两种方式进行查询。

从这里可以看到，在A中，一致性非锁定读读取的仍然是同一个快照版本，即旧的版本，此时存在新的版本，user_id 为'p_1',这时候我们按照user_id=‘p_1’查不到数据，但是，在同一个事务中，我们不是select，而用update，就会查到数据，因为此时没有使用一致性非锁定读，select对应的是旧快照的版本，而update修改的确是最新的版本，我们继续执行update

先确认下查不到数据

此时执行更新：

这是正如上面所说，update竟然是有数据的。