PostgreSQLにおけるinsertとdeleteの競合について

Question

PostgreSQL 9.6 にて、以下の事象が理解できずに困っております。

二つのトランザクションA,Bがあり、Aは既存レコードのdeleteを、Bは既存レコードのPKの値と同じ値をもつレコードのinsertを行います。
TX Aから開始し、delete直後にTX Bを開始します。

デフォルトのテーブルの状態は以下です。

> select * from hoge;
id
----
  1
(1 row)

TX A:
> begin;
BEGIN
> delete from hoge where id = 1;
DELETE 1
# ここでTX Bを開始
# TX Bのinsert実行後に再開
> commit;
COMMIT

TX B:
> begin;
BEGIN
> insert into hoge values (1);
# TX Aがcommitされるまでinsertが実行されない　※1
INSERT 0 1
> commit;
COMMIT

TX Bの※1部分でinsert処理が待たされますが、この挙動は以下のように考えました。
リファレンス を確認しますと、deleteとinsertではテーブルレベルロック ROW EXCLUSIVE を取得している認識です。

ROW EXCLUSIVE
SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、およびACCESS EXCLUSIVEロックモードと競合します。

UPDATE、DELETE、およびINSERTコマンドは、（参照される他の全てのテーブルに対するACCESS SHAREロックに加えて）対象となるテーブル上にこのモードのロックを獲得します。 通常、このロックモードは、テーブルのデータを変更する問い合わせにより獲得されます。

しかし、ロックモードの競合 を確認しますと、 ROW EXCLUSIVE 同士は競合となり得ないように見受けられました。

上記の挙動の理由/仕組みをご教授頂けないでしょうか。
宜しくお願いいたします。

Comments

[yambejp]

Postgreで確定しているならMySQLタグは外しておいたほうがよいでしょう

[KOZ6.0]

hoge テーブルにプライマリキーがあって行ロックがかかっているのでは？
TX B を insert into hoge values (2); にしたらどうなりますか？

[退会済みユーザー]

ご指摘ありがとうございます。insertに成功しました。

Answer 1

PostgreSQLではSQL標準規格のトランザクション分離レベルのREAD COMMITTEDをデフォルトに設定しています。
PostgreSQLのREAD COMMITTEDにおける insert の仕様は、今回の再現手順を実行するとShareLockを取得しようとして待たされるため、PostgreSQLのREAD COMMITTEDにおける UPDATE 、 DELETE 、SELECT FOR UPDATE 、および SELECT FOR SHARE と同じ仕様ではないかと考えられます。

まとめますと

• delete および insert は ROW EXCLUSIVE テーブルレベルロックを取得する（競合しない）。
• PostgreSQLのREAD COMMITTED（デフォルト）における insert の仕様は、他トランザクションによってその結果が可視となっていない（ commit / rollback されていない）場合、そのトランザクションがcommit / rollback されるのを待つ。

---

• 現在のトランザクション分離レベルの確認

show transaction isolation level;
 transaction_isolation
-----------------------
 read committed
(1 row)

read committed分離レベル

PostgreSQLではリードコミッティドがデフォルトの分離レベルです。 トランザクションがこの分離レベルを使用すると、SELECT問い合わせ（FOR UPDATE/SHARE句を伴わない）はその問い合わせが実行される直前までにコミットされたデータのみを参照し、まだコミットされていないデータや、その問い合わせの実行中に別の同時実行トランザクションがコミットした更新は参照しません。 結果として、SELECT問い合わせはその問い合わせが実行を開始した時点のデータベースのスナップショットを参照することになります。 しかしSELECT文は、自分自身のトランザクション内で実行され更新された結果はたとえまだコミットされていなくても参照します。 単一のトランザクション内であっても、SELECT文を2回連続して発行した場合、最初のSELECT文が開始した後で2番目のSELECT文が開始する前に他のトランザクションが更新をコミットすると、最初とその次に発行したSELECT問い合わせは異なるデータを参照してしまうことにも注意してください。

UPDATE、DELETE、SELECT FOR UPDATE、およびSELECT FOR SHAREコマンドは対象行を検索する際にSELECTコマンドと同じように振舞います。 これらのコマンドは、問い合わせが開始された時点で既にコミットされた対象行のみを検出します。しかし、その対象行は、検出されるまでに、同時実行中の他のトランザクションによって、既に更新（もしくは削除あるいはロック）されてしまっているかもしれません。 このような場合更新されるべき処理は、最初の更新トランザクションが（それがまだ進行中の場合）コミットもしくはロールバックするのを待ちます。 最初の更新処理がロールバックされるとその結果は無視されて、2番目の更新処理で元々検出した行の更新を続行することができます。 最初の更新処理がコミットされると、2番目の更新処理では、最初の更新処理により行が削除された場合はその行を無視します。 行が削除されなかった時の更新処理は、最初のコミットで更新された行に適用されます。 コマンドの検索条件（WHERE句）は、更新された行がまだその検索条件に一致するかどうかの確認のため再評価されます。 検索条件と一致している場合、2番目の更新処理は、更新された行を使用して処理を開始します。 SELECT FOR UPDATEおよびSELECT FOR SHAREの場合、ロックされクライアントに返されるのは、更新されるバージョンの行であることを意味します。

