L' Eomot Inversé (3/21/2013)
Let's be clear here: under what circumstances is the order indeterminate?
I never investigated this myseslf. I have heard this from many people. People I value and trust sufficiently to believe them.
Is this a problem with a single foreign key? Could be - after all, there are still two tables involved in that case, and you can't control the order in which locks are taken.
More likely, this is indeed referring to a scenario with either one table referencing two or more other tables, or with a chain of tables. The more tables there are, the higher the chance that the order the optimizer chooses to access the table causes deadlocks with other transactions.
And for the record, I was indeed referring to the orde in which TABLES are accessed, not the order in whichs ROWS are processed. The common advice for avoiding deadlocks is to always access tables in the same order, and that can only be guaranteed if you wrote code that touches one table at a time.
When there are multiple referring tables, either you deal with all the referring tables in at most 3 triggers or you can't determine the order because you can't specify more than which tables are hit in the first trigger and which in the last, so your triggers may need to be a bit unwieldly if there are more than 3 referring tables and you want to force the order.
I never create more than a single trigger per operation on each table. If I have to do a lot of stuff on inserts of table X, then the ON INSERT trigger for table X will be long - but I have full control over the order in which operations are performed.
(I am in fact so used to writing triggers that way that I often forget that it's possible to have multiple triggers for the same table/operation combination)