ogp

Techouseの「エンジニア基礎勉強会」とは

Techouse では「基礎勉強会」と称して2週間に1回、わたしが OS・ネットワーク・データベース・ハードウェア・セキュリティ・システムアーキテクチャなどをお話する勉強会を開催しています。

今回お試し的に、勉強会の内容を公開してみます。

データベース(1) ACID
データベース(2) 分離性

今回は前回「データベース<ACID編>」の続きです。
ご笑覧くださいますと幸いです。

データベース(2) トランザクション <中編: 分離性>

前回で扱った「銀行口座」の例をそのまま使っていきます。

BEGIN;
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
  id         serial  PRIMARY KEY,
  name       varchar NOT NULL,
  balance    decimal NOT NULL,
  CONSTRAINT balance_not_minus CHECK (balance >= 0)
);
INSERT INTO accounts(name, balance) VALUES ('A', 0), ('B', 0);
SELECT * from accounts;

COMMIT;

行ロック色々

まずは復習です。

前回でやった、
「SELECT FOR UPDATE で行ロックを取得する」例として、
「AさんからBさんに500円振り込む」のと同時に
「BさんからAさんに1200円振り込む」ということをやってみます。

初期状態として、 Aさんの口座には1000円、Bさんの口座には2000円、入っているものとします。

BEGIN;
UPDATE accounts SET balance = 1000 WHERE name = 'A';
UPDATE accounts SET balance = 2000 WHERE name = 'B';
COMMIT;

2枚のターミナルを開いて psql のプロンプトを立ち上げて、プログラム1・2の気持ちになって、実行してみてください。(手順は前回のものを参照してください)

▼プログラム1 (AからBへ500円振り込む):

BEGIN;
  -- Aの残高を調べる
  SELECT balance FROM accounts WHERE name = 'A' FOR UPDATE;
  -- Bの残高を調べる
  SELECT balance FROM accounts WHERE name = 'B' FOR UPDATE;
  -- Aの残高から500円減らす
  UPDATE accounts SET balance = XXXX WHERE name = 'A';
  -- Bの残高を500円増やす
  UPDATE accounts SET balance = XXXX WHERE name = 'B';
COMMIT;

▼プログラム2 (BからAへ1200円振り込む):

BEGIN;
  -- Aの残高を調べる
  SELECT balance FROM accounts WHERE name = 'A' FOR UPDATE;
  -- Bの残高を調べる
  SELECT balance FROM accounts WHERE name = 'B' FOR UPDATE;
  -- Bの残高を1200円減らす
  UPDATE accounts SET balance = XXXX WHERE name = 'B';
  -- Aの残高から1200円増やす
  UPDATE accounts SET balance = XXXX WHERE name = 'A';
COMMIT;

実行結果を確認してみましょう。

SELECT * FROM accounts;

うまくいきましたかね？

排他ロックと共有ロック

SELECT FOR UPDATE のように
「この行はこれから更新するから、他の更新・参照トランザクションには待ってもらってよ」
という方式のことを 「排他ロック」 といいます。

似たようなもので、
「この行は今から参照するから、他のトランザクションからの更新は待ってもらってよ」
というのもあります。

これを 「共有ロック」 といいます。

共有ロックは、参照中の更新を防止するときに使います。
試しに使ってみましょう。

▼プログラム1 (更新をする)

BEGIN;
SELECT * FROM accounts FOR SHARE;
UPDATE accounts SET balance = 1000 WHERE name = 'A'; -- ブロックされる
ROLLBACK;

▼プログラム2 (参照するだけ)

BEGIN;
SELECT * FROM accounts FOR SHARE;
ROLLBACK;

共有ロックを取得している間、 UPDATE 文の実行が待たされるということがわかりますね。

排他ロック・共有ロックの概念は RDBMS に限らず、様々な DBMS やファイルシステム、メモリ上のロックでも共通している一般的な概念です。ちゃんと理解しておきましょう。

UPDATE 文実行時の排他ロック

