Consistent Nonlocking Reads

谁借莪１个温暖的怀抱￠ 2022-08-10 15:58 20阅读 0赞

本文转载自：http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html

### 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. 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, a [`SELECT`][SELECT] sees 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 transaction [isolation level][] is [`REPEATABLE READ`][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.

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

Consistent read is the default mode in which `InnoDB` processes [`SELECT`][SELECT] statements in [`READ COMMITTED`][READ COMMITTED] and[`REPEATABLE READ`][REPEATABLE READ] isolation levels. 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.

Suppose that you are running in the default [`REPEATABLE READ`][REPEATABLE READ] isolation level. When you issue a consistent read (that is, an ordinary [`SELECT`][SELECT] statement), `InnoDB` gives your transaction a timepoint according to which your query sees the database. 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.

Note

The snapshot of the database state applies to [`SELECT`][SELECT] statements within a transaction, not necessarily to [DML][]statements. If you insert or modify some rows and then commit that transaction, a [`DELETE`][DELETE] or [`UPDATE`][UPDATE] statement issued from another concurrent `REPEATABLE READ` transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz'; -- Returns 0: no rows match.
    DELETE FROM t1 WHERE c1 = 'xyz'; -- Deletes several rows recently committed by other transaction.
    
    SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc'; -- Returns 0: no rows match.
    UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc'; -- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
    SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba'; -- Returns 10: this txn can now see the rows it just updated.

You can advance your timepoint by committing your transaction and then doing another [`SELECT`][SELECT] or [`START TRANSACTION WITH CONSISTENT SNAPSHOT`][START TRANSACTION WITH CONSISTENT SNAPSHOT].

This is called **multi-versioned concurrency control**.

In the following example, 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.

Session A              Session B
    
               SET autocommit=0;      SET autocommit=0;
    time
    |          SELECT * FROM t;
    |          empty set
    |                                 INSERT INTO t VALUES (1, 2);
    |
    v          SELECT * FROM t;
               empty set
                                      COMMIT;
    
               SELECT * FROM t;
               empty set
    
               COMMIT;
    
               SELECT * FROM t;
               ---------------------
               |    1    |    2    |
               ---------------------
               1 row in set

If you want to see the “freshest” state of the database, use either the [`READ COMMITTED`][READ COMMITTED] isolation level or a [locking read][]:

SELECT * FROM t LOCK IN SHARE MODE;

With [`READ COMMITTED`][READ COMMITTED] isolation level, each consistent read within a transaction sets and reads its own fresh snapshot. With `LOCK IN SHARE MODE`, a locking read occurs instead: A `SELECT` blocks until the transaction containing the freshest rows ends (see [Section 14.5.5, “Locking Reads (`SELECT ... FOR UPDATE` and `SELECT ... LOCK IN SHARE MODE`)”][Section_14.5.5_ _Locking Reads _SELECT ... FOR UPDATE_and_SELECT ... LOCK IN SHARE MODE]).

Consistent read does not work over certain DDL statements:

*  Consistent read does not work over [`DROP TABLE`][DROP TABLE], because MySQL cannot use a table that has been dropped and`InnoDB` destroys the table.
 *  Consistent read does not work over [`ALTER TABLE`][ALTER TABLE], because that statement makes a temporary copy of the original table and deletes the original table when the temporary copy is built. When you reissue a consistent read within a transaction, rows in the new table are not visible because those rows did not exist when the transaction's snapshot was taken.

The type of read varies for selects in clauses like [`INSERT INTO ... SELECT`][INSERT INTO ... SELECT], [`UPDATE ... (SELECT)`][UPDATE], and[`CREATE TABLE ... SELECT`][CREATE TABLE ... SELECT] that do not specify `FOR UPDATE` or `LOCK IN SHARE MODE`:

*  By default, `InnoDB` uses stronger locks and the [`SELECT`][SELECT] part acts like [`READ COMMITTED`][READ COMMITTED], where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
 *  To use a consistent read in such cases, enable the [`innodb_locks_unsafe_for_binlog`][innodb_locks_unsafe_for_binlog] option and set the isolation level of the transaction to [`READ UNCOMMITTED`][READ UNCOMMITTED], [`READ COMMITTED`][READ COMMITTED], or [`REPEATABLE READ`][REPEATABLE READ] (that is, anything other than [`SERIALIZABLE`][SERIALIZABLE]). In this case, no locks are set on rows read from the selected table.

[Previous][] /  [Next][Section_14.5.5_ _Locking Reads _SELECT ... FOR UPDATE_and_SELECT ... LOCK IN SHARE MODE] /  [Up][] /  [Table of Contents][]

# User Comments #

<table style="margin:0px 0px 10px; padding:4px 0px 8px; border:0px; outline:0px; font-size:14px; vertical-align:baseline; background-color:transparent; border-spacing:0px; border-collapse:collapse; font-weight:bold; width:1040px"> 
 <tbody style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent"> 
  <tr style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:top; background-color:transparent"> 
   <td style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> Posted by A Ramos on October 24 2009 6:57pm</td> 
   <td align="right" style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=10432&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-consistent-read.html&amp;action=delete" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=10432&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-consistent-read.html" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Edit</a>]</td> 
  </tr> 
 </tbody> 
