> Advisory locks would work here (better that than table lock), but I
> don't think that's the right approach. Transaction 2 should simply do
> a
> select * from parent_tbl
> where id=1 for update;
>
> at the start of the transaction.
That's actually what I'm doing (just forgot to include it in the
simplified example). What I'm struggling with is that since these locks
aren't enforced in one central place, so I have to run the "for update"
query in every far corner of my code that touches data, whether or not
it reads or writes to parent_tbl. If any of the developers forget
to add it, the data can become corrupted. And since I'm essentially
using row-level locks as advisory locks, I wondered if just using
advisory locks directly would benefit us somehow, in quicker
transactions, CPU/memory overhead, WAL, etc.
In my real application, there are lots of "parent_tbl" and when I try
to "for update" the appropriate ones, I get deadlocks. I know in
theory, I only need to lock things in the same order, everywhere.
But in practice, it seems hard to achieve.