はじめに

本記事は学習コミュニティで書籍勉強会を実施した時の発表資料です。
使用書籍：『スッキリわかるSQL入門』

今回はトランザクションの続きとして、SQL文を用いて対象をロックする方法について記載します。

何か気づいた点等ございましたら、コメントいただけますと幸いです。

なぜ対象データをロックするのか

結論から言うと、処理の途中で他者による対象データへの同時操作により、正確な処理ができなくなるのを防ぐため。

前回作成した記事の「トランザクションの分離」の所でも述べているので、よければ見てください。

ロックの種類

主に以下の3種類。

ちなみに、ロックをかける時に厳しさを指定することができる。

行ロックの方法

普通、SELECT文で選択した行には自動で共有ロックがかかる。排他ロックをかけるためには、SELECT文の最後にFOR UPDATEをつければ良い。

SELECT ~ FOR UPDATE (NOWAIT)

文末のNOWAITオプションを加えると、DBMSは他のトランザクションのロック解除を待たずにロック失敗のエラーを返すようになる。
処理を待たせたくないアプリケーションで有効なオプション。

（例）収支表テーブルの2021年6月以降のデータに対して行ロックをする

 BEGIN ;  -- トランザクション開始
SELECT * FROM 収支表
 WHERE 日付 >= '2021-06-01'
   FOR UPDATE ;  -- 6月以降のデータを排他ロック
SELECT ~ ;  -- [集計処理1]
SELECT ~ ;  -- [集計処理2]
SELECT ~ ;  -- [集計処理3]
COMMIT;  -- ロックが解除され、トランザクション終了

表ロックの方法

特定の表全体をロックするためには、LOCK TABLE命令を利用する。

LOCK TABLE テーブル名 IN モード名 MODE (NOWAIT)
-- モード名にはEXCLUSIVE(排他ロック)かSHARE(共有ロック)が入る

（例）収支表テーブルに対して表ロックをする

BEGIN ;  -- トランザクション開始
LOCK TABLE 収支表 IN EXCLUSIVE MODE ;  -- 表を排他ロック
SELECT ~ ;  -- [集計処理1]
SELECT ~ ;  -- [集計処理2]
SELECT ~ ;  -- [集計処理3]
COMMIT;  -- ロックが解除され、トランザクション終了

デッドロックについて

デッドロックは複数のトランザクションの処理が途中で永久に止まる状態のこと。
データベース上で同時に多くのトランザクションが実行されると発生する可能性がある。

図を見れば分かりやすいので以下の通り。

デッドロックによる処理の停止を防ぐため、DBMSにはデッドロックを自動的に解決する仕組みがある。
具体的には、デッドロックを発見したら片方のトランザクションを強制終了（失敗）させることで、デッドロックを解決するという仕組み。
しかし、片方の処理が失敗することや処理の停止時間が発生するのを考慮すると、デッドロックは極力避けるべき。

デッドロックの予防法は次の二つが挙げられる。

1. トランザクションの時間を短くする
2. 同じ順番でロックする

1に関して、ロックしている時間が短いほど他のトランザクションと競合する可能性は低くなる。
2に関して、デッドロックは2つのトランザクションがそれぞれ異なる順番でロックを行うために発生する問題だと分かれば、納得がいく。

まとめ

• SQL文を用いて行や表、データベース全体にロックをかけることが可能。
• 複数の対象に異なる順番でロックを試みると、デッドロックが発生する可能性がある。
• デッドロックを防ぐためには、トランザクションの時間を短縮したり、ロックの順番を統一するとよい。

今後も学習する過程で、有益な情報を発信できればと思いますので、良ければLGTMをお願いします！

参考文献

この記事は以下の書籍を参考にして執筆しました。
・書籍『スッキリわかるSQL入門第2版ドリル222問付き！』

勉強会メンバー記事

・[SQL]SQL入門　-テーブルの作成-
・SQLのまとめ③(インデックスについて)
・【SQL書籍勉強会】テーブル設計

過去の記事

・【SQL】基本文法とデータ型
・【SQL】4大命令ってなんだっけ？
・【SQL】データベースの真価は「テーブルの結合」にあり！
・【SQL】トランザクションをマスターしよう！