postgresql分離レベルと行ロックの保持

公式はここ
https://www.postgresql.jp/docs/9.4/explicit-locking.html

https://qiita.com/behiron/items/571562ea33b8212a4c32
pg_locksみるときは以下のSQLで。

select
 l.pid,
 l.locktype,
 c.relname as table_name,
 l.page,
 l.tuple,
 l.transactionid,
 l.mode,
 l.granted, -- trueの場合lock取得済み
 s.state
from
 pg_locks l
 left join pg_class c  on (l.relation=c.oid)
 left join pg_stat_activity s on (l.pid=s.pid)
where
 l.mode <> 'AccessShareLock' -- レコードが多くなり見辛くなるので対象外
 and
 l.pid <> pg_backend_pid()  -- このクエリ実行自体は対象外
order by
 l.pid;

pg_locksは行ロックがでないのでpgrowlocksを使う
psqlで

CREATE EXTENSION pgrowlocks;

するだけ。
https://qiita.com/myzkyy/items/d456747f2c70a748587a
pgrowlocksで見るときは以下SQLで

SELECT * FROM tablename AS t, pgrowlocks('tablename') AS p WHERE p.locked_row = t.ctid;

#READ COMMITTED

Tran1

postgres=# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN
postgres=*# select * from book where id = 1 for update;
 id | name
----+-------
  1 | book1
(1 row)

for update読み

postgres=# SELECT * FROM book AS t, pgrowlocks('book') AS p WHERE p.locked_row = t.ctid;
 id | name  | locked_row | locker | multi | xids  |     modes      | pids
----+-------+------------+--------+-------+-------+----------------+-------
  1 | book1 | (0,24)     |    768 | f     | {768} | {"For Update"} | {330}
(1 row)

#REPEATABLE READ

postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
postgres=*# select * from book where id = 1 for update;
 id | name
----+-------
  1 | book1
(1 row)

postgres=# SELECT * FROM book AS t, pgrowlocks('book') AS p WHERE p.locked_row = t.ctid;
 id | name  | locked_row | locker | multi | xids  |     modes      | pids
----+-------+------------+--------+-------+-------+----------------+-------
  1 | book1 | (0,24)     |    769 | f     | {769} | {"For Update"} | {330}
(1 row)