On 31/12/2009 12:46 AM, Richard Broersma wrote:
> On Wed, Dec 30, 2009 at 8:29 AM, Sherif Kottapurath<sherifkm@gmail.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