X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D612DD1B57E for ; Tue, 14 Oct 2003 16:30:11 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 35238-08 for ; Tue, 14 Oct 2003 13:29:41 -0300 (ADT) Received: from anchor-post-37.mail.demon.net (anchor-post-37.mail.demon.net [194.217.242.87]) by svr1.postgresql.org (Postfix) with ESMTP id BD2D0D1B533 for ; Tue, 14 Oct 2003 13:27:57 -0300 (ADT) Received: from lfix.demon.co.uk ([80.177.205.209] helo=cerberus.lfix.co.uk) by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1) id 1A9S1d-000PEB-0b; Tue, 14 Oct 2003 17:27:57 +0100 Received: from linda.lfix.co.uk ([192.168.1.1]) by cerberus.lfix.co.uk with esmtp (Exim 4.22) id 1A9S1Z-00058I-Jg; Tue, 14 Oct 2003 17:27:53 +0100 Subject: Re: Lock! From: Oliver Elphick To: =?ISO-8859-1?Q?Szab=F3_P=E9ter?= Cc: pgsql-admin@postgresql.org In-Reply-To: <004c01c39175$0f0b3620$2702a8c0@WorkGroup> References: <004c01c39175$0f0b3620$2702a8c0@WorkGroup> Content-Type: text/plain; charset=iso-8859-15 Organization: LFIX Limited Message-Id: <1066148873.5581.67.camel@linda.lfix.co.uk> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Tue, 14 Oct 2003 17:27:53 +0100 Content-Transfer-Encoding: quoted-printable X-LFIX-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/140 X-Sequence-Number: 10701 On Mon, 2003-10-13 at 11:30, Szab=F3 P=E9ter wrote: > Hi! >=20=20 > 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). =20=20=20=20=20=20=20=20 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. --=20 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 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D "I delight to do thy will, O my God; yea, thy law is=20 within my heart." Psalms 40:8=20