さきほどの例では SELECT FOR UPDATE で明示的に排他ロックを取得しましたが、実は UPDATE/INSERT/DELETE 文を実行するだけで暗黙的に排他ロックが取得されます。

▼プログラム1

BEGIN;
UPDATE accounts SET balance = 1000 WHERE name = 'A';
ROLLBACK;

▼プログラム2

BEGIN;
SELECT * FROM accounts FOR UPDATE; -- 待たされる
ROLLBACK;

長時間のロック

ロックの取得待ちが長時間つづくと、どうなるでしょうか？

CDN やロードバランサにはタイムアウトが設定されています。
一定時間以内に HTTP レスポンスを返すことができないと、HTTP 504 Gateway Timeout エラーがクライアントに対して返されます。
ロック待ちが長くなると、504 エラーの原因となってしまいますね。

対策として PostgreSQL の場合にはパラメータでロック取得待ち時間を設定できるようになっており、
一定以上ロック取得に時間がかかった場合にはトランザクションを中止して
強制的にロールバックするようになっています。

SET lock_timeout = 10000; -- 10秒ロックが取得できなかったらロールバック

Amazon Aurora PostgreSQL のデフォルトは 0 (タイムアウトしない) です。
このタイムアウトに引っかかった場合、 Rails では、例外 ActiveRecord::StatementInvalid が上がります。
これを rescue してリトライすれば成功するやもしれません。

MySQL でも同様のパラメータがあります。

SET innodb_lock_wait_timeout = 10;

単位は秒で、Amazon Aurora MySQL のデフォルトは50秒です。
超過すると Lock wait timeout exceeded; try restarting transaction エラーが発生します。これも Rails では、例外 ActiveRecord::StatementInvalid が上がります。

これを使って「ただいま別の処理が進行中のため、リクエストを受付できませんでした。お手数をおかけしますが時間をおいて最後実行してください」という表示をするという手がありますね。(映画館の座席予約システムとかはそうなっているのではないかなと思います。)

NOWAIT オプション

さきほどの例では、ロック取得待ちの間待たされました。
これでは困る、すぐに失敗してほしい、というケースの場合には NOWAIT オプションを利用します。

次のSQL文を1行ずつ交互に実行していきましょう。

▼プログラム1:

BEGIN;
UPDATE accounts SET balance = 1000 WHERE name = 'A';
ROLLBACK;

▼プログラム2:

BEGIN;
SELECT * FROM accounts FOR UPDATE NOWAIT;
ROLLBACK;

SKIP LOCKED オプション

「行ロックを取得できない行をすっ飛ばして SELECT する」 というオプションもあります。

▼プログラム1:

BEGIN;
UPDATE accounts SET balance = 1000 WHERE name = 'A';
ROLLBACK;

▼プログラム2

BEGIN;
SELECT * FROM accounts FOR UPDATE SKIP LOCKED;
ROLLBACK;

「こんなの何に使うんだ」と思われる方、正解です。
普通は、めったなことがない限り、使いません。
サポートされ始めたのもつい最近のことです。
(PostgreSQL 9.5 (2016)、MySQL 8 (2018) からサポート)

めったなことが起こったりするのが現場というものなので、覚えておいて損はありません。
「めったなこと」を目の当たりにしたときにだけ、使うことを考えてください。

Rails で lock 時のオプション ( NOWAIT・SKIP LOCKED ) を指定するには ActiveRecord::Base.execute を使って生 SQL で実行する必要があります。

Rails 7 に更新すると lock! メソッドのオプションが利用できます。

ActiveRecord::Base.transaction do
  account = Account.find_by(name: 'A').lock!("FOR UPDATE NOWAIT")
  # ...
end

悲観的ロック

ここまで説明したアプローチを総称して「悲観的ロック」といいます。
ペシミスティックロック = pessimistic lock ともいいます。
「自分が先に更新できるような状況を確保する」というアプローチです。

悲観的ロックを取得している間、他のトランザクションの進行が待たされます。

楽観的ロック

「悲観的ロック」のその逆のパターンとして「楽観的ロック」というアプローチがあります。
オプティミスティックロック = optimistic lock ともいいます。

