> On 26 Aug 2001 13:50:16 -0700, Cody <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
