X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BF89DD1B198 for ; Tue, 29 Jun 2004 17:26:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96876-05 for ; Tue, 29 Jun 2004 20:26:24 +0000 (GMT) Received: from cmailg1.svr.pol.co.uk (cmailg1.svr.pol.co.uk [195.92.195.171]) by svr1.postgresql.org (Postfix) with ESMTP id 4F853D1B196 for ; Tue, 29 Jun 2004 17:26:21 -0300 (ADT) Received: from modem-3803.lemur.dialup.pol.co.uk ([217.135.142.219] helo=[192.168.0.100]) by cmailg1.svr.pol.co.uk with esmtp (Exim 4.14) id 1BfPBO-00084s-Gs; Tue, 29 Jun 2004 21:26:22 +0100 Subject: Re: lock timeout patch From: Simon Riggs To: Josh Berkus Cc: pgsql-hackers@postgresql.org In-Reply-To: <200406291036.48477.josh@agliodbs.com> References: <200406291036.48477.josh@agliodbs.com> Content-Type: text/plain Organization: 2nd Quadrant Message-Id: <1088540781.3266.7493.camel@stromboli> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 (1.4.5-7) Date: Tue, 29 Jun 2004 21:26:22 +0100 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200406/982 X-Sequence-Number: 55532 On Tue, 2004-06-29 at 18:36, Josh Berkus wrote: > Tom, > > > I'd accept a mechanism to enforce a timeout at the lock level if you > > could show me a convincing use-case for lock timeouts instead of > > statement timeouts, but I don't believe there is one. I think this > > proposal is a solution in search of a problem. > > Hmmm ... didn't we argue this out with NOWAIT? What did we conclude then? > I'm reluctant to go over old ground repeatedly. > > Let me say for myself that I would use this feature if it existed, but would > not miss it a whole lot if the patch was rejected. Here's the idea: > Can't vouch for the patch, but I can say this would get used... > I have an ... database ... > which requires that the evaluations, sometimes interlocking, of regions be > "closed" simultaneously (in one transaction). This means that during the > closure process, certain kinds of data entry needs to be frozen out. I am > using ... lock timeout functionality for this; bascially, the data > entry waits for 30 seconds, and then tells the user to try again in 10 > minutes. Just implementing this same scenario, using DB2 (...). Of course, if I had MVCC on that application, I could argue that this is not required...is that the basis of the "not required" view? > > I could do the same thing in PostgreSQL using NOWAIT and a loop on the client > side. But the lock timeout is somewhat easier. SQLServer and DB2 support a lock timeout system wide, simple but not granular. Oracle supports the NOWAIT option, even though it supports readers-dont-block locking. I prefer the NOWAIT option as it gives a more detailed handle on the exact statements that you wish to wait, or not. Without NOWAIT, we would need to set lock_timeout = 30 (seconds) Statement level timeout is a different thing entirely, since there are very often statements that need to run for 2-3 hours (even more in some cases), so statement level timeout is set to 10000 (seconds). Best Regards, Simon Riggs