ストレージエンジンとロックの特性

MySQL におけるロックの挙動は、選択するストレージエンジンによって大きく異なります。主に利用される MyISAM と InnoDB の特徴を比較します。

• MyISAM: オーバーヘッドが小さくロック獲得が高速です。デッドロックは発生しませんが、ロック粒度が表単位であり concurrent access（同時アクセス）の性能が低下しやすいです。トランザクションをサポートしていないため、現在の主要な用途では採用されることが少なくなっています。
• InnoDB: オーバーヘッドは较大ですが、行レベルでのロック制御が可能です。デッドロックの可能性がありますが、競合が発生しにくく、トランザクションをサポートしています。MySQL のデフォルトエンジンとして推奨されます。

concurrent access における課題：在庫の二重更新

在庫管理システムなどで典型的な問題として、「在庫超売（オーバーセールス）」があります。在庫数が 1 の商品に対し、複数のユーザーが同時に購入処理を行った場合、以下のような競合状態が発生する可能性があります。

1. ユーザー A が在庫確認を行い、剩余数が 1 であることを確認。
2. ユーザー A が更新処理を実行する前に、ユーザー B も在庫確認を行い、剩余数が 1 であることを確認。
3. 双方が更新処理を実行し、在庫数が -1 になってしまう。

この問題に対処するための主なアプローチは以下の 2 通りです。

1. 悲観的ロック（Pessimistic Locking）

データ取得時点でロックを獲得し、トランザクションが完了するまで他からのアクセスをブロックします。

SELECT stock_count FROM product_inventory WHERE sku_id = 1001 FOR UPDATE;
UPDATE product_inventory SET stock_count = stock_count - 1 WHERE sku_id = 1001;
COMMIT;

これにより、最初のトランザクションが完了するまで、他のトランザクションはその行にアクセスできなくなります。

2. 楽観的ロック（Optimistic Locking）

バージョン番号を用いて、取得時からデータが変更されていないことを確認してから更新を行います。

-- 取得時
SELECT stock_count, version FROM product_inventory WHERE sku_id = 1001;
-- 更新時（バージョンが一致した場合のみ更新）
UPDATE product_inventory 
SET stock_count = stock_count - 1, version = version + 1 
WHERE sku_id = 1001 AND version = 取得したバージョン;

更新対象行数が 0 の場合は、他から変更されたことを意味するため、処理を再試行またはエラーとします。

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

MySQL では、トランザクションの隔離レベルを設定することで、データの整合性とパフォーマンスのバランスを制御できます。

• READ UNCOMMITTED: 未コミットのデータも読み取れます。ダーティリードが発生する可能性があります。
• READ COMMITTED: コミットされたデータのみ読み取れます。ダーティリードは防止されますが、不可重複読やファントム読が発生する可能性があります。
• REPEATABLE READ: 同一トランザクション内での読み取り結果の一貫性が保証されます。InnoDB のデフォルト設定です。ファントム読については特定の条件下で防止されます。
• SERIALIZABLE: 最も厳格な隔離レベルです。すべての読み取りにロックがかかり、並行性が大幅に低下しますが、すべての異常現象を防止できます。

主な現象の定義：

• ダーティリード: 他トランザクションが未コミットの変更内容を読み込んでしまう現象。
• 不可重複読: 同一トランザクション内で、同じ行を再度読み込んだ際に値が変更されている現象。
• ファントム読: 同一トランザクション内で、同じ条件で検索した際に行数が変動する現象。

ロック操作の実践

実際にロックの挙動を確認するためのコマンドと手順を紹介します。

隔離レベルの確認と変更

-- 現在の隔離レベルを確認
SHOW GLOBAL VARIABLES LIKE 'transaction_isolation';

-- 隔離レベルを変更（例：READ UNCOMMITTED）
SET GLOBAL transaction_isolation = 'READ-UNCOMMITTED';

テーブルロック（Table Lock）

MyISAM など、行ロックをサポートしないエンジンで使用されます。明示的にロックを宣言する必要があります。

-- 書き込みロック（他セッションからの読み書きをブロック）
LOCK TABLES stock_manager WRITE;

-- 読み込みロック（他セッションからの読み取りは許可、書き込みはブロック）
LOCK TABLES stock_manager READ;

-- ロックの解放
UNLOCK TABLES;

複数の接続間でロックを獲得した場合、ロックを解放するまで他のセッションは待機状態になります。

行ロック（Row Lock）

InnoDB エンジンでは、更新系语句実行時に自動的に行ロックがかかります。トランザクションの制御には自動コミットの設定変更が必要です。

-- 自動コミットを無効化
SET autocommit = 0;

-- 更新処理（該当行にロックがかかる）
UPDATE stock_manager SET quantity = 5 WHERE item_id = 1;

-- 変更の確定
COMMIT;

別のセッションで同じ行を更新しようとすると、最初のトランザクションが完了するまで待機させられます。異なる行を更新する場合は、ロック競合は発生しません。

デッドロック（Deadlock）

複数のトランザクションが互いに相手のロックを待ち続ける状態をデッドロックと呼びます。

例：

• トランザクション A: 行 1 をロック -> 行 2 のロックを待機
• トランザクション B: 行 2 をロック -> 行 1 のロックを待機

この状態になると、双方とも処理を継続できなくなります。MySQL はデッドロックを検知すると、一方のトランザクションを自動的にロールバックして解消を試みます。必要に応じて、長時間待機しているプロセスを強制終了（KILL）することで対応することもあります。