Database deadlocks

Question

One of the classical reasons we have a database deadlock is when two transactions are inserting and updating tables in a different order.

For example, transaction A inserts in Table A then Table B.

And transaction B inserts in Table B followed by A.

Such a scenario is always at risk of a database deadlock (assuming you are not using serializable isolation level).

My questions are:

1. What kind of patterns do you follow in your design to make sure that all transactions are inserting and updating in the same order. A book I was reading- had a suggestion that you can sort the statements by the name of the table. Have you done something like this or different - which would enforce that all inserts and updates are in the same order?

2. What about deleting records? Delete needs to start from child tables and updates and inserts need to start from parent tables. How do you ensure that this would not run into a deadlock?

Answer 1

1. All transactions are inserting\updating in the same order.
2. Deletes; identify records to be deleted outside a transaction and then attempt the deletion in the smallest possible transaction, e.g. looking up by the primary key or similar identified during the lookup stage.
3. Small transactions generally.
4. Indexing and other performance tuning to both speed transactions and to promote index lookups over tablescans.
5. Avoid 'Hot tables', e.g. one table with incrementing counters for other tables primary keys. Any other 'switchboard' type configuration is risky.
6. Especially if not using Oracle, learn the looking behaviour of the target RDBMS in detail (optimistic / pessimistic, isolation levels, etc.) Ensure you do not allow row locks to escalate to table locks as some RDMSes will.