JDBC problems

[Yazan Jber <qe...@gmail.com>]

I'm having a concurrency problem that seems to be caused by the way
tomcat handles JDBC connections.
My problem happens in a database procedure that is supposed to lock a
cretin raw in the database, preventing any other call to the procedure
from acuiring the same raw, but this behavior is only guaranteed if
the calls to the procedure are made from deferent physical
connections.
In some circumstances the same raw is being acquired by two calls
implying that the calls are being made from the same physical
connection, this made me think that tomcat is returning different
logical connection handles to the same physical connection. Is this
possible ?? Does resource shareable scope mean that tomcat may return
a resource connection before the connection is released?

Thank you in advance

---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org

Re: JDBC problems

[Christopher Schultz <ch...@christopherschultz.net>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yazan,

Yazan Jber wrote:
> I'm having a concurrency problem that seems to be caused by the way 
> tomcat handles JDBC connections.

I'm guessing it's the way your application handles connections, not Tomcat.

> My problem happens in a database procedure that is supposed to lock a
>  cretin raw [certain row?] in the database, preventing any other call
> to the procedure from acquiring the same raw, but this behavior is
> only guaranteed if the calls to the procedure are made from deferent
> physical connections.

Huh? That doesn't sound right. If you are issuing a SELECT .. FOR UPDATE
in a transaction, then maybe this is true. Is that what you are doing?

> In some circumstances the same raw is being acquired by two calls 
> implying that the calls are being made from the same physical 
> connection, this made me think that tomcat is returning different 
> logical connection handles to the same physical connection.

When you request a Connection object from the JNDI DataSource, you
always get a new one. If you are having the same thread call
DataSource.getConnection and expecting to get the same one every time,
you are misunderstanding the way the connection pool works. Request one
connection, use it, then close it.

Your experience does not match your assertion: what you ought to be
worried about is Tomcat giving out the /same/ connection to more than
one thread, which I'm sure it's not doing.

> Is this possible?

Probably not.

> Does resource shareable scope mean that tomcat may return a resource
> connection before the connection is released?

What in the world is "resource sharable" scope? Whatever it is, Tomcat
will not return a resource before the connection is released. The only
way to return the Connection to the pool is to call Connection.close().

My guess is that you have improper or no exception handling when you are
dealing with JDBC connections. Remember that you /must/ close each
resource (ResultSet, Statement, and Connection) when you are finished
with them. If you fail to do this, you /will/ have strange things happen
(such as two "different" requests "somehow sharing" a database connection).

If not all of your JDBC code looks something like this, then you've got
a problem:

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try
{
   conn = // get database connection
   ps = conn.prepareStatement(...);
   // fill ps parameters
   rs = ps.executeQuery();
   // read results
}
finally
{
   if(null != rs) try { rs.close(); }
   catch(SQLException sqle) { sqle.printStackTrace(); }
   if(null != ps) try { ps.close(); }
   catch(SQLException sqle) { sqle.printStackTrace(); }
   if(null != conn) try { conn.close(); }
   catch(SQLException sqle) { sqle.printStackTrace(); }
}

If you are using transactions (conn.setAutoCommit(false)), then you need
even more error handling to get it right.

- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGuOCr9CaO5/Lv0PARAjyZAKCBdFKax0mXBTBqU/Q6ZsPHdjU1XACfW514
W+r8AyNKEO81rQK2Bu9dV78=
=iP5X
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org