- Denish Patel
- 8th November 2010
If you’ve ever gotten a page about database connections are blocked, or phone call or email from an annoyed user whose transaction just won’t go through, or from a developer who can’t understand why application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked.
postgres=# insert into testlock values (‘lock’,’me’);
INSERT 0 1 postgres=# insert into testlock values (‘find’,’me’);
INSERT 0 1
postgres=# select * from testlock;
foo | bar
find | me
postgres=# update testlock set foo='hang' where foo='lock';
AndSession 2, try to update same row:
postgres=# update testlock set foo='escapeme' where foo='lock';
This statement will hang, blocked by the lock that Session 1 is holding on the row.
-[ RECORD 1 ]
locked_mode | ShareLock
blocked_pid | 9468
blocked_user | postgres
blocked_statement | update testlock set foo='escapeme' where foo='lock';
blocked_query_age | 00:10:28.98475
blocking_pid | 8056
blocking_user | postgres
blocking_statement | IDLE in transaction
blocking_query_age | 00:10:24.403767
blocking_xact_age | 00:10:58.529881
Let me know if you have suggestions to make it more better.
- PSQL Helper: Managing Connections and Simplifying Queries
- Vacuum Those MVs!
- Working With Repmgr: Using Other 3rd Party Tools for Setting up a Standby
- Working with Amazon Aurora PostgreSQL: dag, standby rebooted again!
- Working with Amazon Aurora PostgreSQL: what happened to the stats?
- How to set application_name for psql command line utility?
- Is there a limit on number of partitions handled by Postgres?
- Postgres 11 partitioning
- Audit logging with Postgres partitioning
- Audit logging using JSONB in Postgres