👻

PostgreSQLのロックをまとめる

2024/05/16に公開

PostgreSQL

SQL

tech

DBのロックの挙動について理解する機会があったので学んだことをまとめてみます。

ロックとは

DB操作中に他トランザクションからの意図しない変更を防ぐための仕組みで、データの整合性を保つためには不可欠です。ロックを適切に使用しなければデータの不整合が発生する原因になります。
またロック待ちによるパフォーマンス低下や後述するデッドロックの発生に気を付ける必要があります。

参考：

ロックには大きく2種類あります。

排他ロック（Exclusive Lock）

他のトランザクションからロック対象を変更することも参照することも出来ません。

例）排他ロック中のtestテーブルに対する操作

id	name
1	AAA
2	BBB

SELECT * FROM table WHERE id = 1;           -- ロック解放待ち
UPDATE table SET name = 'aaa' WHERE id = 1; -- ロック解放待ち

他のトランザクションからロック対象を変更することは出来ませんが、参照することは出来ます。

例）共有ロック中のtestテーブルに対する操作

id	name
1	AAA
2	BBB

SELECT * FROM table WHERE id = 1;           -- 実行可能
UPDATE table SET name = 'aaa' WHERE id = 1; -- ロック解放待ち

PostgreSQLにおけるロック

PostgreSQLのロックの仕組みとして主にテーブルレベルロックと行レベルロックがあります。

テーブルレベルロック

テーブル全体に対してロックを取得します。以下のロックモードが存在し、表のような競合関係があります。
ロックモード名にrowと入っていてもテーブルレベルロックであることに注意してください。（ロックモードの名前は歴史的なものらしい）

ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE

テーブルレベルロックの競合関係

要求するロックモード	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X		X	X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

行レベルロック

行レベルロックは特定の行に対してロックを取得します。以下のロックモードが存在し、表のような競合関係があります。
行レベルロックは同じ行に対する書き込みとロックだけをブロックしますが、データの問い合わせには影響を与えません。

FOR UPDATE
FOR NO KEY UPDATE
FOR SHARE
FOR KEY SHARE

行レベルロックの競合関係

要求するロックモード	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				X
FOR SHARE			X	X
FOR NO KEY UPDATE		X	X	X
FOR UPDATE	X	X	X	X

ロックを試してみる

ロックの挙動を確認するためにtestテーブルを作成します。
このテーブルを使用して複数トランザクションから操作された際にどのようにロックがかかるか確認します。

CREATE TABLE test(
	id serial,
	name varchar(100)
);
INSERT INTO test(name) VALUES ('AAA');
INSERT INTO test(name) VALUES ('BBB');

読み取り(ACCESS SHARE)と書き込み(ROW EXCLUSIVE)を実行します。
前述の通り、ロックは競合しませんでした。

-- transaction1
BEGIN;
SELECT * FROM test;

-- transaction2
BEGIN;
UPDATE test set name = 'aaa' WHERE id = 1;

読み取り(ACCESS SHARE)と列の追加(ACCESS EXCLUSIVE)を実行します。
前述の通り、ロックは競合します。
transaction1が終了するまでtransaction2の行追加が待機されます。

-- transaction1
BEGIN;
SELECT * FROM test;

-- transaction2
BEGIN;
ALTER TABLE test ADD COLUMN type int;

3. ROW EXCLUSIVE同士

書き込み(ROW EXXCLUSIVE)を別トランザクションから同時に実行します。
こちらも前述の通り、競合しませんでした。

-- transaction1
BEGIN;
UPDATE test SET name = 'aaa' WHERE id = 1;

-- transaction2
BEGIN;
UPDATE test SET name = 'bbb' WHERE id = 2;

しかし、同一レコードに対して更新をかけるとROW EXCLUSIVE同士であるのに競合が発生します。

-- transaction1
BEGIN;
UPDATE test SET name = 'aaa' WHERE id = 1;

-- transaction2
BEGIN;
UPDATE test SET name = 'ccc' WHERE id = 1;

これはテーブルレベルロックは競合しませんが、同一レコードに対して変更を加えることによって行レベルロックが競合しています。

デッドロック

デッドロックとは複数のトランザクションが互いにロックを取得しようとして待機し続ける状況を指します。

下記SQLを実行するとtransaction1, transaction2がそれぞれ取得したロックを保持したまま、相手がロックを取得している行に対して変更を加えることでデッドロックが発生します。
PostgreSQLではデッドロックを検知し、自動的に一方のトランザクションを終了させることでデッドロックを解消します。

-- transaction1
BEGIN;
UPDATE test SET name = 'aaa' WHERE id = 1;

-- transaction2
BEGIN;
UPDATE test SET name = 'bbb' WHERE id = 2;

-- transaction1
UPDATE test SET name = 'ccc' WHERE id = 2; -- transaction2のロック解放待ち

-- transaction2
UPDATE test SET name = 'ddd' WHERE id = 1; -- transaction1のロック解放待ち

デッドロック対策

デッドロックを防ぐためには以下を気を付ける必要があります。

トランザクションで処理するテーブルの順序を統一する
不必要にトランザクションを長くしない
適切なロックレベルを使用する

まとめ

DBにはデータの不整合を防ぐため、ロックを取得して他トランザクションからの操作を防ぐ仕組みがあります。
ロックを適切に使用しなければパフォーマンスの低下やデッドロックの発生によるシステムの停止につながるので、それぞれのロックの挙動を理解しておくことが大切です。