What are 'best practices' for dealing with

问题:

While researching a deadlock issue, I found the following post:

https://rails.lighthouseapp.com/projects/8994/tickets/6596

The gist of it is as follows:

1. the MySQL docs say:

   Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

2. Therefore debugging transient deadlocks is an antipattern because MySQL says they are OK and unavoidable.

3. Therefore, Rails should offer us a way, because it:

   makes the assumption that there is the "best" way to do things, and it's designed to encourage that way

4. but Rails doesn't offer us a way so we are using a hacky DIY thing.

So if all of this is true, where is the Rails solution?

---

NOTE: This project is inactive, but seems simple enough to be a solution. Why does Rails not have something like this? https://github.com/qertoip/transaction_retry

回答1:

The fix, for me, was a better index.

The update in question was in a query with a join, and existing indexes were not sufficient for MySQL to join and search efficiently.

Adding the appropriate index completely removed the deadlock issue even in tests with unreasonably concurrent loads.