Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


14.2.4 一貫性非ロック読み取り

一貫性読み取りとは、InnoDB がマルチバージョンを使用して、ある時点でのデータベースのスナップショットをクエリーに提供することを意味します。クエリーには、その時点よりも前にコミットされたトランザクションによる変更のみが表示され、その時点よりもあとのトランザクションまたはコミットされていないトランザクションによる変更は表示されません。このルールの例外として、同じトランザクション内の以前のステートメントによる変更はクエリーに表示されます。この例外によって、次のような異常が発生します。テーブル内の一部の行を更新すると、更新された行の最新バージョンが SELECT に表示されますが、いずれかの行の旧バージョンも表示される可能性があります。その他のセッションで同じテーブルが同時に更新される場合、その異常は、データベースに存在しない状態でテーブルが表示される可能性があることを意味します。

トランザクション分離レベルREPEATABLE READ (デフォルトのレベル) である場合は、同じトランザクション内のすべての一貫性読み取りで、そのトランザクション内の最初のこのような読み取りで確立されたスナップショットが読み取られます。現在のトランザクションをコミットしたあとに、新しいクエリーを発行すると、クエリーの新しいスナップショットを取得できます。

分離レベルが READ COMMITTED の場合は、トランザクション内の各一貫性読み取りで、独自の新しいスナップショットが設定され、読み取られます。

一貫性読み取りは、InnoDBREAD COMMITTED および REPEATABLE READ 分離レベルで SELECT ステートメントを処理する際のデフォルトモードです。一貫性読み取りではアクセスされるテーブル上にロックが設定されないため、その他のセッションも、そのテーブル上で一貫性読み取りが実行されるときと同時に、それらのテーブルを自由に変更できます。

デフォルトの REPEATABLE READ 分離レベルで実行していると仮定します。一貫性読み取り (つまり、通常の SELECT ステートメント) を発行すると、InnoDB は、クエリーがデータベースを参照するときの基準となるタイムポイントをトランザクションに付与します。タイムポイントが割り当てられたあとに、別のトランザクションが行を削除してコミットした場合は、その行が削除済みとして表示されません。挿入および更新も同様に処理されます。

注記

データベースの状態のスナップショットは、トランザクション内の SELECT ステートメントに適用されますが、DML ステートメントには必ずしも適用されるとは限りません。一部の行を挿入または変更してから、そのトランザクションをコミットする場合は、そのセッションでクエリーが実行される可能性がない場合でも、別の並列実行 REPEATABLE READ トランザクションから発行された DELETE または UPDATE ステートメントによって、コミットされたばかりの行が影響を受ける可能性があります。トランザクションによって別のトランザクションでコミットされた行が更新または削除されると、これらの変更を現在のトランザクションに表示できるようになります。たとえば、次のような状況が発生する可能性があります。

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.

トランザクションをコミットしてから、別の SELECT または START TRANSACTION WITH CONSISTENT SNAPSHOT を実行すると、タイムポイントを進めることができます。

これは、マルチバージョン並列処理制御と呼ばれます。

次の例では、セッション B が挿入をコミットし、セッション A も同様にコミットした場合にのみ、B によって挿入された行が A に表示されます。これにより、タイムポイントが 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

データベースの最新状態を確認する場合は、READ COMMITTED 分離レベルとロック読み取りのいずれかを使用します。

SELECT * FROM t LOCK IN SHARE MODE;

分離レベルが READ COMMITTED の場合は、トランザクション内の各一貫性読み取りで、独自の新しいスナップショットが設定され、読み取られます。LOCK IN SHARE MODE の場合は、代わりにロック読み取りが発生します。SELECT は、最新の行を含むトランザクションが終了するまでブロックされます (セクション14.2.5「ロック読み取り (SELECT ... FOR UPDATE および SELECT ... LOCK IN SHARE MODE)」を参照)。

特定の DDL ステートメントでは、一貫性読み取りが機能しません。

  • DROP TABLE では、MySQL が削除されたテーブルを使用できず、そのテーブルは InnoDB によって破棄されるため、一貫性読み取りが機能しません。

  • ALTER TABLE では、そのステートメントで元のテーブルの一時コピーが作成され、元のテーブルは一時コピーが構築されるときに削除されるため、一貫性読み取りが機能しません。トランザクション内で一貫性読み取りを再発行しても、新しいテーブル内の行はトランザクションのスナップショット取得されたときには存在していなかったため、表示できません。MySQL 5.6.6 の時点では、この場合に、トランザクションからTable definition has changed, please retry transactionという ER_TABLE_DEF_CHANGED エラーが返されます。

FOR UPDATE または LOCK IN SHARE MODE を指定しない INSERT INTO ... SELECTUPDATE ... (SELECT)CREATE TABLE ... SELECT などの句での選択では、読み取りのタイプが異なります。

  • デフォルトでは、InnoDB はより強固なロックを使用し、SELECT 部分は READ COMMITTED と同様に機能します。この場合、同じトランザクション内でも、各一貫性読み取りで独自の新しいスナップショットが設定され、読み取られます。

  • このような場合に一貫性読み取りを使用するには、innodb_locks_unsafe_for_binlog オプションを有効にし、トランザクションの分離レベルを READ UNCOMMITTEDREAD COMMITTED、または REPEATABLE READ (つまり、SERIALIZABLE 以外のすべて) に設定します。この場合、選択したテーブルから読み取られた行には、ロックが設定されません。


User Comments
  Posted by A Ramos on October 24, 2009
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.
  Posted by Asaf M on November 18, 2009
Dude, someone answered this on the forum:
http://forums.mysql.com/read.php?22,281645,287007#msg-287007
  Posted by Pradeep Gupta on October 10, 2012
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

Sign Up Login You must be logged in to post a comment.