Our OLTPish mixed load system has not had a single deadlock since last April, and we just love it.
I would not make any blanket statements, but I think in our case being deadlock-free just makes a lot of practical sense. Of course, in other cases in might not be worth it.
To accomplish deadlock-free state, we have taken the following steps:
- Snapshot isolation for all readers
- If concurrent updates are not frequent, we just acquire some exclusive lock before updating. As long as collisions are not likely, this does not slow us down much. We stress test how it works.
- If concurrent updates become frequent, we solve the problem outside the database. Our application servers accumulate incoming changes and save them in bulk. Of course, these bulk saves still have to acquire some exclusive lock before updating. We stress test how it works.
I still have to see how it works out long term, but short term we save considerable effort on not troubleshooting deadlocks, and not having to implement retry logic. So far I think that being deadlock-free is worth it, but only if we can avoid high concurrency, when lots of modifications touch a single row each. If we cannot avoid high concurrency, then it might be easier to just let it deadlock from time to time.
What do you think? Are you doing anything similar?