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

Re: BUG #6548: NOWAIT does not work in Select statement if table is locked using "Lock " command

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: neeraj(dot)punmiya(at)securetogether(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6548: NOWAIT does not work in Select statement if table is locked using "Lock " command
Date: 2012-03-21 15:48:24
Message-ID: 1737.1332344904@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
neeraj(dot)punmiya(at)securetogether(dot)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

In response to

pgsql-bugs by date

Next:From: jf.vanderwoudeDate: 2012-03-21 17:12:51
Subject: BUG #6549: Wrong $PATH setting after installation
Previous:From: Kevin GrittnerDate: 2012-03-21 14:54:32
Subject: Re: BUG #6548: NOWAIT does not work in Select statement if table is locked using "Lock " command

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