「どうせ同時に更新されることはめったに無いんだから、同時更新されるまで何もしないでおこう」という方式です。

PostgreSQL の場合、SQL文の実行結果として、
更新対象が1件の場合 UPDATE 1 、0件の場合 UPDATE 0 などというふうに更新行数が出力されます。

これを使った、楽観的ロックの実装方法を解説していきましょう。

楽観的ロックに対応するため、少しスキーマを変更します。
version というカラムを用意して、
更新するごとに version カラムを 1 ずつインクリメントしていくことにします。

BEGIN;

DROP TABLE IF EXISTS accounts2;

CREATE TABLE accounts2 (
  id         serial  PRIMARY KEY,
  name       varchar NOT NULL,
  balance    decimal NOT NULL,
  version    integer NOT NULL DEFAULT 0,
  CONSTRAINT balance_not_minus CHECK (balance >= 0)
);

INSERT INTO accounts2(name, balance) VALUES ('A', 0), ('B', 0);

SELECT * from accounts2;

COMMIT;

ふたたび、同じく、初期状態として
「Aさんの口座には1000円、Bさんの口座には2000円」入っているものとします。

BEGIN;

UPDATE accounts2 SET balance = 1000, version = 1 WHERE name = 'A';

UPDATE accounts2 SET balance = 2000, version = 1 WHERE name = 'B';

COMMIT;

▼プログラム1 (AからBへ500円振り込む)

BEGIN;
  -- Aの残高とバージョンを調べる
  SELECT balance, version FROM accounts2 WHERE name = 'A';
  -- Bの残高とバージョンを調べる
  SELECT balance, version FROM accounts2 WHERE name = 'B';
  -- もし A のバージョンが1だったら、Aの残高を500円にして、バージョンを2にする
  UPDATE accounts2 SET balance = 500, version = 2 WHERE name = 'A' AND version = 1;
  -- もし B のバージョンが1だったら、Bの残高を2500円にして、バージョンを2にする
  UPDATE accounts2 SET balance = 2500, version = 2 WHERE name = 'B' AND version = 1;
COMMIT;

▼プログラム2 (BからAへ1200円振り込む)

BEGIN;
  -- Aの残高とバージョンを調べる
  SELECT balance, version FROM accounts2 WHERE name = 'A';
  -- Bの残高とバージョンを調べる
  SELECT balance, version FROM accounts2 WHERE name = 'B';
  -- もし A のバージョンが1だったら、A の残高を2200円にして、バージョンを2にする -> 更新件数ゼロ
  UPDATE accounts2 SET balance = 2200, version = 2 WHERE name = 'A' AND version = 1;
  -- もし　B のバージョンが1だったら、B の残高を800円にして、バージョンを2にする -> 更新件数ゼロ
  UPDATE accounts2 SET balance = 800, version = 2 WHERE name = 'B' AND version = 1;
COMMIT;

もし更新できなかったら最初からやり直します。それは RDBMS 任せではなくて Ruby なり何なりのコードを使って、実装します。

Rails の場合、lock_version カラムという名前のカラムを用意するだけです。

class AddLockVersionToAccounts < ActiveRecord::Migration[7.0]
  def change
    change_table :accounts do |t|
      t.integer :lock_version
    end
  end
end

更新時するたびに lock_version カラムの値をインクリメントしてくれるのと、
競合を検出した場合に例外 ActiveRecord::StaleObjectError を発生させてくれます。

楽観的ロックを使うからといって、トランザクションを考えずに済むわけではありません。

トランザクションブロックで囲むのと、異常が発生したときにロールバックするようにコーディングをするのは必須です。

そして、 ActiveRecord::StaleObjectError を rescue してトランザクション全体をリトライするか、画面に「更新が衝突しました。もういちどお試しください」というふうにメッセージを出すなどしてユーザに再実行を促す必要があります。

これら「悲観的ロック」「楽観的ロック」も様々なシステムに共通する、一般的な概念です。

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

ACID の "I" は何か、覚えていますか？
I = Isolation (分離性) でしたね。

