Thread: [c3p0-users] Idle in Transaction
Status: Beta
Brought to you by:
swaldman
From: Manuel G. C. <man...@ho...> - 2010-05-18 14:04:36
|
Hi, I have a problem where my application sometimes locks with a really high number of "Idle in Transaction"s. The application is heavily used, and some transactions are quite large. However, in the worst case scenario, these long transactions shouldn't be more than 10 at the same time. My max pool size is of 120. When I reach the point where these "Idle in Transaction"s start adding up, I quickly reach the 120 transactions and the application no longer responds. Actually, it stops responding when it starts increasing the number of transactions. My question is, what could cause a transaction never to end? I read somewhere that having autovaccum on the DB while the DB is heavily accesed could cause, and I quote: "I think this problem occurs while a (auto) vacuum is running and there is high traffic on the database. It leads sometimes to an inconsisten database with double primary keys in a table or a broken unique indexes ( "pg_statistic_relid_att_index" for instance). I can't enforce this error." I use PostgreSQL. Do you have any ideas? _________________________________________________________________ Organizar y compartir tus documentos nunca fue tan fácil. Hotmail te da 25 GB gratis. Ver más http://www.descubrewindowslive.com/hotmail/almacenamiento.asp |
From: Steve W. <swa...@mc...> - 2010-05-20 02:11:21
|
Manuel, From c3p0's perspective, it is not the fact that you are idle in transaction that is freezing things up, but the fact that the Connections have not been close()ed. Close()ing a Connection automatically terminates any transaction. If you are seeing the pool grow to maxPoolSize Connections and then freeze, then you very likely need to be close()ing Connections more promptly and certainly. In general, since with a Connection pool, acquiring Connections is very fast, your database access Code should be in quick try/finally blocks, that acquire a Connection, do its business, and then close() with certainty. Ultimately what this sounds like is a Connection leak. If you don't know from where in your code the Connection leak is arising, set c3p0.unreturnedConnectionTimeout and set c3p0. debugUnreturnedConnectionStackTraces to true. If Connections are not returned prior to the timeout, they'll be closed and checked in automatically by c3p0 and the stack trace that led to Connection check- out will be logged. Be sure to use the robust resource cleanup idiom for all Connections. See e.g. http://old.nabble.com/Re:-My-connections-are-all-idle...-p27691635.html smiles, Steve On May 18, 2010, at 10:04 AM, Manuel García Cabrera wrote: > Hi, I have a problem where my application sometimes locks with a > really high number of "Idle in Transaction"s. The application is > heavily used, and some transactions are quite large. However, in the > worst case scenario, these long transactions shouldn't be more than > 10 at the same time. My max pool size is of 120. When I reach the > point where these "Idle in Transaction"s start adding up, I quickly > reach the 120 transactions and the application no longer responds. > Actually, it stops responding when it starts increasing the number > of transactions. > > My question is, what could cause a transaction never to end? I read > somewhere that having autovaccum on the DB while the DB is heavily > accesed could cause, and I quote: > > "I think this problem occurs while a (auto) vacuum is running and > there is > high traffic on the database. It leads sometimes to an inconsisten > database > with double primary keys in a table or a broken unique indexes ( > "pg_statistic_relid_att_index" for instance). I can't enforce this > error." > > > I use PostgreSQL. Do you have any ideas? > > Subir, organizar y compartir documentos online, está a un clic de tu > bandeja de entrada. Ver más > ------------------------------------------------------------------------------ > > _______________________________________________ > c3p0-users mailing list > c3p...@li... > https://lists.sourceforge.net/lists/listinfo/c3p0-users |
From: Manuel G. C. <man...@ho...> - 2010-05-20 13:09:26
|
The thing is, I'm not manually opening the connections. I have a dataSource (com.mchange.v2.c3p0.ComboPooledDataSource), and I set this dataSource to a Hibernate Session Factory (org.springframework.orm.hibernate3.LocalSessionFactoryBean). Then, I begin and commit or rollback transactions. The other way I use this dataSource (the same instance if that matters) is through JdbcTemplate (org.springframework.jdbc.core.JdbcTemplate). So, Hibernate and JdbcTemplate are the ones opening and closing connections. _________________________________________________________________ Registrate en Hotmail y disfrutá de 25 GB para organizar y compartir tus documentos. http://mail.live.com/ |
From: Steve W. <swa...@mc...> - 2010-05-20 18:27:00
|
I'm not very expert on hibernate or Spring, but I think that it is hibernate Sessions that map to Connections. That is, you should not be holding a Session open and then starting/stopping multiple transactions. You should instead acquire and close a new Session for each transaction, holding them open only very briefly, if you want to get the most out of the resource-use efficiency and heal-after-db- restart that a Connection pool like c3p0 can offer. smiles, Steve On May 20, 2010, at 9:09 AM, Manuel García Cabrera wrote: > The thing is, I'm not manually opening the connections. I have a > dataSource (com.mchange.v2.c3p0.ComboPooledDataSource), and I set > this dataSource to a Hibernate Session Factory > (org.springframework.orm.hibernate3.LocalSessionFactoryBean). Then, > I begin and commit or rollback transactions. The other way I use > this dataSource (the same instance if that matters) is through > JdbcTemplate (org.springframework.jdbc.core.JdbcTemplate). So, > Hibernate and JdbcTemplate are the ones opening and closing > connections. > > Tu vida no tiene límites, ahora Hotmail tampoco. 25 GB para > organizar y compartir todo. Hacé clic aquí |
From: Dean H. <de...@al...> - 2010-05-20 19:18:22
|
yes, and seam(the better JSF so to speak) automatically manages the session now and on every request, opens and closes a transaction. I doubt that has bugs though it could. Anyways, I could not figure out why the c3p0 properties were not being loaded that I had configured. I really need to figure that one out first I think. * I wonder, do you have logging in c3p0 that is much like log4jdbc where it logs every call to open and close connection? *In that fashion, I could see what hibernate/seam is doing and then with log4jdbc see what c3p0 is doing on the other end and map them. I would expect to see a hibernate call to open and then 30 calls to log4jdbc to open a new connection(if it kept failing I mean). thanks, Dean 2010/5/20 Steve Waldman <swa...@mc...> > I'm not very expert on hibernate or Spring, but I think that it is > hibernate Sessions that map to Connections. That is, you should not be > holding a Session open and then starting/stopping multiple > transactions. You should instead acquire and close a new Session for > each transaction, holding them open only very briefly, if you want to > get the most out of the resource-use efficiency and heal-after-db- > restart that a Connection pool like c3p0 can offer. > > smiles, > Steve > > > On May 20, 2010, at 9:09 AM, Manuel García Cabrera wrote: > > > The thing is, I'm not manually opening the connections. I have a > > dataSource (com.mchange.v2.c3p0.ComboPooledDataSource), and I set > > this dataSource to a Hibernate Session Factory > > (org.springframework.orm.hibernate3.LocalSessionFactoryBean). Then, > > I begin and commit or rollback transactions. The other way I use > > this dataSource (the same instance if that matters) is through > > JdbcTemplate (org.springframework.jdbc.core.JdbcTemplate). So, > > Hibernate and JdbcTemplate are the ones opening and closing > > connections. > > > > Tu vida no tiene límites, ahora Hotmail tampoco. 25 GB para > > organizar y compartir todo. Hacé clic aquí > > > > ------------------------------------------------------------------------------ > > _______________________________________________ > c3p0-users mailing list > c3p...@li... > https://lists.sourceforge.net/lists/listinfo/c3p0-users > |