Hi there,

I have a application where there are possibilities of a DB query getting rejected because of LOCK on the table row.

My application uses Spring with Hibernate, which is using c3p0 connection pooling to connect to the DB.

The Datasource configuration is as below.

<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="jdbcUrl">
<value>URL</value>
</property>
<property name="user">
<value>User</value>
</property>
<property name="password">
<value>Password</value>
</property>
<property name="initialPoolSize">
<value>2</value>
</property>
<property name="maxPoolSize">
<value>5</value>
</property>
<property name="minPoolSize">
<value>1</value>
</property>
</bean>

Problem:
Often the DB query gets rejected by the error "Caused by: java.sql.SQLException: ORA-00054: resource busy and acquire with NOWAIT specified".
Whenever this occurs, that particular connection is no more valid / used ( I assume ).
So after 5 such errors ( 5 is te maxPoolSize ), I am seeing that the application is hung because there is no more connection to serve.

Can someone please let me know
1. Suppose if the above error occurs, wont that connection be used again ?
2. How to recover from this ?

Much appreciate if anyone can help me here.