Обсуждение: NOWAIT doesn't work

Hello

it is expected behave?

1.session

postgres=# begin;
BEGIN
postgres=# lock oo IN ACCESS EXCLUSIVE MODE;
LOCK TABLE

2. session

postgres=# select * from oo for update nowait;

hangs forever ....

Regards

Pavel Stehule

tested on 9.3

Pavel

2012/10/31 Pavel Stehule <pavel.stehule@gmail.com>:
> Hello
>
> it is expected behave?
>
> 1.session
>
> postgres=# begin;
> BEGIN
> postgres=# lock oo IN ACCESS EXCLUSIVE MODE;
> LOCK TABLE
>
> 2. session
>
> postgres=# select * from oo for update nowait;
>
> hangs forever ....
>
> Regards
>
> Pavel Stehule

On Wednesday, October 31, 2012 02:51:38 PM Pavel Stehule wrote:
> it is expected behave?
> 
> 1.session
> 
> postgres=# begin;
> BEGIN
> postgres=# lock oo IN ACCESS EXCLUSIVE MODE;
> LOCK TABLE
> 
> 2. session
> 
> postgres=# select * from oo for update nowait;
> 
> hangs forever ....

NOWAIT is about row level locks, not about table level locks.

Andres
-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

Hi,

On Wednesday, October 31, 2012 02:51:38 PM Pavel Stehule wrote:
> Hello
> 
> it is expected behave?
> 
> 1.session
> 
> postgres=# begin;
> BEGIN
> postgres=# lock oo IN ACCESS EXCLUSIVE MODE;
> LOCK TABLE
> 
> 2. session
> 
> postgres=# select * from oo for update nowait;
> 
> hangs forever ....

Yes, I think so. From the documentation:

Note that NOWAIT applies only to the row-level lock(s)
(http://www.postgresql.org/docs/9.2/static/sql-select.html)

Greetings,CK

Pavel Stehule escribió:
> Hello
>
> it is expected behave?
>
> 1.session
>
> postgres=# begin;
> BEGIN
> postgres=# lock oo IN ACCESS EXCLUSIVE MODE;
> LOCK TABLE
>
> 2. session
>
> postgres=# select * from oo for update nowait;
>
> hangs forever ....

"select for update nowait" would raise an error if the tuple-level lock
is being held by some other process; but what's making it wait here is
the table-level lock.

Now, is this the right behavior?  I'm not sure.  But I know for certain
that making it behave as you expect is very tricky.  The table lock is
grabbed during parse analysis; we'd have to postpone grabbing the lock
until after we have had the chance to notice that there's a FOR UPDATE
clause for the table with a NOWAIT option attached.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera escribió:

> Now, is this the right behavior?  I'm not sure.  But I know for certain
> that making it behave as you expect is very tricky.  The table lock is
> grabbed during parse analysis; we'd have to postpone grabbing the lock
> until after we have had the chance to notice that there's a FOR UPDATE
> clause for the table with a NOWAIT option attached.

Furthermore you could do it manually: just do a LOCK TABLE NOWAIT in the
second session before the SELECT FOR UPDATE.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

2012/10/31 Alvaro Herrera <alvherre@2ndquadrant.com>:
> Alvaro Herrera escribió:
>
>> Now, is this the right behavior?  I'm not sure.  But I know for certain
>> that making it behave as you expect is very tricky.  The table lock is
>> grabbed during parse analysis; we'd have to postpone grabbing the lock
>> until after we have had the chance to notice that there's a FOR UPDATE
>> clause for the table with a NOWAIT option attached.
>
> Furthermore you could do it manually: just do a LOCK TABLE NOWAIT in the
> second session before the SELECT FOR UPDATE.

I understand now, thank you to all for information

Regards

Pavel

>
> --
> Álvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services

> Now, is this the right behavior?  I'm not sure.  But I know for certain
> that making it behave as you expect is very tricky.  The table lock is
> grabbed during parse analysis; we'd have to postpone grabbing the lock
> until after we have had the chance to notice that there's a FOR UPDATE
> clause for the table with a NOWAIT option attached.

I think our present behavior violates POLS.  Users would logically
expect NOWAIT to work for all types of locks.  If we get LOCK TIMEOUT
working in 9.3, this will apply there as well.

No question that changing that would be a new feature, though.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com