Thread: insert deadlock

Thread 1 does 1+ inserts into Table A and 1 update of Table B in a
single transaction.
This thread runs on demand.

Thread 2 does 1+ inserts into Table C in a separate transaction.  This
thread runs once an hour.

There are no foreign key constraints from/to A or B to/from C. The
default isolation level is used.

Thread 1 runs fine except when Thread 2 is running; in this case, about
30% of the time, it gets a deadlock:

2005-12-02 15:04:46,771 [TP-Processor8] INFO
[com.timestock.tess.services.processors.DefectProcessor] - <SQLException>
java.sql.BatchUpdateException: Batch entry 0 insert into ts_defects
(version_info, ts_tran_type, ts_transet_id, ts_tranunit_id,
ts_trancomp_id, ts_user_id, ts_defect_def_id, ts_biz_event_id,
ts_monitor_id, ts_defect_number, ts_occur_date, ts_defect_value,
ts_observed_value, ts_http_status_code, ts_transaction_size,
ts_transaction_time, ts_business_impact, ts_defect_type,
ts_user_importance, ts_defect_importance, ts_tran_importance,
ts_server_ip_address, ts_server_mac_address, ts_server_port,
ts_client_ip_address, ts_missing_id, ts_missing_name, ts_content_error,
ts_data_type, ts_soft_delete, ts_id) values (0, 3, 632465709984376570,
NULL, NULL, 632471964872343772, 632465709984376573, 600000000000000001,
632465709984375001, 1133564681282, 2005-12-02 15:04:38.000000-0800, 280,
NULL, 200, 87120, 167, 64, 2, 4, 4, 4, 3232238338, 00:11:21:DF:5E:00,
80, 3232235928, 0, NULL, NULL, 1, 0, 600000000001844673) was aborted.
Call getNextException to see the cause.

2005-12-02 15:04:46,773 [TP-Processor8] INFO
[com.timestock.tess.services.processors.DefectProcessor] - <SQLException>
java.sql.SQLException: ERROR: deadlock detected

Rolling back the deadlocked transaction and retrying it works. What I
don't understand is why the deadlock occurs as tables A/B seem to have
no connection with C; can anyone explain this?

This is using PG version 8.0.3.

Thanks,
Brian Cox

Brian Cox <bcox@wilytech.com> writes:
> There are no foreign key constraints from/to A or B to/from C.

How sure are you of that?  FK conflicts are much the most common reason
for unexpected deadlocks in pre-8.1 PG releases.

> 2005-12-02 15:04:46,773 [TP-Processor8] INFO
> [com.timestock.tess.services.processors.DefectProcessor] - <SQLException>
> java.sql.SQLException: ERROR: deadlock detected

Java is doing you no favors here by suppressing the detail message that
would have told you exactly who blocked on what.  Perhaps you can get
the detail by looking in the postmaster log, though.

            regards, tom lane

Brian Cox <bcox@wilytech.com> writes:

> Thread 1 does 1+ inserts into Table A and 1 update of Table B in a single
> transaction.
> This thread runs on demand.
>
> Thread 2 does 1+ inserts into Table C in a separate transaction.  This thread
> runs once an hour.

I suspect you'll have to include more information than this to get an answer.
At least the \d output for tables A B and C including the foreign keys and
triggers.

Note that you need to worry not just about foreign key columns in tables A, B,
and C, but also any other table that has a foreign key column that refers to
these tables.

--
greg