CODEWITHSUNDEEP
postgresqllocking
Nils Bokermann
Nils Bokermann

Reputation: 131

postgresql remove stale lock

After a system crash my Postgresql database does have a lock on a row.

The pg_locks table contains a lot of rows without a pid. i.e.

select locktype,database,relation,virtualtransaction, pid,mode,granted from pg_locks p1;

locktype    | database | relation | virtualtransaction |  pid  |       mode       | granted 
---------------+----------+----------+--------------------+-------+------------------+---------
 relation      |    16408 |    31459 | -1/40059           |       | AccessShareLock  | t
 relation      |    16408 |    31459 | -1/40059           |       | RowExclusiveLock | t
 relation      |    16408 |    31022 | -1/40060           |       | AccessShareLock  | t
 transactionid |          |          | -1/40060           |       | ExclusiveLock    | t
 relation      |    16408 |    31485 | -1/40060           |       | AccessShareLock  | t

How do I get the transaction 40060 killed and the locks removed?

Upvotes: 6

Views: 4132

Answers (1)

Nils Bokermann
Nils Bokermann

Reputation: 131

Ok, solution found by myself:

  1. Find the gid to the transaction (i.e. 40060 in the case above) by select * from pg_prepared_xacts where transaction = 40060;
  2. Find an awful long gid.
  3. ROLLBACK PREPARED gid;

This will clear the locks.

Upvotes: 6

Related Questions