How to unlock row in Postgres

Question

I've probably killed (-9) somehow process which locked a row in my table. Now whenever I try to:

SELECT * 
FROM my_table 
WHERE id=6 FOR UPDATE NOWAIT;

I get:

ERROR: could not obtain lock on row in relation "my_table"`.

How can I unlock that row?

Qback · Accepted Answer · 2018-12-13 14:31:21Z

1

Ok, I figured it out somehow.

I made select * from pg_locks;,
checked pid similar to that which i killed.
Then I made ps aux | grep suspicious_pid,
i got something like: postgres: q-back my_project ::1(63686) idle in transaction.
then kill suspicious_pid, where suspicious_pid is number of PID of course.

answered Dec 13, 2018 at 14:31

Qback

5,0283 gold badges29 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

It's better to use pg_cancel_backend(pid) or pg_terminate_backend(pid). But the real question is: why do you have connections that start transactions but never commit?

Qback Over a year ago

@a_horse_with_no_name thanks for tip. I had an async worker which locked the row at the start of working and kept it locked until finish download from server and finally commit. I've killed it while it was working and the transaction survived opened. I have no better idea how to explain that..

Collectives™ on Stack Overflow

How to unlock row in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related