On Thu, Dec 31, 2009 at 6:37 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
On 31/12/2009 12:29 AM, Sherif Kottapurath wrote:
I am seeing the following problem, where a thread holding a java lock
seems to be blocking another query. I suspect the the thread holding the
java lock
may be waiting for a DB lock to be freed by the second one. I am pasting the
relevant parts of the java stack trace. All threads shown here are
operating on
the same table, and they are all parts of transactions involving
multiple tables.
The PgJDBC documentation states: "If a thread attempts to use the connection while another one is using it, it will wait until the other thread has finished its current operation. If the operation is a regular SQL statement, then the operation consists of sending the statement and retrieving any ResultSet (in full)."
The lock your thread is waiting on is a lock that controls access to the PostgreSQL connection object so that only one thread may do work with it at a time.
Thanks Craig. This has set us off in the right direction. There is some code which passes transactions (and related connection)
around threads, and that must be the culprit. Should be able to report back within a day whether that was indeed the culprit.
If your threads are deadlocking, it's probably because one thread is waiting for another to do work that it cannot do until the first thread releases the connection. Perhaps you forgot to close a statement somewhere? deadlock detection is set for 1 sec and no dedlocks are reported by
postgres.
We are using multiple connections, but apparently, sometimes 2 threads are trying to use the same connection
and one of these threads is also holding another incomplete transaction.
Thanks a lot.
Sherif
--
Craig Ringer