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
