rihad wrote:
Given this type query:
UPDATE bw_pool
SET user_id=?
WHERE bw_id=
(SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
RETURNING bw_id
Can you use a SERIALIZABLE transaction and avoid the explicit lock?
Not really. Since LOCKing bw_pool backs up later firewall manipulation
(of which there's one) I'm not really prepared to restart transactions
due to deadlocks. It's easier for me to prevent deadlocks altogether by
carefully stacking queries according to the level of lock
restrictiveness, albeit at a price that the whole transaction will be
single threaded, even parts of it that don't need it. I was indeed
willing to exclusively lock only as little code as possible
(fine-grained locking), but neglected the importance of the locking-type
order.
