I've recently been receiving error notifications from a web application I maintain indicating that some SQL commands have been failing caused by deadlocks.
I did some looking into the (various) commands that have been causing the problem and they all have similar procedural issues in common
I'm wondering what would be the best refactor for these objects. I'm sure if I can figure out a better way to do this, I can apply that knowledge to some of the other problem areas. Here is one of the more simple scenarios:
- OrderPeople (Represents individuals assigned to an "Order")
1. Delete a row from "Orders"
2. Cascade delete foreign key on "Orders" -> "OrdersPeople", then deletes associated rows
3. "OrderPeople" has a trigger that fires on Insert, Update and Delete that then in turn updates the "LastUpdated" field on the Order that the people are assigned to... which is also currently (or already has?) been deleted.
4. Deadlock occurs... (not all the time though)
I want to make sure anytime a person which is assigned to an order is added or removed, I update the "LastUpdated" date on the "Orders" table, however in the situation where you delete an entire order and it cascades the delete to the associated table, the trigger no longer makes sense because the order doesn't exist.
Suggestions? I'd like to keep the trigger there but do not know the best way to handle the possible deadlock.
This sounds very like a classic example of "trigger happy" programming - the temptation to create unnecessary trigger code in blissful ignorance of the headaches it causes. A trigger to set a "LastUpdated" attribute seems a bit like using a bulldozer to dig a flower bed.
The best way to ensure efficiency and tight control of updates is to put all data modification code in your stored procedures or other data abstraction tier. Avoid using triggers for anything that modifies data.