Thread: 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