Re: locking problem in jdbc driver?

On 31/12/2009 12:46 AM, Richard Broersma wrote:
> On Wed, Dec 30, 2009 at 8:29 AM, Sherif Kottapurath<sherifkm(at)gmail(dot)com> wrote:
>
>> All threads shown here are operating on
>> the same table, and they are all parts of transactions involving multiple tables.
>> deadlock detection is set for 1 sec and no dedlocks are reported by postgres.
>
> From my experience, PostgreSQL doesn't report deadlocks.

It should, at least with default settings. Failure to do so would, I
expect, be considered a bug. How, exactly, are you creating a deadlock
situation for your testing?

> However, unless you've set your transaction isolation level to
> SERIALIZABLE ( the default isolation level is READ COMMITED), you will
> not be getting deadlocks from PostgreSQL.

Sure you will. In READ COMMITTED:

CREATE TABLE a ( );
CREATE TABLE b ( );

T1: LOCK TABLE a;
T2: LOCK TABLE b;
T2: LOCK TABLE a;
T1: LOCK TABLE b;

then one of the transactions will be aborted with:

ERROR: deadlock detected
DETAIL: Process 13674 waits for AccessExclusiveLock on relation 57791
of database 57071; blocked by process 13672.
Process 13672 waits for AccessExclusiveLock on relation 57788 of
database 57071; blocked by process 13674.
HINT: See server log for query details.

What *doesn't* happen in read committed isolation is serialization failures.

> Also, IIRC the PostgreSQL JDBC driver only allows a single thread to
> access a connection object at a time.

Yep. It's thread safe, but a given connection can only be doing one
thing at a time, so other Java threads will wait for access to the
connection.

--
Craig Ringer