美文网首页
MySQL之快照读

MySQL之快照读

作者: 猿来是八阿哥 | 来源:发表于2020-01-13 13:58 被阅读0次
    mysql

    一、快照读

    1. 定义

    快照读 即:snapshot read,官方叫法是:Consistent Nonlocking Reads,即:一致性非锁定读,官方的解释是:

    A consistent read means that InnoDB uses 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.

    即:

    快照读 是: InnoDB 通过 MVCC多版本控制)将数据库在过去某个时刻的快照应用在查询上,使得:
    这次查询 只能看到 别的事务生成快照前提交的数据,而不能看到 别的事务生成快照后提交的数据或者未提交的数据

    2. 存在的问题

    The exception to this rule is that the query sees the changes made by earlier statements within the same transaction.

    1. If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows.
    2. 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.

    快照读 的问题在于:在同一个事务中,能够读取到之前提交的数据。表现为:

    • 如果在同一个事务中,先更新了表中的一些行,然后进行查询,读到了更新后的数据,应该是读到未更新的数据。即:产生了 不可重复读 的问题。
    • 如果在同一个事务中,先更新了表(drop table alter table),然后进行查询,会发现表的状态已经不是快照时的状态了。
    3. 好处

    A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

    • 快照读repeatable-readread-committed 级别下,默认的查询模式,好处是:读不加锁,读写不冲突,这个对于 MySQL 并发访问提升很大。
    4. 隔离级别

    With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

    • read-committed 隔离级别下,事务中的快照读,总是以最新的快照为基准进行查询的。

    If the transaction isolation level is REPEATABLE 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.

    • repeatable-read 隔离级别下,快照读是以事务开始时的快照为基准进行查询的,如果想以最新的快照为基准进行查询,可以先把事务提交完再进行查询。

    In the default REPEATABLE READ isolation level, if another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.

    • repeatable-read 隔离级别下,别的事务在你生成快照后进行的删除、更新、新增,快照读是看不到的。
    5. 特别注意-1

    The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements.

    字面意思:在事务中,为查询创建的快照,并不适用与 DML 语句。

    也就是说:如果事务 A 开始时创建的快照,查询不到数据 col1=1,但此时事务 B 刚刚提交 insert col1=1insert col1=1,此时如果事务 A 执行,delete col1=1,是能将事务 B 生成的数据删除的。

    时刻 事务 A 事务 B
    t0 start transaction; start transaction;
    t1 select * from t where col1=1;# return: 0 rows found...
    t2 insert into t(col1) values (1);# return: insert success...
    t3 update t set col1=1 where col1=2;# return: 1 rows updated...
    t4 commit;
    t5 delete from t where col1=1;# return: 2 rows deleted...
    6. 特别注意-2

    Session A sees the row inserted by B only when B has committed the insert and A has committed as well, so that the timepoint is advanced past the commit of B.

    字面意思:即使事务 A 的快照是在事务 B 提交之前创建的,但事务 A 也只有在事务 A 和事务 B 都提交后,才能看到事务 B 新增的数据。

    时刻 事务 A 事务 B
    t0 start transaction; start transaction;
    t1 select * from t; #empty
    t2 insert into t values (1);
    t3 select * from t; #empty
    t4 commit;
    t5 select * from t; #empty
    t6 commit;
    t7 select * from t; # 1 rows found

    相关文章

      网友评论

          本文标题:MySQL之快照读

          本文链接:https://www.haomeiwen.com/subject/ovggactx.html