Deadlock with JDBC connection

[Nilesh Yawale]

Hi All,

I'm using Weblogic application server 7.0.4 for my application. All my transactions are container managed. Also I'm not using multithreading. But I'm getting many deadlocks. My connection pool size is 100.

Can anybody suggest me the probable reasons of it and solutions?

Thanks in advance

Nilesh

[Chris Mathews]

Originally posted by Nilesh Yawale:
I'm using Weblogic application server 7.0.4 for my application. All my transactions are container managed.

What type of deadlocks? Application code? Database? Regardless, without having access to the sourcecode, an in-depth understanding of the codebase and domain, and a lot of time their is really no way anyone here (or anywhere else online) is going to be able to solve your problem. The best we can do is offer blind guesses... and even for that we would need more information.

Originally posted by Nilesh Yawale:
Also I'm not using multithreading. But I'm getting many deadlocks. My connectin pool size is 100.

100!!!
There is no way you need 100 connections in your connection pool, this could be part of your problem if you are running into database deadlocks (too many open cursors and such). In fact, unless you have tweaked the thread pool size in WebLogic (which I recommend against in most cases) you are guaranteed to never use most of your connection pool. The default execute thread pool size is 15... which is no where near your 100. Take a look at your connection pool statistics and you will probably find that you never get over 10 or 12 active connections. Drop that baby down!

[Byron Estes]

Also make sure you are explicity closing out your JDBC statements and result sets as soon as you can.

Be sure to use the a try/catch/finally structure with the cleanup in the finally.

Be careful in instances where you reuse the same declared variable. I recently had a developer who reports to me encounter an issue where they ran out of database cursors. They thought they were cleaning up appropriately and it looked like they were until we noticed that they were creating a prepared statement, using it and then creating and assigning another prepared statement object to thes same variable without cleaning up first. Long story short. The JDBC driver didn't know the cursor was no longer needed, so it was left hanging out there with no addressability.

[Karthik Guru]

Originally posted by Chris Mathews:

In fact, unless you have tweaked the thread pool size in WebLogic (which I recommend against in most cases)

Chris, have you run into any issues with that?
If there are too many concurrent users executing workflows that take quite sometime to finish, w'dnt it be a good thing to have more threads instead of default 15.
And do you have

no of connections = number of threads OR
is it ok to have say,
no of connections = number of threads X 2

?

I think if the datasource is configured to honour global transactions, all database access code ends up using the same Connection object for the pool?
Is that why 1 connection / thread is ok?