From: Oliver Elphick <olly@lfix.co.uk>
To: Szabó Péter <matyee@westel900.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Lock!
Date: Tue, 14 Oct 2003 17:27:53 +0100
Message-ID: <1066148873.5581.67.camel@linda.lfix.co.uk> (raw)
In-Reply-To: <004c01c39175$0f0b3620$2702a8c0@WorkGroup>
References: <004c01c39175$0f0b3620$2702a8c0@WorkGroup>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: olly@lfix.co.uk, matyee@westel900.net
Subject: Re: Lock!
In-Reply-To: <1066148873.5581.67.camel@linda.lfix.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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