pg_locks

指定されたプロセスにより保持されているロックを表す行内ではgrantedは真です。偽の場合はこのロックを獲得するため現在プロセスが待機中であることを示しています。つまり、同じロック対象のオブジェクトに対して何らかの他のプロセスが競合するロックを保持、もしくは待機していることを意味します。 待機中のプロセスはその別のプロセスがロックを解放するまで活動を控えます。 （もしくはデッドロック状態が検出されることになります）。 単一プロセスでは一度に多くても1つのロックを獲得するために待機します。

pg_locks（原文）

Throughout running a transaction, a server process holds an exclusive lock on the transaction's virtual transaction ID. If a permanent ID is assigned to the transaction (which normally happens only if the transaction changes the state of the database), it also holds an exclusive lock on the transaction's permanent transaction ID until it ends. When a process finds it necessary to wait specifically for another transaction to end, it does so by attempting to acquire share lock on the other transaction's ID (either virtual or permanent ID depending on the situation). That will succeed only when the other transaction terminates and releases its locks.

• TX Aにてdelete実行直後のロック状態

> SELECT l.pid,d.datname,l.locktype,relation::regclass,transactionid,l.mode,l.granted
    FROM pg_locks l  LEFT JOIN pg_database d ON l.database = d.oid
    WHERE  l.pid != pg_backend_pid()
    ORDER BY l.pid;
 pid | datname |   locktype    | relation  | transactionid |       mode       | granted
-----+---------+---------------+-----------+---------------+------------------+---------
  96 | root    | relation      | hoge_pkey |               | RowExclusiveLock | t
  96 | root    | relation      | hoge      |               | RowExclusiveLock | t
  96 |         | virtualxid    |           |               | ExclusiveLock    | t
  96 |         | transactionid |           |          1701 | ExclusiveLock    | t
(4 rows)

• TX Bにてinsert実行後のロック状態

=> TX A(id:1699)の完了を待っていることが、ShareLockがnot grantedであることからわかる

119 | | transactionid | | 1699 | ShareLock | f

> SELECT l.pid,d.datname,l.locktype,relation::regclass,transactionid,l.mode,l.granted
    FROM pg_locks l  LEFT JOIN pg_database d ON l.database = d.oid
    WHERE  l.pid != pg_backend_pid()
    ORDER BY l.pid;
 pid | datname |   locktype    | relation  | transactionid |       mode       | granted
-----+---------+---------------+-----------+---------------+------------------+---------
  96 |         | virtualxid    |           |               | ExclusiveLock    | t
  96 |         | transactionid |           |          1699 | ExclusiveLock    | t
  96 | root    | relation      | hoge_pkey |               | RowExclusiveLock | t
  96 | root    | relation      | hoge      |               | RowExclusiveLock | t
 119 | root    | relation      | hoge_pkey |               | RowExclusiveLock | t
 119 |         | transactionid |           |          1699 | ShareLock        | f
 119 | root    | relation      | hoge      |               | RowExclusiveLock | t
 119 |         | virtualxid    |           |               | ExclusiveLock    | t
 119 |         | transactionid |           |          1700 | ExclusiveLock    | t
(9 rows)

• TX Aをcommitした直後のロック状態

> SELECT l.pid,d.datname,l.locktype,relation::regclass,transactionid,l.mode,l.granted
    FROM pg_locks l  LEFT JOIN pg_database d ON l.database = d.oid
    WHERE  l.pid != pg_backend_pid()
    ORDER BY l.pid;
 pid | datname |   locktype    | relation | transactionid |       mode       | granted
-----+---------+---------------+----------+---------------+------------------+---------
 119 | root    | relation      | hoge     |               | RowExclusiveLock | t
 119 |         | virtualxid    |          |               | ExclusiveLock    | t
 119 |         | transactionid |          |          1702 | ExclusiveLock    | t
(3 rows)

hoge_pkey は index

> select * from hoge_pkey;
ERROR:  "hoge_pkey" is an index

Answer 2

システムカタログのpg_locksビューでロックモードの確認してみたらいかがでしょうか？

Comments

[退会済みユーザー]

ご指摘ありがとうございます。上記にて確認結果を載せております。