実行中のトランザクションが他のトランザクションの実行による影響を受けないことを「分離性」といいます。その性質を見ていきましょう。

まずデータをリセットします。

BEGIN;


INSERT INTO accounts(name ,balance) VALUES ('A', 1000);

INSERT INTO accounts(name ,balance) VALUES ('B', 2000);

COMMIT;

TRUNCATE accounts; の ”TRUNCATE” は「切り詰める」という意味です。 DELETE FROM accounts; よりも高速にデータを削除することがてきます。

ただしトランザクション内での利用は不可、ロールバック不可で、削除された行数は出力されない、等のデメリットがあります。

さて RDBMS にはトランザクション分離レベルというものがありまして、
「トランザクションがどれくらいの分離性を確保するか」を設定できるようになってます。

   ↑分離性が強い
SERIALIZABLE;
REPEATABLE READ;
READ COMMITTED;
READ UNCOMMITTED;
   ↓分離性が弱い

トランザクション分離レベルは、 BEGIN 文のオプションで指定できます。

BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN ISOLATION LEVEL READ UNCOMMITTED;

Rails の場合は、トランザクションブロック開始時のオプションに、トランザクション分離レベルを指定できます。

ActiveRecord::Base.transaction(isolation: :serializable) do ... end
ActiveRecord::Base.transaction(isolation: :repeatable_read) do ... end
ActiveRecord::Base.transaction(isolation: :read_committed) do ... end
ActiveRecord::Base.transaction(isolation: :read_uncommitted) do ... end

デフォルト (未指定) のときは RDBMS のデフォルトになります。

MySQL の場合は "REPEATABLE READ"
PostgreSQL の場合は "READ COMMITTED"

です。これから1個ずつ試していきましょう。

SERIALIZABLE

一番分離性が高い SERIALIZABLE を試してみましょう。

データをリセットして…

TRUNCATE accounts;
INSERT INTO accounts(name ,balance) VALUES ('A', 1000), ('B', 2000);

プログラム1 (AからBへ500円振り込む)
プログラム2 (BからAへ1200円振り込む)
の気持ちになって、下記の順番で SQL 文を実行してみてください。

↓左側のウィンドウで実行 = プログラム1 (AからBへ500円振り込む)
        ↓右側のウィンドウで実行 = プログラム2 (BからAへ1200円振り込む)
BEGIN ISOLATION LEVEL SERIALIZABLE;
        BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE name = 'A';
SELECT balance FROM accounts WHERE name = 'B';
UPDATE accounts SET balance = 500 WHERE name = 'A';
UPDATE accounts SET balance = 2500 WHERE name = 'B';
        SELECT balance FROM accounts WHERE name = 'A';
        SELECT balance FROM accounts WHERE name = 'B';
COMMIT;
        UPDATE accounts SET balance = XXXX WHERE name = 'B';
        UPDATE accounts SET balance = XXXX WHERE name = 'A';
        COMMIT;

プログラム2の UPDATE 文で ERROR: could not serialize access due to concurrent update というエラーが出て更新が失敗しましたよね。 ROLLBACK を余儀なくされているのではないでしょうか。

SERIALIZABLE というのは「同時にトランザクションを実行した場合であっても、1個ずつトランザクションを実行したのと同じ結果となることを保証する、保証できない場合にはトランザクションを失敗させる」というものです (PostgreSQL の場合)。

エラーの文章を意訳すると「1個ずつトランザクションを実行したときと同じ結果になることが保証できないので失敗したよ!」の意です。

これを使うことで、分離性に関する問題を完全に回避できます。
ロックを書き忘れても安全です。
(ただしエラーを rescue してリトライするなどする必要はあります)

しかし、パフォーマンスが良くありません。なので…普通は利用禁止です。
Web開発で通常は使うことはないんじゃないかな…と思います。

REPEATABLE READ

SERIALIZABLE の次に分離性が強いのが REPEATABLE READ です。

データをリセットして…

TRUNCATE accounts;
INSERT INTO accounts(name, balance) VALUES ('A', 1000), ('B', 2000);

