トランザクション分離レベル

じぶんなりにまとめてみた

トランザクション分離レベル

処理が「複数同時に並行して」実行されようとした場合，「待ち時間を減らすためどれだけデータの一貫性を犠牲にして良いか」を定めたもの
ANSI/ISO SQL標準で定められている

SERIALIZABLE

最も強い分離レベル
トランザクションを時間的重なりなく逐次実行した場合と同じ結果となる
ただし同じ結果とされる逐次実行の順はトランザクション処理のレベルでは保証されない

REPEATABLE READ

同じトランザクション中では同じデータは何度読み取りしても毎回同じ値を読むことができる
ただしファントム・リードが発生する可能性がある

READ COMMITTED

他のトランザクションによる更新については，常にコミット済みのデータのみを読み取る
MVCCはREAD COMMITTEDを実現する実装の一つ
ファントム・リードに加え，非再現リードが発生する可能性がある

READ UNCOMMITTED

他の処理によって行われている、書きかけのデータまで読み取る
ファントム・リード，非再現リードさらにダーティ・リードが発生する可能性がある

ファントム・リード

並行して動作する他のトランザクションが追加したり削除したデータが途中で見えてしまう現象

非再現リード

同じトランザクション中でも同じデータを読み込むたびに値が変わってしまう現象

ダーティ・リード

確定前の不完全なデータや，計算途中のデータを読み取ってしまう現象

トランザクション分離レベルと発生する現象の表

分離レベル	ファントム・リード	非再現リード	ダーティ・リード
SERIALIZABLE	発生しない	発生しない	発生しない
REPEATABLE READ	発生する	発生しない	発生しない
READ COMMITTED	発生する	発生する	発生しない
READ UNCOMMITTED	発生する	発生する	発生する

これらの分離レベルを実現するため暗黙的にロックが行われているはずだけど，ステートメントに応じてどのような暗黙的ロックが行われるのかはデータベースマネジメントシステムの実装による(と思う)

用語

MVCC

MultiVersion Concurrency Control(多版型同時実行制御)
データベース管理システムの可用性を向上させる制御技術のひとつ
複数同時に処理要求が行われた場合でも同時並行性を失わずに処理し，かつ情報の一貫性を保証する仕組み
READ COMMITTEDを実現する実装の一つ

疑問とか

SLELECT, UPDATE, DELETEはレコードが存在しているので挙動を想像しやすいが，トランザクション開始時点でレコードの存在しないINSERTは挙動が想像しにくいたとえば，分離レベルがSERIALIZABLEである複数トランザクションから同一キーをを持つレコードをINSERTしようとした時はどのような挙動になるだろう？

推測

以下の観点から考えてみた

後続トランザクションから
- 先行トランザクションでINSERTされた行が見えるか
  →分離レベルSERIALIZEDはファントム・リードが起きないのだから見えないはず
- 先行トランザクションでINSERTされた行と同じキーを持つ行をINSERTできるか
  →INSERTのリクエストは受け付けてもらえるが，先行トランザクションによって該当テーブル(もしくはレコード)がロックされているのでコミットされるまで待たされると予想
  →さらに後続トランザクションがコミットした際に先行トランザクションで作成したレコードとキーが重複するため制約違反となりトランザクションごとABORTされると予想

こたえあわせ

先行トランザクション開始

mysql> use isolation_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

後続トランザクション開始

mysql> use isolation_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

mysql>

先行トランザクションでレコードをINSERT

mysql> insert into id_tbl values (1, 'taro');
Query OK, 1 row affected (0.00 sec)

mysql> select * from id_tbl;
+----+------+
| id | name |
+----+------+
|  1 | taro |
+----+------+
1 row in set (0.00 sec)

後続トランザクションでSELECT

mysql> select * from id_tbl;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ファントム・リードが起きていないことの確認のつもりだったが，ブロックされてしまった

後続トランザクションで同じキーをもつレコードをINSERT

mysql> insert into id_tbl values (1, 'taro');

予想通り待たされる

先行トランザクションでCOMMITした時の後続トランザクションの様子

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

これも予想通り制約違反となった

ちょっと深追い

概ね予想通りだったが，後続トランザクションでSELECTがブロックされたのが気になったので調べてみた

後続トランザクションでSELECTがブロックされているときのトランザクションとロック状態

mysql> select * from information_schema.innodb_trx order by trx_id\G
*************************** 1. row ***************************
                    trx_id: 1817
                 trx_state: RUNNING
               trx_started: 2013-06-25 20:06:39
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 24
                 trx_query: select * from information_schema.innodb_trx order by trx_id
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: SERIALIZABLE
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 1818
                 trx_state: LOCK WAIT
               trx_started: 2013-06-25 20:07:11
     trx_requested_lock_id: 1818:6:3:4
          trx_wait_started: 2013-06-25 20:07:11
                trx_weight: 3
       trx_mysql_thread_id: 25
                 trx_query: select * from id_tbl
       trx_operation_state: fetching rows
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 3
     trx_lock_memory_bytes: 376
           trx_rows_locked: 3
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: SERIALIZABLE
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

mysql> select * from information_schema.innodb_locks order by lock_id\G
*************************** 1. row ***************************
    lock_id: 1817:6:3:4
lock_trx_id: 1817
  lock_mode: X
  lock_type: RECORD
 lock_table: `isolation_test`.`id_tbl`
 lock_index: PRIMARY
 lock_space: 6
  lock_page: 3
   lock_rec: 4
  lock_data: 3
*************************** 2. row ***************************
    lock_id: 1818:6:3:4
lock_trx_id: 1818
  lock_mode: S
  lock_type: RECORD
 lock_table: `isolation_test`.`id_tbl`
 lock_index: PRIMARY
 lock_space: 6
  lock_page: 3
   lock_rec: 4
  lock_data: 3
2 rows in set (0.00 sec)

先行トランザクションにより対象テーブルがロックされていてSELECTがブロックされているのかと思ったが，実際は先行トランザクションが排他ロックしたレコードを後続トランザクションが共有ロックしようとして競合しロック獲得待ちになっていた

noob

底辺オペレーターのメモ

トランザクション分離レベル