Why did we choose to use triggers to update the balance table?
Advantages:
- Consistency between transaction table and balance table.
- High performance due to the efficiency of SQL Server triggers
- Apart from the triggers themselves, no additional code is needed to keep the balance table up to date.
Disadvantages:
- Chance of locking errors when 2 transactions update different transaction records, which related to the same balance record.
Implementation issues:
- Define 3 triggers: One for insert, one for update and one for delete. One trigger for update, insert and delete resulted in our solution to locking errors.
- Start every trigger with the statement IF @@ROWCOUNT = 0 RETURN. This is done for performance reasons, if no inserts, updates or deletes took place at all, do not do anything.
- To limit locking situations:
1) Keep database transactions as short as possible.
2) Do not delete the balance record if all underlying transactions are deleted. In most situations this balance record will be used when new transactions are created. - In the past we have used indexed views, however because of performance reasons we moved to the trigger solution.
2 comments:
Andre,
good posting. To mitigate the risk of locking during update triggers (where records in your balances table get updated), you could
1. consider that the update trigger is inserting records rather then updating existing ones. Then build a mechanism (periodic, i.e. once a day during the night) that compresses all the inserted records in a way that serves the business purpose. For example, if you only need to know what the balance is on a general ledger for a certain period/year combination, you could compress all the inserted records up to that level.
2. another way to prevent locking is instead of having a delete trigger delete existing balance records, you could again just insert records where the inserted record offsets the original record. Then run a periodic process during off-peak times that compresses the records in your balance table.
Ronald
What isolation level are you using?
Post a Comment