Re: locking problem in jdbc driver?

On Thu, Dec 31, 2009 at 6:37 AM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)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.
>>
>
> It looks like your threads may all be trying to use the same connection.
>
> 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.
>>
>
> PostgreSQL will detect deadlocks where two connections in PostgreSQL are
> each waiting for locks the other connection holds. You're only using one
> connection, and your issue is with Java locking, so PostgreSQL knows nothing
> about it.
>
>
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
>