Skip site navigation (1) Skip section navigation (2)

Re: SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5

From: Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Date: 2009-05-11 19:24:41
Message-ID: 712F0D2A-A57A-40D6-BCAD-B64BC9DDDD07@cybertec.at (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-hackers
hello tom ...

the reason for SELECT FOR UPDATE is very simple:
this is the typical lock obtained by basically every business  
application if written properly (updating a product, whatever).
the problem with NOWAIT basically is that if a small transaction holds  
a a lock for a subsecond, you will already lose your transaction  
because it does not wait at all (which is exactly what you want in  
some cases). however, in many cases you want to compromise on wait  
forever vs. die instantly.
depending on the code path we could decide how long to wait for which  
operation. this makes sense as we would only fire 1 statement instead  
of 3 (set, run, set back).

i agree that a GUC is definitely an option.
however, i would say that adding an extension to SELECT FOR UPDATE,  
UPDATE and DELETE would make more sense form a usability point of view  
(just my 0.02 cents).

if hackers' decides to go for a GUC, we are fine as well and we will  
add it to 8.5.

	many thanks,

		hans



On May 11, 2009, at 4:46 PM, Tom Lane wrote:

> Hans-Juergen Schoenig <postgres(at)cybertec(dot)at> writes:
>> i would like to propose an extension to our SELECT FOR UPDATE  
>> mechanism.
>> especially in web applications it can be extremely useful to have the
>> chance to terminate a lock after a given timeframe.
>
> I guess my immediate reactions to this are:
>
> 1. Why SELECT FOR UPDATE in particular, and not other sorts of locks?
>
> 2. That "clear and easy to use" oracle syntax sucks.  You do not want
> to be embedding lock timeout constants in your application queries.
> When you move to a new server and the appropriate timeout changes,
> do you want to be trying to update your clients for that?
>
> What I think has been proposed previously is a GUC variable named
> something like "lock_timeout", which would cause a wait for *any*
> heavyweight lock to abort after such-and-such an interval.  This
> would address your point about not wanting to use an overall
> statement_timeout, and it would be more general than a feature
> that only works for SELECT FOR UPDATE row locks, and it would allow
> decoupling the exact length of the timeout from application query
> logic.
>
> 			regards, tom lane
>
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: www.postgresql-support.de


In response to

Responses

pgsql-hackers by date

Next:From: Boszormenyi ZoltanDate: 2009-05-11 19:48:31
Subject: Re: SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Previous:From: Greg StarkDate: 2009-05-11 18:40:38
Subject: Re: SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group