In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Oliver Elphick) transmitted: > On Mon, 2003-10-13 at 11:30, Szabó Péter wrote: >> Hi! >> >> I have a lock problem. If i lock a record with SELECT FOR UPDATE, than >> i try to lock again, the process just wait until the record free. But >> i have to send a message to the user like 'The record is locked, try >> later.'. But i can't. How can i do this? > > You can't. > > Quote from the manual: > So long as no deadlock situation is detected, a transaction > seeking either a table-level or row-level lock will wait > indefinitely for conflicting locks to be released. This means it > is a bad idea for applications to hold transactions open for > long periods of time (e.g., while waiting for user input). > > You need to rethink your application. > > What I have done is to read the data with SELECT. Just before changing > it I do SELECT FOR UPDATE and tell the user if anything he is changing > has changed since he read it in. If there has been no change, I go > ahead with the UPDATE. This means that records are only locked for a > very short time. Now, instead of the possibility of being locked out > for a long time, there is a possibility of having to throw away some > editing, but I estimate that to be less of a cost overall.
Another strategy that some of our folks have been trying out is that of "optimistic locking." It's an in-the-application scheme, which is arguably not totally ideal, but it has the not-inconsiderable merit that its cost is _very_ low for the common case where there is no conflict. General idea: You start by SELECTing a lock field on the data you want to update. SELECT STUFF, LOCK_FIELD FROM SOME_RELATION; When you actually do the update, you do it as: UPDATE SOME_RELATION SET THIS='This', THAT='That', LOCK_FIELD=txn_id WHERE [normal criteria] AND LOCK_FIELD='Value_I_Found_Earlier'; If the record has been updated, then LOCK_FIELD will have a different value, and this transaction fails; you'll have to do something to recover, probably a ROLLBACK, and perhaps a retry. On the other hand, if the record _hasn't_ been touched by anyone else, then this change will go through, and there wasn't any costly locking done in the DBMS. It's not new; it was presented in the IEEE Transactions on Software Engineering back in '91, and that might not be its genesis... <http://www.computer.org/tse/ts1991/e0712abs.htm> There's discussion of it in a Squeak Wiki... <http://minnow.cc.gatech.edu/squeak/2634> It seems to have become publicly popular in the Java world, presumably due to them finding it expensive to do pessimistic locking (e.g. - as in starting out with the SELECT FOR UPDATE). -- output = ("aa454" "@" "freenet.carleton.ca") http://cbbrowne.com/info/linux.html debugging, v: Removing the needles from the haystack. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]