Re: SELECT FOR UPDATE - Mailing list pgsql-general

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@nwlink.com ("Glen Parker") wrote in message news:<000001c12f2c$83de3950$0b01a8c0@saturn>...
> > On 26 Aug 2001 13:50:16 -0700, Cody <ok@mochamail.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@nwlink.com