やってみましょう。

↓左側のウィンドウで実行 = プログラム1 (AからBへ500円振り込む)
        ↓右側のウィンドウで実行 = プログラム2 (BからAへ1200円振り込む)
BEGIN ISOLATION LEVEL REPEATABLE READ;
        BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE name = 'A';
SELECT balance FROM accounts WHERE name = 'B';
UPDATE accounts SET balance = 500 WHERE name = 'A';
UPDATE accounts SET balance = 2500 WHERE name = 'B';
        SELECT balance FROM accounts WHERE name = 'A';
        SELECT balance FROM accounts WHERE name = 'B';
COMMIT;
        UPDATE accounts SET balance = XXXX WHERE name = 'B';
        UPDATE accounts SET balance = XXXX WHERE name = 'A';
        COMMIT;

SERIALIZABLE のときと同様に

ERROR:  could not serialize access due to concurrent update

が出ましたよね。

これは PostgreSQL の REPEATABLE READ 固有の機能「Snapshot Isolation」によるものです。これを使って `SERIALIZABLE に近いことができるぞ、という機能なんですね。

Write Skew

たとえば、ホテルの受付スタッフさんを管理するテーブルがあるとします。

CREATE TABLE reception (
  id         serial  PRIMARY KEY,
  name       varchar NOT NULL,
  on_duty     boolean NOT NULL
);

INSERT INTO reception(name, on_duty) VALUES ('Alice', true),  ('Bob', true), ('Carol', false);

name	on_duty (勤務中)
Alice	true
Bob	true
Carol	false

ホテルの受付スタッフさんは3人いて、うち1人以上が勤務中、としなければならないとします。
よってスタッフさんは、自分以外に勤務中の人数が1人以上いれば帰れるものとします。
今 Alice と Bob が同時に帰ろうとしました。

これをテーブルに反映していきます。

↓左側のウィンドウで実行 = プログラム1 (Alice が帰る)
        ↓右側のウィンドウで実行 = プログラム2 (Bob が帰る)
BEGIN ISOLATION LEVEL REPEATABLE READ;
        BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM reception WHERE on_duty = true;
        SELECT COUNT(*) FROM reception WHERE on_duty = true;
UPDATE reception SET on_duty=false WHERE name = 'Alice';
        UPDATE reception SET on_duty=false WHERE name = 'Bob';
COMMIT;
        COMMIT;

SELECT * FROM reception;

というわけで、2人とも帰ってしまいました。ちゃんちゃん。

このような更新時異常を Write Skew (書き込みスキュー)といいます。

データを戻して、同じ問題が SERIALIZABLE で発生しないかを確認しましょう。データを初期状態に戻して…

UPDATE reception SET on_duty = true WHERE name = 'Alice';
UPDATE reception SET on_duty = true WHERE name = 'Bob';

レッツトライ。

↓左側のウィンドウで実行 = プログラム1 (Alice が帰る)
        ↓右側のウィンドウで実行 = プログラム2 (Bob が帰る)
BEGIN ISOLATION LEVEL SERIALIZABLE;
        BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM reception WHERE on_duty = true;
        SELECT COUNT(*) FROM reception WHERE on_duty = true;
UPDATE reception SET on_duty=false WHERE name = 'Alice';
        UPDATE reception SET on_duty=false WHERE name = 'Bob';
COMMIT;
        COMMIT;

はい、ちゃんと検出してくれました。

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

Write Skew を回避するには、通常行ロックを使いますが、トランザクション分離レベルを SERIALIZABLE にすることでも解決できます。ただし SERIALIZABLE は遅いのでパフォーマンスが犠牲になります。

READ COMMITTED

READ COMMITTED は REPEATABLE READ より1段低い、トランザクション分離レベルです。

これが、PostgreSQL のデフォルトです。 REPEATABLE READ と同様に Write Skew が発生し、さらに加えていろんな問題が発生します。

Phantom Read

データをリセットして…

TRUNCATE accounts;
INSERT INTO accounts(name, balance) VALUES ('A', 1000), ('B', 2000);

C さんの口座がトランザクション中に追加されたとして、他のトランザクションからそれが見えるでしょうか？
さっそく試してみましょう。
次のものを実行してみてください。

↓プログラム1 (C さんの口座を追加)
        ↓プログラム2 (口座一覧を取得する)
BEGIN ISOLATION LEVEL READ COMMITTED;
        BEGIN ISOLATION LEVEL READ COMMITTED;
        SELECT * FROM accounts;
SELECT * FROM accounts;
INSERT INTO accounts(name, balance) VALUES ('C', 0);
SELECT * FROM accounts;
COMMIT;
        SELECT * FROM accounts; -- C の口座は見える??
        COMMIT;

C さんの口座、見えちゃってますよね？

このように「別のトランザクションによって INSERT されたレコードが見えてしまう」異常現象のことを「Phantom Read」といいます。

またデータをリセットして…

TRUNCATE accounts;
INSERT INTO accounts(name, balance) VALUES ('A', 1000), ('B', 2000);

REPEATABLE READ で発生しない、ということを確認しましょう。

↓プログラム1 (C さんの口座を追加)
        ↓プログラム2 (口座一覧を取得する)
BEGIN ISOLATION LEVEL REPEATABLE READ;
        BEGIN ISOLATION LEVEL REPEATABLE READ;
        SELECT * FROM accounts;
SELECT * FROM accounts;
INSERT INTO accounts(name, balance) VALUES ('C', 0);
SELECT * FROM accounts;
COMMIT;
        SELECT * FROM accounts; -- C は見える??
        COMMIT;

ちなみに、Phantom Read は REPEATABLE READ でも発生するものとされています。
ただし PostgreSQL の REPEATABLE READ では発生しません。
PostgreSQL では Snapshot Isolation という仕組みがあり、 REPEATABLE READ であっても Phantom Read が発生しないようになってるんですよ。すごいですよね。

Non-Repeatable Read (別名 Fuzzy Read)

またデータをリセットして…

TRUNCATE accounts;
INSERT INTO accounts(name, balance) VALUES ('A', 1000), ('B', 2000);

トランザクション中にAさんの残高を0円にしたとします。

↓プログラム1 (Aさんの残高を0円にする)
        ↓プログラム2 (残高の一覧を取得する)
BEGIN ISOLATION LEVEL READ COMMITTED;
        BEGIN ISOLATION LEVEL READ COMMITTED;
        SELECT * FROM accounts; -- A さんの残高は? (1回目)
SELECT * FROM accounts;
UPDATE accounts SET balance = 0 WHERE name = 'A';
SELECT * FROM accounts;
COMMIT;
        SELECT * FROM accounts; -- A さんの残高は? (2回目)
        COMMIT;

1回目は1000円、2回目は0円でしたよね。

同じトランザクションブロックでの SELECT なのに、
他のトランザクションによって数字が変化してしまっています。

この現象のことを Non-Repeatable Read、別名 Fuzzy Read といいます。

再びリセットして、

TRUNCATE accounts;
INSERT INTO accounts(name ,balance) VALUES ('A', 1000), ('B', 2000);

同じことが REPEATABLE READ で発生しないか見てみましょう。

↓プログラム1 (Aさんの残高を0円にする)
        ↓プログラム2 (残高の一覧を取得する)
BEGIN ISOLATION LEVEL REPEATABLE READ;
        BEGIN ISOLATION LEVEL REPEATABLE READ;
        SELECT * FROM accounts; -- A さんの残高は? (1回目)
SELECT * FROM accounts;
UPDATE accounts SET balance = 0 WHERE name = 'A';
SELECT * FROM accounts;
COMMIT;
        SELECT * FROM accounts; -- A さんの残高は? (2回目)
        COMMIT;

1回目も2回目も同じ数字になっていることが確認できましたね。

Read Skew

またデータをリセットして…

TRUNCATE accounts;
INSERT INTO accounts(name, balance) VALUES ('A', 1000), ('B', 2000);

プログラム1 (AからBへ500円振り込む)、
プログラム2 (BからAへ1200円振り込む)、
を次の順番で実行していきましょう。

↓プログラム1 (AからBへ500円振り込む)
        ↓プログラム2 (BからAへ1200円振り込む)
BEGIN ISOLATION LEVEL READ COMMITTED;
        BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE name = 'A';
SELECT balance FROM accounts WHERE name = 'B';
UPDATE accounts SET balance = 500 WHERE name = 'A';
UPDATE accounts SET balance = 2500 WHERE name = 'B';
        SELECT balance FROM accounts WHERE name = 'A';
        SELECT balance FROM accounts WHERE name = 'B';
COMMIT;
        UPDATE accounts SET balance = XXXX WHERE name = 'B';
        UPDATE accounts SET balance = XXXX WHERE name = 'A';
        COMMIT;

結果としておかしな値になりましたよね？
読み取りタイミングの差によって発生する、この現象のことを Read Skew といいます。
PostgreSQL の場合 REPEATABLE READ 以上にすることで、発生しなくなります。

口座問題の解消法

口座の同時更新問題は、以下の4通りのアプローチで解決が可能です。

悲観的ロックで、ブロックする (SELECT … FOR UPDATE)
- 後のトランザクションの実行が待たされる場合があるので
  Timeout になるリスクがある
悲観的ロックで、ロックがとれない場合に失敗させる ( SELECT .. FOR UPDATE NOWAIT)
- リトライなりエラー画面なりのを実装するのと、発生ケースのテストが必要なので面倒
楽観的ロック
- リトライなりエラー画面なりのを実装するのと、発生ケースのテストが必要なので面倒
REPEATABLE READ 以上のトランザクション分離レベルにする
- リトライなりエラー画面なりのを実装するのと、発生ケースのテストが必要なので面倒

考えなきゃ、できない

はい、これが、トランザクションです。
見て分かる通り、「これをやっておけば大丈夫」という万能な解決策は、ありません。
考えないと解決できない問題・知っておかないとなんともならない、そんな問題だらけです。

うまくやれるようにがんばっていきましょう。

覚えると、危険

Phantom Read, Read Skew, Write Skew, Lost Update, Non-Repeatable Read (Fuzzy Read) の他にも、Observe Skew や Dirty Read などがあったり、MySQL と PostgreSQL での動作の違いもたくさんあったり…本当にいろいろな違いがあるんです。NoSQL も含めればより一層深い世界が広がっています。

が、個人的には、そうした細かい挙動は敢えて覚えないようにしています。
記憶を頼りにすると、間違えそうで怖いからです。

航空機のパイロットは離陸時や着陸時、非常時に必ずチェックリストを使って確認します。それは、記憶に頼ることがヒューマンエラーの原因となってしまうからです。

違和感を感じたり、必要があるたびに細かく調べて、手で SQL 文を叩いて挙動を確認しましょう。

勉強会

Techouse の基礎勉強会の一部をご覧いただきました。
いかがでしたでしょうか。
Techouse の取り組みに興味を持ってくださいましたら、幸いです。

Techouseでは、社会課題の解決に一緒に取り組むエンジニアを募集しております。ご応募お待ちしております。

jp.techouse.com

Techouse Developers Blog

テックハウス開発者ブログ｜マルチプロダクト型スタートアップ｜エンジニアによる技術情報を発信｜SaaS、求人プラットフォーム、DX推進

Techouse社内勉強会の内容を紹介します(2) データベース<分離性編>

Techouseの「エンジニア基礎勉強会」とは

データベース(2) トランザクション <中編: 分離性>

行ロック色々

排他ロックと共有ロック

UPDATE 文実行時の排他ロック

長時間のロック

NOWAIT オプション

SKIP LOCKED オプション

悲観的ロック

楽観的ロック

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

SERIALIZABLE

REPEATABLE READ

Write Skew

READ COMMITTED

Phantom Read

Non-Repeatable Read (別名 Fuzzy Read)

Read Skew

口座問題の解消法

考えなきゃ、できない

覚えると、危険

勉強会