データベース

[hysryt]

ネクストキーの検証

テストデータ

CREATE TABLE client (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);
INSERT INTO client VALUES(90, 'tanaka');
INSERT INTO client VALUES(102, 'satou');

検証1

トランザクションA

BEGIN;
SELECT * FROM client WHERE id < 100 LOCK IN SHARE MODE;

トランザクションB

パターン1

BEGIN;
INSERT INTO client VALUES(101, 'murata');

ロックされる

パターン2

BEGIN;
UPDATE client SET name = 'baba' WHERE id = 102;

ロックされる

パターン3

BEGIN;
INSERT INTO client VALUES(103, 'kimura');

ロックされない

結果

( negative infinity - 90 ]
( 90 - 102 ]

検証2

トランザクションA

BEGIN;
SELECT * FROM client WHERE id > 100 LOCK IN SHARE MODE;

トランザクションB

パターン1

BEGIN;
INSERT INTO client VALUES(89, 'terada');

ロックされない

パターン2

BEGIN;
UPDATE client SET name = 'yoshida' WHERE id = 90;

ロックされない

パターン3

BEGIN;
INSERT INTO client VALUES(91, 'terada');

ロックされる

結果

( 90 - 102 ]
( 103 - positive infinity ]

検証3

トランザクションA

BEGIN;
SELECT * FROM client WHERE id = 100 LOCK IN SHARE MODE;

トランザクションB

パターン1

BEGIN;
UPDATE client SET name = 'shirou' WHERE id = 90;

ロックされない

パターン2

BEGIN;
INSERT INTO client VALUES(91, 'shirou');

ロックされる

パターン3

BEGIN;
INSERT INTO client VALUES(101, 'shirou');

ロックされる

パターン4

BEGIN;
UPDATE client SET name = 'shirou' WHERE id = 102;

ロックされない

まとめ

• インデックスの操作は常に小さい方から大きい方へ？
• 条件が等号(=)
  • データがない場合はギャップロックのみ
  • データがある場合はレコードロックのみ

[hysryt]

ネクストキーロックの検証2

テストデータ

CREATE TABLE client (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);
INSERT INTO client VALUES(90, 'tanaka');
INSERT INTO client VALUES(100, 'shirou');
INSERT INTO client VALUES(102, 'satou');

検証1

トランザクションA

BEGIN;
SELECT * FROM client WHERE id < 100 LOCK IN SHARE MODE;

トランザクションB

パターン1

BEGIN;
UPDATE client SET name = 'murata' WHERE id = 100;

ロックされる

パターン2

BEGIN;
INSERT INTO client VALUES(101, 'murata');

ロックされない

結果

( netative infinity - 90 ]
( 90 - 100 ]

検証2

トランザクションA

BEGIN;
SELECT * FROM client WHERE id <= 100 LOCK IN SHARE MODE;

トランザクションB

パターン1

BEGIN;
UPDATE client SET name = 'murata' WHERE id = 100;

ロックされる

パターン2

BEGIN;
INSERT INTO client VALUES(101, 'murata');

ロックされる

パターン3

BEGIN;
UPDATE client SET name = 'murata' WHERE id = 102;

ロックされる

パターン4

BEGIN;
INSERT INTO client VALUES(103, 'murata');

ロックされない

結果

( negative infinity - 90 ]
( 90 - 100 ]
( 100 - 102 ]

検証3

トランザクションA

BEGIN;
SELECT * FROM client WHERE id = 100 LOCK IN SHARE MODE;

トランザクションB

パターン1

BEGIN;
INSERT INTO client VALUES(99, 'murata');

ロックされない

パターン2

BEGIN;
UPDATE client SET name = 'murata' WHERE id = 100;

ロックされる

パターン3

BEGIN;
INSERT INTO client VALUES(101, 'murata');

ロックされない

[hysryt]

ネクストキーロックのまとめ

• 条件に該当するレコードが存在する場合、そのレコードの両脇のギャップと、そのレコードと、そのレコードの次に大きいレコードがロックされる。
  • テーブルに 10, 20, 30 があり、条件が>= 20 の場合、条件に該当するのは 20, 30, 最大値。 そのための以下の範囲がロック対象となる。

    ( 10 - 20 ]  // 20 の左隣と 20
    ( 20 - 30 ]  // 30 の左隣と 30
    ( 30 - positive infinity ]  // 30 の左隣と最大値

  • テーブルに 10, 20, 30 があり、条件が<= 20 の場合、条件に該当するのは 最小値, 10, 20。 そのための以下の範囲がロック対象となる。

    ( negative infinity - 10 ]  // 最小値と 10 の左隣と 10
    ( 10 - 20 ]  // 20 の左隣と 20
    ( 20 - 30 ]  // 20 の右隣と 20 の次に大きいレコード

  • ただし条件が等号(=)の場合はそのレコードのみがロック対象となる。
