2

We had a task which was stuck so I used SELECT pg_cancel_backend(<pid of the process>) to kill the task. However the lock that the task hold didn't go away and now one of my table is locked.

I am trying to run a COPY command on the table and it gets stuck with wait_event_type = Lock.

System being Used:

Server: AWS RDS db.m4.xlarge
DB: PostgreSQL 9.6.6

select * from pg_locks;

locktype      | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted | fastpath
--------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
relation      |    16390 |    19694 |      |       |            |               |         |       |          | -1/24019935        |       | RowExclusiveLock | t       | f
transactionid |          |          |      |       |            |      24019935 |         |       |          | -1/24019935        |       | ExclusiveLock    | t       | f


select * from pg_prepared_xacts where transaction=24019935;

transaction |             gid              |           prepared           |  owner   | database
------------+------------------------------+------------------------------+----------+----------
24019935    | PGDIRTY:dirty_state::9994733 | 2018-06-27 13:40:12.88981-08 | postgres | pg


I already tried doing the step suggested in Remove locks without a pid in postgres, but it doesn't show any pgid.

I also tried rebooting my database but the lock still persists.

Can anyone help me figure out how to get rid of this lock ?

Thanks

Shimul
  • 81
  • 7
  • I don't believe that the lock remains after rebooting your server. If it does, you have some daemon process that created a new lock right after startup –  Jul 27 '18 at 11:13
  • In that case the `transactionid` should have been different. – Shimul Jul 27 '18 at 11:17
  • There is no way a lock can survive a reboot of the database server –  Jul 27 '18 at 11:21
  • That's what my understanding is as well. But I rebooted RDS 3 times already and the query I mentioned above still results same. – Shimul Jul 27 '18 at 11:30

1 Answers1

6

Just in case, any other poor fellow gets stuck in similar situation. Here's what worked finally for me.

PS: I don't know why the lock didn't go away with restart as mentioned in the comments of the question.

ROLLBACK PREPARED (SELECT gid FROM pg_prepared_xacts);

Explanation: For some reason, the prepared transaction was never rolled back properly even after termination of the PID and reboot of the RDS server (maybe RDS does some funny reboot and not full db restart ? ). After I rolled back the prepared transaction manually, the transaction was finally removed, and lock released.

The suggestion mentioned by Thomas C. G. de Vilhena in Postgresql DROP TABLE doesn't work helped me fix it finally.

Shimul
  • 81
  • 7