Re: SELECT FOR UPDATE

Jan Wieck wrote:
> 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.
Alternatively, if transactions are used, User2's changes will be
rolled back and lost. Therefore it is necessary to use SELECT FOR UPDATE
with isolation level READ COMMITTED so that User2 will see and not
overwrite User1's simultaneous changes.

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.

I have used a compromise in my programming: read with SELECT, then reread
with SELECT FOR UPDATE on the first change. This reduces the risk of
locking, though it still leaves the possibility open. This could be
refined by having the application time out if it is left untouched for too
long (user gets a phone call, forgets he has a record open and goes to
lunch).

Can you suggest a better way of handling this problem? It would need to
balance better the risk of locking against the risk of losing interactive
editing.

It would be nice to have a lock timeout, for example:

SET TIMEOUT ON LOCK TO 5

with the default being a long enough time for it not to timeout on
normal transient locks. Then SELECT FOR UPDATE would timeout after the
set period and return an error so that the application could regain
control. However, I don't know how feasible this is.

My ideal would be for SELECT FOR UPDATE to timeout with a message:
"table t primary key k locked by backend with PID ppppp"
(using oid if there is no primary key).

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For God hath not appointed us to wrath, but to obtain
salvation by our Lord Jesus Christ, Who died for us,
that, whether we wake or sleep, we should live
together with him."
I Thessalonians 5:9,10