Re: [GENERAL] Locking & concurrency - best practices

From: Adam Rich <adam.r(at)indigodynamic.com>
Date: Tue Jan 15 2008 - 00:03:05 EST

> 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.

---------------------------(end of broadcast)---------------------------

TIP 5: don't forget to increase your free space map settings Received on Tue Jan 15 01:07:05 2008