Writer
$ /usr/pgsql-13/bin/pgbench -i -s 100 -U awsuser -h aurora-postgres124.cluster-********.ap-northeast-1.rds.amazonaws.com -d postgres --partitions=10 --partition-method=range
- クラスターエンドポイントに接続し、テーブルロックをかける。
$ psql "host=aurora-postgres124.cluster-********.ap-northeast-1.rds.amazonaws.com user=awsuser dbname=postgres port=5432"
> begin;
> lock table pgbench_accounts in access exclusive mode;
Reader
$ psql "host=aurora-postgres124.cluster-ro-********.ap-northeast-1.rds.amazonaws.com user=awsuser dbname=postgres port=5432"
> select l.pid,l.granted,d.datname,l.locktype,relation,relation::regclass,transactionid,l.mode
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 | granted | datname | locktype | relation | relation | transactionid | mode
9204 | t | postgres | relation | 16508 | pgbench_accounts_2 | | AccessExclusiveLock
9204 | t | postgres | relation | 16585 | pgbench_accounts_1 | | AccessExclusiveLock
9204 | t | postgres | relation | 16526 | pgbench_accounts_8 | | AccessExclusiveLock
9204 | t | | virtualxid | | | | ExclusiveLock
9204 | t | postgres | relation | 16517 | pgbench_accounts_5 | | AccessExclusiveLock
9204 | t | postgres | relation | 16520 | pgbench_accounts_6 | | AccessExclusiveLock
9204 | t | postgres | relation | 16523 | pgbench_accounts_7 | | AccessExclusiveLock
9204 | t | postgres | relation | 16499 | pgbench_accounts | | AccessExclusiveLock
9204 | t | postgres | relation | 16511 | pgbench_accounts_3 | | AccessExclusiveLock
9204 | t | postgres | relation | 16532 | pgbench_accounts_10 | | AccessExclusiveLock
9204 | t | postgres | relation | 16514 | pgbench_accounts_4 | | AccessExclusiveLock
9204 | t | postgres | relation | 16529 | pgbench_accounts_9 | | AccessExclusiveLock
14942 | t | | virtualxid | | | | ExclusiveLock
14942 | t | rdsadmin | relation | 16394 | 16394 | | AccessShareLock
(14 rows)
- Writer でロックがかかっているテーブルを参照する。
> select* from pgbench_accounts;