トランザクション分離レベルの勉強記録(5) 書き込みの検証手順と詳細な予測

• 検証全体のまとめ

「予測①　書き込みは、どの分離レベルでも同様に排他ロックを取得する」の検証手順と、詳細な予測。

検証手順

各トランザクション分離レベルで、トランザクション内の書き込み操作が排他ロックを取得しているかを確認する。

1. ターミナルを開き、SET SESSION TRANSACTION ISOLATION LEVELを設定する
2. トランザクションTx1を開始する
3. Tx1で書き込みSQLを実行する
4. innodb_lock_monitorを確認する
   • Tx1が排他ロックを取得しているか？
   • その範囲は？
5. Tx1をROLLBACKする
6. 1～5の繰り返し

準備

CREATE DATABASE TIL;
USE TIL;
CREATE TABLE test (
  id INTEGER NOT NULL DEFAULT 0,
  a INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);
INSERT INTO test (id, a) VALUES (2, 20);
INSERT INTO test (id, a) VALUES (3, 30);
INSERT INTO test (id, a) VALUES (6, 30);
INSERT INTO test (id, a) VALUES (7, 10);

上記SQLでテーブルtest.TILに以下の4行が準備される。

id	a
2	20
3	30
6	30
7	10

innodb_lock_monitorの有効化・無効化

/* 有効化 */
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
/* 無効化 */
DROP TABLE innodb_lock_monitor;

※innodb_lock_monitorの出力はかなりの量になるので、途中でMySQLをサーバーを停止してmysql_error.logファイルをリネームする等、適宜対応する。

トランザクション分離レベルの変更（セッション単位）

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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

SHOW VARIABLES LIKE 'tx_isolation';

検証に使用するSQLと予測

INSERT

INSERT_1

INSERT INTO test (id, a) VALUES (1, 10);

【予測①-INSERT】
どの分離レベルにおいても挿入した行のレコードロック(X)を取得する。

UPDATE

UPDATE_1

UPDATE test SET a = 200 WHERE id = 2;

UPDATE_2

UPDATE test SET a = 200 WHERE id = 2 OR id = 6;

UPDATE_3

UPDATE test SET a = 200 WHERE id = 2 OR id = 5;

UPDATE_4

UPDATE test SET a = 200 WHERE id = 2 OR id = 9;

• UPDATE_1やUPDATE_2だけを見れば、実際に更新する行のレコードロック(X)で十分である
• UPDATE_3やUPDATE_4のような場合、存在しないレコードはロックできない
  • 他のトランザクションによって、条件に該当するレコードが挿入される可能性がある（そしてその行は更新されない）
  • 少なくともSERIALIZABLEはこれを許可しない
  • id >= （条件の値）となる最初のレコードのネクストキーロック(X)を取得すれば挿入を防げる

【予測①-UPDATE_1-4】
SERIALIZABLEは実際に更新するレコードのネクストキーロック(X)と、id >= （条件の値）となる最初のレコードのネクストキーロック(X)を取得する。他の分離レベルもおそらく同じ。
弊害としてWHERE句の条件に該当しないのに挿入できなくなるレコードがある。

• UPDATE_3のロックによってid = 4のレコードも挿入できなくなる
• UPDATE_4のロックによってid = 8およびid > 9のレコードも挿入できなくなる

UPDATE_5

UPDATE test SET a = 200 WHERE id <= 2;

UPDATE_6

UPDATE test SET a = 200 WHERE id <= 4;

UPDATE_7

UPDATE test SET a = 200 WHERE id <= 9;

• UPDATE_5だけを見れば、実際に更新する行のネクストキーロック(X)で十分である
• UPDATE_6やUPDATE_7のような場合、存在しないレコードはロックできない
  • 他のトランザクションによって、条件に該当するレコードが挿入される可能性がある（そしてその行は更新されない）
  • 少なくともSERIALIZABLEはこれを許可しない
  • id >= （条件の値）となる最初のレコードのネクストキーロック(X)を取得すれば挿入を防げる

【予測①-UPDATE_5-7】
SERIALIZABLEは実際に更新するレコードのネクストキーロック(X)と、id >= （条件の値）となる最初のレコードのネクストキーロック(X)を取得する。他の分離レベルもおそらく同じ。
弊害としてWHERE句の条件に該当しないのに挿入できなくなるレコードがある。

• UPDATE_6のロックによってid = 5のレコードも挿入できなくなる
• UPDATE_7のロックによってid > 9のレコードも挿入できなくなる

UPDATE_8

UPDATE test SET a = 200 WHERE id >= 2;

UPDATE_9

UPDATE test SET a = 200 WHERE id >= 5;

UPDATE_10

UPDATE test SET a = 200 WHERE id >= 9;

• 実際に更新する行のネクストキーロック(X)だけだと
  • 他のトランザクションによって、条件に該当するレコードが挿入される可能性がある（そしてその行は更新されない）
  • 少なくともSERIALIZABLEはこれを許可しない
  • supremumのネクストキーロック(X)を取得すれば挿入を防げる

【予測①-UPDATE_8-10】
SERIALIZABLEは実際に更新するレコードのネクストキーロック(X)と、supremumのネクストキーロック(X)を取得する。他の分離レベルもおそらく同じ。
弊害としてWHERE句の条件に該当しないのに挿入できなくなるレコードがある。

• UPDATE_9のロックによってid = 4のレコードも挿入できなくなる
• UPDATE_10のロックによってid = 8のレコードも挿入できなくなる

UPDATE_11

UPDATE test SET a = 300 WHERE a = 30;

UPDATE_12

UPDATE test SET a = 300 WHERE a = 100;

• 検索条件であるaにインデックスがないので、全レコードをスキャンすることになる
• レコードロック(X)だけだと
  • 他のトランザクションによって、条件に該当するレコードが挿入される可能性がある（そしてその行は更新されない）
  • 少なくともSERIALIZABLEはこれを許可しない
  • ネクストキーロック(X)を取得すれば挿入を防げる
• レコードすべてをロックする？もしくは、実際に更新するレコードだけをロックする？
  • 実際に更新する行のロックだけだと他のトランザクションによって、条件に該当しなかったレコードが更新され、条件に該当するようになる可能性がある（そしてその行は更新されない）
  • 少なくともSERIALIZABLEはこれを許可しない
  • スキャンしたレコードすべてのロックを取得すれば防げる

【予測①-UPDATE_11-12】
SERIALIZABLEは全行のネクストキーロック(X)とsupremumのネクストキーロック(X)を取得する。他の分離レベルもおそらく同じ。
弊害としてWHERE句の条件に該当しなくも、すべてのレコードが挿入および更新できなくなる。

DELETE

DELETE_1

DELETE FROM test WHERE id = 2;

DELETE_2

DELETE FROM test WHERE id = 2 OR id = 6;

DELETE_3

DELETE FROM test WHERE id = 2 OR id = 5;

DELETE_4

DELETE FROM test WHERE id = 2 OR id = 9;

DELETE_5

DELETE FROM test WHERE id <= 2;

DELETE_6

DELETE FROM test WHERE id <= 4;

DELETE_7

DELETE FROM test WHERE id <= 9;

DELETE_8

DELETE FROM test WHERE id >= 2;

DELETE_9

DELETE FROM test WHERE id >= 5;

DELETE_10

DELETE FROM test WHERE id >= 9;

DELETE_11

DELETE FROM test WHERE a = 30;

DELETE_12

DELETE FROM test WHERE a = 100;

【予測①-DELETE】
UPDATEの対応する番号と同じ。

結果へのリンク

• INSERTの結果