SQLのロックについてまとめています。
SQLのロックの解説
SQLのロックとは、データベース上でクエリによりデータを操作する際、整合性を保つために他のクエリからのアクセスを制御する仕組み。
シンプルに言うと、１つのレコードを更新するのは１つのクエリのみ、というルールを徹底するためのものです。複数のテーブルを同時に更新する場合は、ロック機能が必須です。
例えば、テーブルA、テーブルB、テーブルCをクエリ１が、テーブルAをクエリ２が同時に更新できてしまうと、テーブル間のデータの整合性が取れなくなってしまいます。
sql ロックの種類
SQLのロックには、共有、排他などの種類があります。
共有ロックは、select文など更新をともなわない読み取り操作を許可するロックです。
排他ロックは、insert、update、deleteなどの更新系クエリ実行時に、他のクエリからのアクセスをselectも含めて全てロックし、待たせます。
２つのクエリがお互いに排他ロックをかけあってしまうことを「デッドロック」と呼びます。どちらも待ち状態になるため、永遠にクエリの処理が終わらなくなるんですね。
古いタイプのデータベースでは、デッドロックが頻繁に起こっていたため、ロックの影響範囲を行単位（Oracle）に変えたり、更新用のゆるめの「更新ロック」（SQL Server）というロック種類を作るなど、各DBMSで工夫がされています。
例えば、MySQLやOracleではselect for update という記述が可能。トランザクション内で使用すると、条件に合致した行のみが行ロックされるんですね。テーブル丸ごとの表ロックよりは、はるかにデッドロックの確率が減るというわけです。
// テーブル構造をコピー mysql> create table employees2 like employees; Query OK, 0 rows affected (0.03 sec) // データをコピー mysql> insert into employees2 select * from employees; Query OK, 300024 rows affected (4.46 sec) Records: 300024 Duplicates: 0 Warnings: 0 // データがコピーできたことを確認 mysql> select * from employees2 limit 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec) // トランザクション開始 mysql> begin; Query OK, 0 rows affected (0.00 sec) // for update指定でselect実行 mysql> select first_name from employees2 where emp_no="10001" for update; +------------+ | first_name | +------------+ | Georgi | +------------+ 1 row in set (0.00 sec) // トランザクションが閉じていないので、行ロックが発生する
【関連記事】
▶SQLのfor updateは行レベルでテーブルをロックする トランザクションで有効
SQLのロック単位
ロック単位はDBMSにより異なります。
データベースまるごとのデータベースロック。データベース構造を変更するSQLを発行中などに発生します。
テーブル単位のロックは、通常のinsert、update、deleteなどの更新系クエリの実行で発生します。
テーブル単位でロックすると、デッドロックが頻発するので、ロックの影響範囲を小さくするために出てきたのが、ページ単位のロック、行単位のロック、キー単位のロックなど。
postgreSQLのように、LOCKコマンドで明示的に対象テーブルとロックモードを指定できるものもあります。
下記は、トランザクション開始後にfilmsテーブルにSHARE MODE（共有ロック）をかけている例です。なお、postgreSQLのLOCK命令はテーブル単位でのみ指定が可能です。
BEGIN WORK;
LOCK TABLE films IN SHARE MODE;
SELECT id FROM films
WHERE name = 'Star Wars: Episode I - The Phantom Menace';
-- レコードがなければROLLBACKしてください。
INSERT INTO films_user_comments VALUES
(_id_, 'GREAT! I was waiting for it for so long!');
COMMIT WORK;
まとめ
- SQLのロックは、更新中のデータに対して、他のクエリからのアクセスを制御する機能
- ロックの種類は、共有ロックや排他ロックなど、複数あり
- ロックの影響範囲は、テーブル単位、行単位、ページ単位などDBMSによって異なる
