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 36288D1B524 for ; Wed, 15 Oct 2003 04:30:06 +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 69547-04 for ; Wed, 15 Oct 2003 01:29:35 -0300 (ADT) Received: from gateway.vault-id.com (unknown [203.115.192.108]) by svr1.postgresql.org (Postfix) with SMTP id 13E1FD1B4F4 for ; Wed, 15 Oct 2003 01:29:28 -0300 (ADT) Received: (qmail 19572 invoked from network); 15 Oct 2003 04:10:15 -0000 Received: from unknown (HELO bytecraft.com.my) (219.93.229.26) by mail.bytecraft.com.my with SMTP; 15 Oct 2003 04:10:15 -0000 Message-ID: <3F8CCC7F.6@bytecraft.com.my> Date: Wed, 15 Oct 2003 12:26:39 +0800 From: Ang Chin Han User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20030225 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Oliver Elphick Cc: =?ISO-8859-15?Q?Szab=F3_P=E9ter?= , pgsql-admin@postgresql.org Subject: Re: Lock! References: <004c01c39175$0f0b3620$2702a8c0@WorkGroup> <1066148873.5581.67.camel@linda.lfix.co.uk> In-Reply-To: <1066148873.5581.67.camel@linda.lfix.co.uk> X-Enigmail-Version: 0.71.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig7451EC7D9C91AC049309A543" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/150 X-Sequence-Number: 10711 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig7451EC7D9C91AC049309A543 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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. --------------enig7451EC7D9C91AC049309A543 Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/jMyCNYbTUIgzwfARAm7UAKCP8QuGDKhWA5WeTCtEW1PkrhqY4gCfSv5p obAe6j8Hpc1OESXl5be9/2E= =MaNL -----END PGP SIGNATURE----- --------------enig7451EC7D9C91AC049309A543--