Обсуждение: BUG #6548: NOWAIT does not work in Select statement if table is locked using "Lock " command

The following bug has been logged on the website:

Bug reference:      6548
Logged by:          Neeraj Punmiya
Email address:      neeraj.punmiya@securetogether.com
PostgreSQL version: 9.1.3
Operating system:   CentOS 6.2 , Windows 7
Description:=20=20=20=20=20=20=20=20

Steps to simulate problem
=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

Create Table Test (x integer,y integer);

Insert Into Test Values(1,100);
Insert Into Test Values(2,200);

First database session:
---------------------------
start transaction;
Lock table Test;

Second database session
-----------------------
start transaction;

Select y from Test where x=3D1 for update nowait;

select query waits until first session releases table lock.=20
Nowait does not have any effect in select statement.=20

<neeraj.punmiya@securetogether.com> wrote:
=20
> Create Table Test (x integer,y integer);
>=20
> Insert Into Test Values(1,100);
> Insert Into Test Values(2,200);
>=20
> First database session:
> ---------------------------
> start transaction;
> Lock table Test;
>=20
> Second database session
> -----------------------
> start transaction;
>=20
> Select y from Test where x=3D1 for update nowait;
>=20
> select query waits until first session releases table lock.=20
> Nowait does not have any effect in select statement.=20
=20
That is all functioning as designed and documented.  From the docs
at:
=20
http://www.postgresql.org/docs/9.1/interactive/sql-select.html#SQL-FOR-UPDA=
TE-SHARE
=20
| Note that NOWAIT applies only to the row-level lock(s) * the
| required ROW SHARE table-level lock is still taken in the ordinary
| way (see Chapter 13). You can use LOCK with the NOWAIT option
| first, if you need to acquire the table-level lock without waiting.
=20
This is not a bug.
=20
-Kevin

neeraj.punmiya@securetogether.com writes:
> First database session:
> ---------------------------
> Lock table Test;

> Second database session
> -----------------------
> Select y from Test where x=1 for update nowait;

> select query waits until first session releases table lock.

This isn't a bug.  FOR UPDATE NOWAIT is only meant to specify not
waiting for row-level locks on the rows you're trying to lock for
update, as per the explanation in the SELECT reference page:

    To prevent the operation from waiting for other transactions to
    commit, use the NOWAIT option. With NOWAIT, the statement
    reports an error, rather than waiting, if a selected row cannot
    be locked immediately. Note that NOWAIT applies only to the
    row-level lock(s) - the required ROW SHARE table-level lock is
    still taken in the ordinary way

If it meant "fail on any lock anywhere" the behavior would be too
unpredictable.  You might consider doing "lock table Test in row share
mode nowait" in the second transaction, if you want it to fail on
table-level locks too.  Another solution is to just use a short
statement_timeout rather than NOWAIT clauses.

            regards, tom lane