How to Detect Query Which Holds the Lock in Postgres

PostgreSQL find locks including the table name

Try this :

select nspname,relname,l.* from pg_locks l join pg_class c on 
 (relation=c.oid) join pg_namespace nsp on (c.relnamespace=nsp.oid) where 
  pid in (select procpid from pg_stat_activity where 
  datname=current_database() and current_query!=current_query())

How can I find out what is holding a PgLock advisory lock in the Postgres database?

Here’s how to go from a PgLock#lock! call to the actual lock in the database to get more info about what’s holding it:

Find the lock keys that PgLock is using for your lock name. This comes in two halves (the & 0xFFFF_FFFF is necessary because PgLock works with these numbers as signed ints, but Postgres treats them as unsigned):
- The first one is 2147483648. This is simply PG_LOCK_SPACE & 0xFFFF_FFFF from pg_lock.rb.
- The second one can be obtained by replacing your lock name in the following:
```
PgLock.new(name: '').send(:key, "my_lock_name") & 0xFFFF_FFFF
```
Run this query in Postgres to find your key in the pg_locks table, replacing the objid with the one from the key you got above:
```
SELECT *
FROM pg_locks
WHERE locktype = 'advisory' AND
  classid = 2147483648 AND -- First key, the static PG_LOCK_SPACE one
  objid = 2928511612 AND -- Second key, based on your lock name
  objsubid = 2; -- key is int pair, not single bigint
```
This will show information about any active locks being held on this key. In particular the pid column is the posgres server pid of the connection holding the lock.
You can get more information about what the connection holding the lock is doing from pg_stat_activity:
```
SELECT * FROM pg_stat_activity WHERE pid = PID;
```
In extremis you can terminate the connection and force the lock to release with:
```
SELECT pg_terminate_backend(PID);
```

Does Postgres lock all rows in a query atomically, even across different tables via JOIN?

The query will lock the rows one after the other as they are selected. The exact order will depend on the execution plan. Perhaps you can add FOR UPDATE OF table_name to lock rows only in the table where you need them locked.

I have two more ideas:

rewrite the query so that it locks the rows in a certain order:

WITH b AS MATERIALIZED (
   SELECT id, table_a_id
   FROM tableb
   WHERE id = 42
   FOR NO KEY UPDATE
)
SELECT b.id
FROM tablea
WHERE EXISTS (SELECT 1 FROM b
              WHERE tablea.id = b.table_a_id)
ORDER BY tablea.id
FOR NO KEY UPDATE;

Performance may not be as good, but if everybody selects like that, you won't get a deadlock.

lock the tables:
```
LOCK TABLE tablea, tableb IN EXCLUSIVE MODE;
```
That lock will prevent concurrent row locks and data modifications, so you will be safe from a deadlock.
Only do that as a last-ditch effort, and don't do it too often. If you frequently take high table locks like that, you keep autovacuum from running and endanger the health of your database.

How to release possible Postgres row locks?

What version of PostgreSQL are you running? The following assumes 8.1.8 or later (it may apply to earlier versions too, I don't know).

I presume that you mean that phpPgAdmin timed out -- the PostgreSQL backend will take as long as it takes to complete a query/update. In that case, it's possible that the original session is still alive and the UPDATE query is still running. I suggest running the following query (taken from chapter 24 of the PostgreSQL docs) on the machine that hosts the PostgreSQL server process, to see whether the session is still alive:

ps auxwww|grep ^postgres

Several rows should appear: 1 for the postmaster master process, and 1 each for "writer", "stats buffer", and "stats collector" processes. Any remaining lines are for processes serving DB connections. These lines will contain the username and database name.

Hopefully, from that you can see whether the session you performed the original UPDATE in is still hanging around. Although in theory you could find more detailed info by SELECTing from the system view pg_stat_activity, by default PostgreSQL is not set up to populate the most useful fields (such as current_query and query_start). See chapter 24 for how to enable this in the future.

If you see the session is still there, kill it. You will need to be logged in as the user running the process (usually postgres) or root to do so -- if you don't run the server yourself, get your DBA to do this for you.

One more thing: for updating rows in a table, PostgreSQL avoids using locks. Instead, it allows every writing transaction to create a new "version" of the DB, which becomes the "current version" when the transaction is committed, provided it doesn't conflict with updates made in the meantime by other transactions. So I suspect the "hanging" you're seeing is caused by something else -- though what, I'm not sure. (Have you checked the obvious things, like whether the disk partition containing the DB is full?)

Postgres long-running transaction holding lock on parent partitioned table

Creating a partition with CREATE TABLE ... PARTITION OF ... requires an ACCESS EXCLUSIVE lock on the partitioned table, which will conflict with all access to the partitioned table.

On the other hand, inserting into the partition requires an ACCESS SHARE lock on the partitioned table while the insert statement is being planned. That causes a lock conflict.

I see two ways out:

Create new partitions in two steps:

CREATE TABLE inventory_items_42 (
   LIKE inventory_items INCLUDING DEFAULTS INCLUDING CONSTRAINTS
);
ALTER TABLE inventory_items
   ATTACH PARTITION inventory_items_42 FOR VALUES IN (42);

That requires only a SHARE UPDATE EXCLUSIVE lock on the partitioned table (from PostgreSQL v12 on), which is compatible with concurrent inserts.

Use a server prepared statement for the INSERT into the partition and make sure you prepare the statement before you start long running transaction that loads the data. You can use PostgreSQL's PREPARE and EXECUTE statements for that or use your API's facilities.