• 条件に該当するレコードが存在しない場合、そのレコードが存在するべきギャップがロックされる。

[hysryt]

仮説

• INSERT はギャップロックでないと抑制できない
  • そもそも INSERT する前はレコードが存在しないのでレコードロックできない

[hysryt]

並列トランザクションにより起こりうる現象

分離レベルやロック方法を変更することで防ぐことができる。 また、パフォーマンスとの兼ね合いからこれらの現象を許容することもある。

ダーティーリード

他のトランザクションでUPDATE、INSERT、DELETEした結果をコミット前に取得できてしまう現象。 分離レベルをREAD COMMITTED以上にすれば防ぐことができる。

ノリピータブルリード（ファジーリード）

同じSELECTを実行するとき、一度目と二度目の間に他のトランザクションがUPDATEで更新したデータを読み込んでしまい、前後でSELECTの結果が異なってしまう現象。 分離レベルをREPEATABLE READ以上にすれば防ぐことができる。

ファントムリード

同じSELECTを実行するとき、一度目と二度目の間に他のトランザクションがINSERTあるいはDELETEで追加（削除）した結果を読み込んでしまい、前後でSELECTの結果が異なってしまう現象。 分離レベルをSERIALIZEにすれば防ぐことができる。 また、MySQLであればREPEATABLE READ以上でもファントムリードを防ぐことができる。

ロストアップデート

他のトランザクションがUPDATEしたデータをさらにUPDATEしてしまい、他のトランザクションの更新が無かったことになってしまう現象。 UPDATE前の読み込み時に排他ロックをかければ防ぐことができる。

[hysryt]

分離レベル

各トランザクションをどの程度分離させるかを表す指標。 トランザクションが分離されているほどデータの整合性が保たれやすいが、その分パフォーマンスに影響が出る。

ANSI/ISO SQL標準では以下の4つが定義されている。 SERIALIZABLE がもっとも分離されており、READ UNCOMMITTED がもっとも分離されていない（トランザクション同士が干渉し合う可能性が高い）。

READ UNCOMMITED

他トランザクションの確定前（コミット前）のデータを読み取れてしまう（ダーティーリード）。 つまり読み取ったデータはロールバックされる可能性があるということ。 そのデータをもとにした更新はデータの不整合が生じる危険性を孕んでいる。

READ COMMITED

確定した（コミットした）データのみ読み取る。 READ UNCOMMITED より安全だが、トランザクション中にデータが別トランザクションによって更新される可能性がある。 言い換えると、扱っている最中のデータが他者によって書き換えられてしまう可能性がある（ノンリピータブルリード/ファジーリード）。

REPEATABLE READ

確定した（コミットした）データのみ読み取り、かつ、一度読み込んだデータはその後読み込む際も常に最初に読み込んだ時点のデータを読み取る。 トランザクション中に他トランザクションがデータを更新したとしてもそのデータは無視される、とも言い換えられる。 ただしこれは更新に限った話であり、他トランザクションがデータを追加、あるいは削除した場合はそのデータを読み取ってしまう（ファントムリード）。

また、他のトランザクションの更新を無視するため、気づかずその更新を上書きしてしまう可能性がある（ロストアップデート）が、これは読み取り時にあらかじめロックをかける（ロッキングリード）ことで解決できる。

SERIALIZABLE

各トランザクションを直列に実行した場合と同じ結果になることを保証する。 そのためロストアップデートは発生しない。

PostgreSQLではロストアップデートを発生させようとするとエラーが発生する。

could not serialize access due to concurrent update

MySQLではロストアップデートを発生させようとしても自動的にSELECTが共有ロックをかけるため、発生しない。

先述の通りMySQLのSerializableではSELECTが常に共有ロックをかける。 https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html#isolevel_serializable

InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. （InnoDB は autocommit が無効の場合は暗黙的に通常の SELECT を SELECT ... FOR SHARE に変換する。）

そのためデッドロックが発生する可能性がある。 デッドロックが発生した場合、後から排他ロックをかけたトランザクションは即座にロールバックされる。 その後、先に排他ロックをかけたトランザクションはそのままクエリが実行される。