</table>

Programmer beware, "Consistent Nonlocking Reads" are not automatic or even very easy to accomplish reliably. In my own experience I've found blocking reads (and the resulting lock timeouts) to be quite common with innodb.  
  
The following very simple test locks out a reader in innodb. Tested in 5.0.67. I reported it as a bug and was told it's normal, expected behavior:  
  
Session \#1  
\----------  
mysql> create table t1(a int) engine=innodb;  
Query OK, 0 rows affected (0.05 sec)  
  
mysql> start transaction;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> insert into t1 values (3);  
Query OK, 1 row affected (0.02 sec)  
  
Session \#2 (fire up a separate window without closing the above)  
\-----------  
mysql> select \* from t1;  
^CQuery aborted by Ctrl+C  
ERROR 1317 (70100): Query execution was interrupted  
(I had to kill the query after several seconds)  
  
Setting "set session transaction isolation level read committed;" did not help.  
  
Setting "set session transaction isolation level read uncommitted;" did not help.  
  
Using lock tables on either side did not help.  
  
Setting innodb\_locks\_unsafe\_for\_binlog=1 in my.cnf fixes the problem, but I guess the downside is that now I will not be able to use replication in this database.  
  
\*HOWEVER\*  
If you change the simple select statement to a CREATE TEMPORARY TABLE ... AS SELECT ..., and the target involves an index range, then even innodb\_locks\_unsafe\_for\_binlog does not help.

<table style="margin:0px 0px 10px; padding:4px 0px 8px; border:0px; outline:0px; font-size:14px; vertical-align:baseline; background-color:transparent; border-spacing:0px; border-collapse:collapse; font-weight:bold; width:1040px"> 
 <tbody style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent"> 
  <tr style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:top; background-color:transparent"> 
   <td style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> Posted by Asaf M on November 18 2009 3:04pm</td> 
   <td align="right" style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=10483&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-consistent-read.html&amp;action=delete" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=10483&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-consistent-read.html" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Edit</a>]</td> 
  </tr> 
 </tbody> 
</table>

Dude, someone answered this on the forum:  
[http://forums.mysql.com/read.php?22,281645,287007\#msg-287007][http_forums.mysql.com_read.php_22_281645_287007_msg-287007]

<table style="margin:0px 0px 10px; padding:4px 0px 8px; border:0px; outline:0px; font-size:14px; vertical-align:baseline; background-color:transparent; border-spacing:0px; border-collapse:collapse; font-weight:bold; width:1040px"> 
 <tbody style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent"> 
  <tr style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:top; background-color:transparent"> 
   <td style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> Posted by Pradeep Gupta on October 10 2012 2:41pm</td> 
   <td align="right" style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12443&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-consistent-read.html&amp;action=delete" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12443&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-consistent-read.html" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Edit</a>]</td> 
  </tr> 
 </tbody> 
</table>

When we are selecting from any table from 1 session then we cant alter that table from another session.  
  
Session A :  
  
mysql> select \* from person\_test where id=1;

+----+-------------+
    | id | name        |
    +----+-------------+
    |  1 | Antonio Paz |
    +----+-------------+

1 row in set (0.00 sec)  
  
Session B :  
  
mysql> alter table person\_test add (city char(20));  
Ctrl-C -- sending "KILL QUERY 4" to server ...  
Ctrl-C -- query aborted.  
ERROR 1317 (70100): Query execution was interrupted

注：近期参加MySQL运维学习，老师推荐该文章作为学习和技术提高的扩展阅读，先记录到自己的博客中，随后慢慢消化、学习、提高。本文章与“备份恢复”主题相关

[consistent read]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_consistent_read
[SELECT]: http://dev.mysql.com/doc/refman/5.5/en/select.html
[isolation level]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_isolation_level
[REPEATABLE READ]: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_repeatable-read
[READ COMMITTED]: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed
[DML]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_dml
[DELETE]: http://dev.mysql.com/doc/refman/5.5/en/delete.html
[UPDATE]: http://dev.mysql.com/doc/refman/5.5/en/update.html
[START TRANSACTION WITH CONSISTENT SNAPSHOT]: http://dev.mysql.com/doc/refman/5.5/en/commit.html
[locking read]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_locking_read
[Section_14.5.5_ _Locking Reads _SELECT ... FOR UPDATE_and_SELECT ... LOCK IN SHARE MODE]: http://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html
[DROP TABLE]: http://dev.mysql.com/doc/refman/5.5/en/drop-table.html
[ALTER TABLE]: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html
[INSERT INTO ... SELECT]: http://dev.mysql.com/doc/refman/5.5/en/insert.html
[CREATE TABLE ... SELECT]: http://dev.mysql.com/doc/refman/5.5/en/create-table.html
[innodb_locks_unsafe_for_binlog]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog
[READ UNCOMMITTED]: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-uncommitted
[SERIALIZABLE]: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_serializable
[Previous]: http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html
[Up]: http://dev.mysql.com/doc/refman/5.5/en/innodb-concepts.html
[Table of Contents]: http://dev.mysql.com/doc/refman/5.5/en/index.html
[http_forums.mysql.com_read.php_22_281645_287007_msg-287007]: http://forums.mysql.com/read.php?22,281645,287007#msg-287007