Re: Deadlocks caused by referential integrity checks

>>>>> "SS" == Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

SS> If transaction 1 inserts a child row that references row A, then
SS> transaction 2 does a child row that references row B and they both then go
SS> to do child rows that reference the other, in the current implementation,
SS> there's no way to change the order to make that work (although deferring
SS> the constraint often lowers the probability sufficiently).

consider three tables: users, messages, actions.

primary key of users is users_id.

messages referes to users_id as FK.
actions refers to users_id as FK.

Now, we track sent messages by doing a select on users inserting the ID
numbers into messages, along with a message ID.  This select can have
thousands of rows.

the actions track things that those users do.  those actions are
inserted in unpredictable order.

If an action happens by a user who is currently the target of a new
message, both inserts will try to lock that row for the FK check.
Since the order of actions is unpredictable, you're hosed.  Deadlock
occurs and you spit and curse. :-(

If PG had a way for me to tell it the action logger transaction was
"less important" and should be the one killed, I'd live with that,
since the other transaction is usually more expensive.



--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/