postgresql: delete all locks

Question

question: there is a table with over 9000 rows. It must be cleaned but without any locks (table in active using). I tried to use pg_advisory_unlock_all, but no result.

select pg_advisory_unlock_all();
start transaction();
delete from table where id='1';
DELETE 1

start transaction();
delete from table where id='1';
(waiting for finish first transaction)

pg_advisory_unlock will release a previously-acquired exclusive advisory lock — McNets
– McNets, Commented Feb 25, 2017 at 23:44
is it possible at all to toggle off them, or i must set up right locks? — Максим Стукало
– Максим Стукало, Commented Feb 25, 2017 at 23:51
in my task i can't lock table, maybe there is any other way or i can only deacrease transaction locks? — Максим Стукало
– Максим Стукало, Commented Feb 26, 2017 at 0:57

Laurenz Albe · Accepted Answer · 2017-02-27 08:35:04Z

1

There is no way to delete data from a table without locking the rows you want to delete.

That shouldn't be a problem as long as concurrent access doesn't try to modify these rows or insert new ones with id = '1', because writers never block readers and vice versa in PostgreSQL.

If concurrent transactions keep modifying the rows you want to delete, that's a little funny (why would you want to delete data you need?). You'd have to wait for the exclusive locks, and you might well run into deadlocks. In that case, it might be best to lock the whole table with the LOCK statement before you start. Deleting from a table that small should then only take a very short time.

answered Feb 27, 2017 at 8:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

postgresql: delete all locks

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related