Skip site navigation (1) Skip section navigation (2)

Re: Question Regarding Locks

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Terry Lee Tucker <terry(at)esc1(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org
Subject: Re: Question Regarding Locks
Date: 2004-10-27 22:44:29
Message-ID: 25524.1098917069@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
Terry Lee Tucker <terry(at)esc1(dot)com> writes:
> I would like to be able to provide feedback to the user when they
> select a row for update (using SELECT FOR UPDATE). At present, if the
> row is being accessed (with SELECT FOR UPDATE) by another user, the
> application just sits there waiting.

To me, this says that you're already off on the wrong foot.

You don't ever want your client application holding locks while a
human user edits text, drinks coffee, goes out to lunch, or whatever.
A better design is to fetch the data without locking it, allow the
user to edit as he sees fit, and then when he clicks "save" you do
something like

	begin;
	select row for update;
	if [ row has not changed since you originally pulled it ] then
		update row with changed values;
		commit;
	else
		abort;
		notify user of conflicts
		let user edit new data to resolve conflicts and try again
	fi

In this design the row lock is only held for milliseconds.

You need to provide some code to let the user merge what he did with the
prior changes, so that he doesn't have to start over from scratch in the
failure case.  What "merge" means requires some business-logic knowledge
so I can't help you there, but this way you are spending your effort on
something that actually helps the user, rather than just tells him he
has to wait.  Performance will be much better too --- long-lasting
transactions are nasty for all sorts of reasons.

BTW, a handy proxy for "row has not changed" is to see if its XMIN
system column is still the same as before.  If so, no transaction has
committed an update to it.  (This may or may not help much, since you're
probably going to end up groveling over all the fields anyway in the
"notify user" part, but it's a cool hack if you can use it.)

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: go_speedyDate: 2004-10-28 06:45:59
Subject: serial = autoincrement = without gaps ??
Previous:From: Terry Lee TuckerDate: 2004-10-27 21:17:30
Subject: Question Regarding Locks

pgsql-general by date

Next:From: Robby RussellDate: 2004-10-27 22:45:14
Subject: Re: interval to seconds conversion. How?
Previous:From: Guy FraserDate: 2004-10-27 22:40:32
Subject: Re: '1 year' = '360 days' ????

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group