MariaDB講座 / 二つの接続で分離レベル・ロック・デッドロックを比べる

MariaDB講座

分離レベルは他の接続の変更を読める範囲を定め、ロックは競合する操作を調整します。独立した二接続を決めた順に操作すると、REPEATABLE READ、READ COMMITTED、ロック待ちとデッドロックを別々に確認できます。

接続A・Bを別々に準備する

同じ学習サーバーとshopcourseへ二つのクライアントを開きます。CONNECTION_ID()が違うことを確かめ、次の保存操作は各接続で行います。一窓へ両側を貼るだけでは同時実行になりません。

SELECT CONNECTION_ID(),DATABASE(),@@SESSION.tx_isolation;
SET @saved_isolation=@@SESSION.tx_isolation;
SET @saved_wait=@@SESSION.innodb_lock_wait_timeout;

Aだけで、既存表を確認してからセットアップします。

USE shopcourse;
CREATE TABLE accounts_36_lab (
 account_id INT PRIMARY KEY,balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts_36_lab VALUES(1,1000),(2,2000);

普通のSELECTの一貫性読み取りと、FOR UPDATEの現在・ロック読み取りは区別します。トランザクションを理解し、本番の表では実験しないでください。

REPEATABLE READをA→B→Aで比較する

初期残高1000から、Aで設定・開始・最初の読取です。

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

次にBで1100へ確定します。

START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;

Aで同じトランザクションのまま再読取します。

SELECT balance FROM accounts_36_lab WHERE account_id=1;

Aの二回は1000です。普通のSELECTが使うスナップショットの比較で、FOR UPDATEも同じという意味ではありません。Aで終了して読みます。

ROLLBACK;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

今度は1100です。

READ COMMITTEDを初期値から比較する

まずBで前の処理を終え、1000へ戻して確定します。

ROLLBACK;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1000 WHERE account_id=1;
COMMIT;

AでREAD COMMITTEDにして最初の読取をします。

ROLLBACK;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

Bで1100へ確定します。

START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;

Aで二回目を読み、終了します。

SELECT balance FROM accounts_36_lab WHERE account_id=1;
ROLLBACK;

Aは1000、1100と変わります。各文に見える確定済みデータを読みます。アプリケーションの分離方針は読み取りだけでなく書き込み競合や整合性でも判断します。

保持ロックと待ち時間切れを分ける

Aで一行をロックしたままにします。

START TRANSACTION;
SELECT account_id,balance FROM accounts_36_lab
WHERE account_id=1 FOR UPDATE;

Bで待ち時間を3秒にし、同じ行を更新します。

SET SESSION innodb_lock_wait_timeout=3;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

Aが開いたままならBは約3秒でタイムアウトします。Bの全トランザクションが自動取消されたと決めつけず、Bで明示して終えます。

ROLLBACK;
SET SESSION innodb_lock_wait_timeout=@saved_wait;

次にAも終了し、ロックを解放します。

ROLLBACK;

四つの交互操作でデッドロックを作る

前の両接続が終了した状態で、A-1→B-1→A-2→B-2の順です。まずAで行1を取得します。

START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

Bで行2を取得します。

START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;

AでBの行2を要求します。

UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;

Aが待っている間に、待ちの完了を待たずBへ切り替えてAの行1を要求します。

UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

InnoDBの検出が有効なら、片方にDeadlock foundが返ります。被害側は固定されません。両窓でそれぞれROLLBACKします。単なる待ち時間切れと循環待ちは違い、プログラムには回数を限った全処理の再試行と二重処理防止が必要です。

両接続の設定を復元する

複数行を同じキー順で取得し、トランザクションを短くすると競合を減らせます。広い条件や有用な索引の不足はロック範囲を増やし得ます。問い合わせ、ログと実行順を調べ、接続障害として一括りにしません。

ROLLBACK;
SET SESSION tx_isolation=@saved_isolation;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
SELECT @@SESSION.tx_isolation,@@SESSION.innodb_lock_wait_timeout;

これはA・B両方で独立に行います。再接続すると保存変数はないため、設定を新しく確認します。両方終了し、必要なデータを保存して、削除してよい実習表だけを消します。