We are seeing blocking threads quite often in our application , not able to identify what is causing the issue .. any help is appreciated. Below is the stack trace of the thread that is starting the blocking. we can see the locking on ms sql server and killing the spid to bring back the application to normal ..
is it with the connection pooling ? or sql driver ? or is it with the java version .. since it's actually happening at the socket level ?
java 220.127.116.11 build
dbcp 1.2.1 - connection pool
MS SQL server
Thread in Blocked state doesn't signify any problem in itself. It's just a thread which waits for some resource to become available or some event (eg. an IO operation) to happen.
The stacktrace shows that the blocked thread very probably waits for a response from the database. When you kill the database process, the database connection is lost and the waiting thread is notified about it somehow, resumes its operation and probably throws an exception (I guess). So, the problem isn't in the application itself, or in the connection pool, but in the database. You need to use database tools to find out what these processes in the database are waiting for, as this is the root cause. The solution will depend on that root cause.
(Note: does killing the SPID really bring the app back to normal? This should produce an exception, as noted above, and you should in turn see that some operation(s) in your app failed, which I wouldn't consider "normal".)
Your executing a prepared statement on a given db connection while the statement is executing on the database another thread wants to do a commit via what looks to be the same connection, your jdbc driver vendor has decided both operations can't happen at the same time on the same connection so one waits fro the other to complete (seems reasonable).
This seems to be more of an architecture \ database performance issue.
"Eagles may soar but weasels don't get sucked into jet engines" SCJP 1.6, SCWCD 1.4, SCJD 1.5,SCBCD 5
First of all, have a close look at your log files to check there aren't some warning messages there. They could be useful to narrow your search.
I don't think the problem is with the connection pool itself. When used properly, it shouldn't give the same connection to two or more threads. However, the dbcp version you're using is rather old. You might try to update to a newer version. Not because it would have a bug that was meanwhile fixed (albeit it is remotely possible), but mostly because a newer version could contain code to detect wrong usage and warn about it.
Are you using an ORM framework, such as Hibernate? I couldn't help a lot in that. If you are using just plain old JDBC, I can give some more tips as to what to look at.
We have been monitoring the logs for a while now and didn't had any luck with them so far. As far as upgrading the dbcp version, I need to prove that's what causing the issue before I can upgrade .. since it's a production. I guess I can try to simulate the issue with old and new versions in test env. if that can be simulated ..
If the logs are silent and upgrading dbcp is out of the question (it probably wouldn't help anyway), you'll need to review your code.
You'll need to go over all places in your code where a database connection is obtained from connection pool, and inspect what is happening to the connection thereafter. Ideally, the connection should be used to run a business transaction on it, ending up with a commit or a rollback, and definitely should be closed when the processing is finished. The closing must happen in a finally block, and no command in the same finally block preceding the connection close should be able to throw an exception (Java 7 try-with-resources would be an ideal way to do this, since it ensures that close() is called in all possible cases). Any deviation from this scheme is suspicious and needs to be verified.
You would be especially looking for indications that the connection is somehow transferred to other threads. It could be done by storing it somewhere where it can be accessed by other threads, such as static variables or, say, a session or servlet context.
I have looked at the code and it seems to be fine. Since it's using a connection object from pool, it is returning properly in the finally block.
After debugging more, i think i may have found where exactly it is happening.. but still can't figure out why .. basically there two prepared statement updates happening with in a transaction and it seems to be blocking while commit on connection object is called.
Both the updates are happening on two different tables, but are linked by foreign key. Also, this part of the code is executed all the time ... so not sure what is happening.
Another very very strange thing, admin guys reported was .. earlier they used to take only thread dumps.. but since last week i have asked them to take heap dumps as well .. so earlier when the locking is happening .. they had to manually kill the spid to bring back the application to normal .. now since they started taking the heap dumps .. when ever they take heap dump.. the locking seems to go away by itself