Re: Locking & concurrency - best practices

On Jan 15, 2008 12:03 AM, Adam Rich <adam(dot)r(at)indigodynamic(dot)com> wrote:
> > 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.

I think you have it backwards...you are considering using advisory
locks as row level locks. Advisory locks do not get released at the
end of the transaction so you have to be little careful with them,
particularly in light of your neglectful developers comment. Advisory
locks also stack, which is something to be careful of.

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

You are simply having to play the hand you dealt yourself with that
design. I don't think having to lock a record before writing to it is
all that terrible, but I understand your perspective. You have a few
general strategies to look at to prevent having to do this:

*) push insert into data table to a function (this is still a
cooperative method)
*) write a trigger on data table that acquires the lock on parent for
insert (or possibly delete), or cache parent status in parent table
via trigger
*) rethink your table design so that parent status is run through the
parent table, forcing a lock
*) write a rule, although I don't advise this
*) kick your developers until they lock records properly

merlin