pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLock!
6+ messages / 6 participants
[nested] [flat]
* Lock!
@ 2003-10-13 10:30 Szabó Péter <matyee@westel900.net>
2003-10-14 16:27 ` Re: Lock! Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Szabó Péter @ 2003-10-13 10:30 UTC (permalink / raw)
To: pgsql-admin
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?
Matyee
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Lock!
2003-10-13 10:30 Lock! Szabó Péter <matyee@westel900.net>
@ 2003-10-14 16:27 ` Oliver Elphick <olly@lfix.co.uk>
2003-10-14 19:00 ` Re: Lock! Christopher Browne <cbbrowne@acm.org>
2003-10-15 04:26 ` Re: Lock! Ang Chin Han <angch@bytecraft.com.my>
0 siblings, 2 replies; 6+ messages in thread
From: Oliver Elphick @ 2003-10-14 16:27 UTC (permalink / raw)
To: Szabó Péter <matyee@westel900.net>; +Cc: pgsql-admin
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.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I delight to do thy will, O my God; yea, thy law is
within my heart." Psalms 40:8
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Lock!
2003-10-13 10:30 Lock! Szabó Péter <matyee@westel900.net>
2003-10-14 16:27 ` Re: Lock! Oliver Elphick <olly@lfix.co.uk>
@ 2003-10-14 19:00 ` Christopher Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 6+ messages in thread
From: Christopher Browne @ 2003-10-14 19:00 UTC (permalink / raw)
To: pgsql-admin
In an attempt to throw the authorities off his trail, olly@lfix.co.uk (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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Lock!
2003-10-13 10:30 Lock! Szabó Péter <matyee@westel900.net>
2003-10-14 16:27 ` Re: Lock! Oliver Elphick <olly@lfix.co.uk>
@ 2003-10-15 04:26 ` Ang Chin Han <angch@bytecraft.com.my>
2003-10-15 15:26 ` Re: Lock! Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Ang Chin Han @ 2003-10-15 04:26 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Szabó Péter <matyee@westel900.net>; pgsql-admin
Oliver Elphick wrote:
> 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).
Unless you set STATEMENT_TIMEOUT:
http://www.postgresql.org/docs/7.3/static/runtime-config.html#RUNTIME-CONFIG-GENERAL
In other words, try:
SET STATEMENT_TIMEOUT=50;
SELECT * FROM foo WHERE foo_id = 1 FOR UPDATE;
That means if another process is locking foo_id = 1 row for more than 50
millisecond after you try to SELECT it FOR UPDATE, you'll get an
error/rollback, and you can then assume it's being used.
Just remember to reset STATEMENT_TIMEOUT to zero or your default before
any slow queries in that transaction.
There was some discussion about emulating (IIRC) Oracle's SELECT .. FOR
UPDATE NOWAIT somewhere in the Postgresql archives. Look them up for
more details.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Lock!
2003-10-13 10:30 Lock! Szabó Péter <matyee@westel900.net>
2003-10-14 16:27 ` Re: Lock! Oliver Elphick <olly@lfix.co.uk>
2003-10-15 04:26 ` Re: Lock! Ang Chin Han <angch@bytecraft.com.my>
@ 2003-10-15 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-10-15 15:26 UTC (permalink / raw)
To: Ang Chin Han <angch@bytecraft.com.my>; +Cc: Oliver Elphick <olly@lfix.co.uk>; Szabó Péter <matyee@westel900.net>; pgsql-admin
Ang Chin Han <angch@bytecraft.com.my> writes:
> Just remember to reset STATEMENT_TIMEOUT to zero or your default before
> any slow queries in that transaction.
Also, you can use "SET LOCAL ..." to constrain the effects of the SET
command; this is probably safer than trying to remember to reset the
variable when you're done.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Lock!
@ 2003-10-13 10:33 Szabó Péter <matyee@mail.alba.hu>
0 siblings, 0 replies; 6+ messages in thread
From: Szabó Péter @ 2003-10-13 10:33 UTC (permalink / raw)
To: pgsql-admin
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?
Matyee
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-10-15 15:26 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-13 10:30 Lock! Szabó Péter <matyee@westel900.net>
2003-10-14 16:27 ` Oliver Elphick <olly@lfix.co.uk>
2003-10-14 19:00 ` Christopher Browne <cbbrowne@acm.org>
2003-10-15 04:26 ` Ang Chin Han <angch@bytecraft.com.my>
2003-10-15 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-13 10:33 Lock! Szabó Péter <matyee@mail.alba.hu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox