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

Re: idle in transaction

From: Barry Lind <blind(at)xythos(dot)com>
To: Alexey Yudichev <Alexey(at)francoudi(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: idle in transaction
Date: 2003-03-15 04:02:53
Message-ID: 3E72A5ED.7040608@xythos.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Alexey,

A transaction being started doesn't prevent you from creating an index. 
  A lock on a table prevents you from creating an index.  A select 
statement will cause locks on the table it selects from and those locks 
are not released until the transaction is committed.  What I suspect is 
happening in your application (I say this from experience because I 
needed to do a lot of work in my application to avoid this) is the 
following:

You have code that is getting a connection from your connection pool and 
using it only for select operations.  And because you are only doing 
selects you are not commiting or rolling back before returning the 
connection.  But because you aren't commiting or rollingback the locks 
the select aquired are still being held and thus indexes can't be 
created, vacuum full can't be run, etc.

You need to make sure that you always commit/rollback before returning a 
connection to your connection pool (or make sure your connection pool 
does that for you).

thanks,
--Barry


Alexey Yudichev wrote:
>   It seems that connection.commit() commits current transaction and immediately begins a new one so that connection status is always "idle in transaction". During that time no indicies could be created/dropped, no vaccum command can be issued etc because of locks I suppose. 
>   I use PostgreSQL 7.1 and tried 7.2 drivers and 7.3 drivers (with option compatible=7.1).
>   Is there anything that can be done to allow creating index without restarting the connection pool?
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 




In response to

pgsql-jdbc by date

Next:From: Barry LindDate: 2003-03-15 04:16:03
Subject: Re: CallableStatement, functions and ResultSets
Previous:From: Barry LindDate: 2003-03-15 03:55:53
Subject: Re: Multiple open ResultSets not allowed?

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