RE: [GENERAL] Re: SELECT FOR UPDATE
> 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
[GENERAL] Re: SELECT FOR UPDATE
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)? > Ironically, anyone who suggested using application level transactions > would be torn apart at any of the places I've worked at--but that > 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. > > What is the saying: To the beginner, there are many options. To the > wise, there are few. It seems to me that SELECT ... FOR UPDATE is not the way to go if it is possible that the selected record may be held for any length of time. The best way around this is going to depend on the application and resolution of conflicts will probably take place in the application. For instance, say you are storing web pages in the database, and you want a number of developers to be able to get pages from the database look at them, and possibly make changes and update the record. Your application might have some kind of sliding time-out scale from a 3-minute "I see a typo" to a 30-minute "This really needs work". It would probably also remind the author before the time-out or possibly automatically save and re-acquire the lock if possible. So, the lock table would need a time-out field, and might also include a queue of people waiting for the record. This is just how I was thinking of it, and again I think it will depend on the application. At some point, you're not making a database, you're creating a cvs. Though a hybrid could be good ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [GENERAL] Re: SELECT FOR UPDATE
I prefer the way Notes (for example) handles it. All records/documents/views are in read-only mode until the user indicates they actually want to edit. They then exclusively lock that record for editing, with optional timeouts (in case their workstation crashes or whatever). This turns out to work well in many situations where you realise the number of times you want to edit compared to the number of times you want to view, is quite small. Stops users having to worry whether anyone else is editing the record at the same time - the system simply won't let them - no loss of data. Andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: SELECT FOR UPDATE
> >But the question itself tells that you're about to implement > >a major design error in your application. Holding database > >locks during user interaction IS A BAD THING. Never, never > >ever do it that way. And anybody telling you something > >different is an overpaid idiot. > > I can see arguments to support this view, but consider this classic > scenario: > > User1: Read data into an interactive program > User1: Start to make changes > User2: Read data into an interactive program > User2: Start to make changes > User1: Save changes > User2: Save changes > > With no locks, both users will have the same original data, but User1's > changes will not be seen by User2 and will therefore be lost. This is an unavoidable consequence of any multi-user system. If two (or more) users can modify the same record, they have to understand that someone else may be modifying that record. This shouldn't be a problem though because UPDATE statements only affect the fields that the user wants to update. User2 will only overwrite changes made by User1 if they both wanted those changes made. There are a few instances where changes might affect how User2 might edit the record, but the only ones I can think of are cumulative changes (for instance, an account balance). Those *do* need to be updated in some sort of critical section, such as that provided by SELECT FOR UPDATE, but I don't think they require attention from User2 unless some special situation occurs (the account would now be overdrawn). > Alternatively, if transactions are used, User2's changes will be > rolled back and lost. Why are they lost? The client should check to see if the transaction succeeds. If not, then it handles the situation in whatever manner makes the best sense. I would think handling an error on UPDATE is much more graceful for both the users and the system than locking the record while a user (who could simply step away from his computer for more coffee) holds the lock indefinitely. > One way out is to do SELECT when reading and a SELECT FOR UPDATE just > before saving; if the row has changed, the user is warned and must > redo his changes -- but this could lead to the loss of a lot of editing. Also, a lengthy WHERE clause in the UPDATE can do the same. In other words, don't just use the primary key but all the fields. If the record doesn't EXACTLY match the WHERE statement, it can't be updated. But a failure to update doesn't mean a loss to editing. It is the client's responsibility to keep that data for as long as the user wants it. Let the client say "Record couldn't be updated, some information has changed. Here is a summary of the changes: xxx. Press Ok to continue, Cancel to modify your changes." > My ideal would be for SELECT FOR UPDATE to timeout with a message: >"table t primary key k locked by backend with PID p" > (using oid if there is no primary key). Personally as a user I'd rather handle the conflict resolution than be locked out of records entirely "Another user is modifying this record. Please twiddle your thumbs and try again in a few minutes" Ugh. I can only imagine how badly my users would badmouth me if they got a message like that above... Greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
