Обсуждение: BUG #1459: Connection hangs when other connection is not committed

The following bug has been logged online:

Bug reference:      1459
Logged by:          Rainer Frey
Email address:      rainer.frey@inxmail.de
PostgreSQL version: 8.0.1
Operating system:   Redhat Linux 9, Kernel 2.4.20-8, AMD Sempron 2500+, 1GB
RAM
Description:        Connection hangs when other connection is not committed
Details:

There seems to be a locking problem when not using autocommit. I came across
this in a relatively complex Java application, but could reproduce it with
the following scenario: (user test has priviledge to create db)
createdb -U test -W testdb
psql -U test -W testdb
CREATE TABLE test_table (id integer);
ALTER TABLE test_table ADD test integer;

now start another client session, disable autocommit:
psql -U test -W testdb
\set AUTOCOMMIT off
SELECT * FROM test_table;

Back in the first session, try to add another column:
ALTER TABLE test_table ADD test1 integer;

This hangs forever, until I commit session 2.
A select should not lock a table even when it is not committed.

Am Donnerstag, 3. Februar 2005 16:11 schrieb Rainer Frey:
> A select should not lock a table even when it is not committed.

The SELECT obtains a read (shared) lock on the table, but the ALTER TABLE
requires a write (exclusive) lock.  This is certainly necessary because you
don't want the structure of the table to be changed while you are reading it.
Additionally, the locking protocol requires that all locks once obtained need
to be held until the end of the transaction.  Both of these issues together
explain the problem you are seeing.  There is nothing that can be done about
it.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Am Freitag, 4. Februar 2005 11:54 schrieb Rainer Frey:
> Thanks for the explanation, though I don't really get the necessity of a
> commit for a read-only statement. Can't a SELECT release its lock after
> it received the response?

If that is the end of the transaction, then you might as well commit it then.
But what if you plan to do an update in the same transaction based on the
selection results?  You can't release and reaquire locks in the same
transaction without getting into a bunch of trouble.  Read up on "strict
two-phase locking" if you're curious.

> Is there any possibility to set a timeout for the lock, after which the
> ALTER TABLE statement fails, instead of remaining in wait status (when
> calling with JDBC?

Yes, there is a statement_timeout parameter or something like that.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Am Freitag, 4. Februar 2005 11:54 schrieb Rainer Frey:
>
>>Is there any possibility to set a timeout for the lock, after which the
>>ALTER TABLE statement fails, instead of remaining in wait status (when
>>calling with JDBC?
>
> Yes, there is a statement_timeout parameter or something like that.

JDBC has a couple of mechanisms that may be more portable than fiddling
with statement_timeout directly:

- Statement.setQueryTimeout(). This is *not* implemented by the current
driver (it is a no-op), but shouldn't be too hard to implement as
mapping to statement_timeout if you feel inclined to do some driver hacking.

- Statement.cancel(). This is implemented by the current driver, but you
will need to build your own infrastructure to handle doing the cancel
from a separate thread if a query takes too long.

-O

On Sat, 5 Feb 2005, Oliver Jowett wrote:

> - Statement.setQueryTimeout(). This is *not* implemented by the current
> driver (it is a no-op), but shouldn't be too hard to implement as
> mapping to statement_timeout if you feel inclined to do some driver hacking.
>

I think it will be complicated on the error handling/cleanup side,
especially with a multithreaded application.  You set it, you execute a
statement, and then you've got to reset it back to the default so other
threads aren't affected by it.

Kris Jurka

Peter Eisentraut schrieb:
> Am Donnerstag, 3. Februar 2005 16:11 schrieb Rainer Frey:
>
>>A select should not lock a table even when it is not committed.
>
>
> The SELECT obtains a read (shared) lock on the table, but the ALTER TABLE
> requires a write (exclusive) lock.  This is certainly necessary because you
> don't want the structure of the table to be changed while you are reading it.
> Additionally, the locking protocol requires that all locks once obtained need
> to be held until the end of the transaction.  Both of these issues together
> explain the problem you are seeing.  There is nothing that can be done about
> it.

Thanks for the explanation, though I don't really get the necessity of a
commit for a read-only statement. Can't a SELECT release its lock after
it received the response?
Is there any possibility to set a timeout for the lock, after which the
ALTER TABLE statement fails, instead of remaining in wait status (when
calling with JDBC?

Rainer Frey