Re: SELECT FOR UPDATE

Thank you for the considerate reply. I have been trying to figure
this out for the last day or two.

I still wouldn't go near SELECT...FOR UPDATE with a ten-foot pole
anymore. It acquires a ROW SHARE MODE lock, and I cannot quite see
how or why that works. I'm trying out SERIALIZABLE, but I think I'll
just manually implement ROW EXCLUSIVE locks & then use the lock table.
I can see that's not app. level transactions, but it would seem to
require more overhead than app level transactions (because of the need
for the lock table).

glenebob(at)nwlink(dot)com ("Glen Parker") wrote in message news:<000001c12f2c$83de3950$0b01a8c0(at)saturn>...
> > On 26 Aug 2001 13:50:16 -0700, Cody <ok(at)mochamail(dot)com> wrote:
> > > I just finished reading Bruce M's book, so this thread confuses me,
> > > esp. Jan's posts. I take full heed of the need for application level
> > > user/thread management, but I was interested in using a parallel
> > > set-up in PG (however redundant that might be). Now that Jan has
> > > discounted "SELECT...FOR UPDATE," is the best alternative using a
> > > central locking table (perhaps in conjunction with LISTEN & NOTIFY)?
>
> It certainly does not discount SELECT...FOR UPDATE ("SFU"). You need some
> way to implement a mutex of sorts at the DB level, in order to insert new
> lock records into the lock table, and this is where SFU comes into play.
> But ANY long running DB level transaction is generally a bad thing.
>
> > > Ironically, anyone who suggested using application level transactions
> > > would be torn apart at any of the places I've worked at--but that
>
> This also is definately not app level transactions. I've implemented a
> lock-table system on a non-transactional database (Paradox) as well, and
> it's not a pretty thing :-) Generally two DB transactions take place to
> effect a checkout/checkin cycle, but what happens in between those two
> operations is completely outside the scope of any kind of transactioning.
>
> > > seems to be the gist of this thread. I cannot see a way to avoid
> > > deadlocks without an application level transaction component, since
> > > the central locking table idea would similarily lock the record
> > > forever if the first transaction failed to COMMIT or ROLLBACK.
>
> If the first transaction fails, it is no different than any other
> transaction failing to end in a timely manor: problems :-) This isn't a
> special case, the database can't ever completely compensate for a
> mis-behaved application, since it can't possibly know how the application is
> *intended* to work.
>
> Provided the app(s) are well-behaved, the common problem would be where the
> second transaction (either an update/unlock or abandon/unlock) never
> happens. As I and others have mentioned, this can be handled by including
> some sort of timeout field in the lock table, a periodic process to clean
> stale lock records from the database, and a tool to explicitly remove locks
> that can be run by a privileged user. In my experience, with a properly
> designed timeout system, stale locks rarely get in the way; with reliable
> client-side software, they don't even occur very often.
>
> Glen Parker
> glenebob(at)nwlink(dot)com