In Part 1 of this article series on optimizing transactional performance of MySQL databases, we explored the importance of keeping transactions small, and in part 2, we discussed selecting appropriate isolation levels.
No discussion of transactional performance is complete without a brief look at deadlocks. If you’re familiar with OS programming, you might already know what a deadlock is—a situation wherein two processes are locked in limbo while accessing the same resource, each waiting for the other to finish.
In a transactional context, a deadlock occurs when two or more clients try to update the same data simultaneously, but in a different sequence. To illustrate, consider Figure 9-2, in which two different transactions are working with the same set of tables, but in a different sequence.
The first transaction is attempting to remove 400 shares from a portfolio account, while the second is trying to add 1,000. Both transactions are initiated at the same time, but the first proceeds by (1) reducing the portfolio account by 400 shares and (2) updating the portfolio net worth table, while the second tries to (1) update the portfolio net worth table to reflect the lower value and (2) deduct 1,000 shares from the portfolio account.
As is clearly visible from the previous example, the result is a deadlock wherein each transaction waits for the other one to finish working with the table it needs to access. If left unresolved, a deadlock such as this would result in each transaction waiting indefinitely for the other one to release its lock on the data. Fortunately, MySQL’s InnoDB table handler comes with built-in intelligence to detect deadlock situations. If it notices one, the InnoDB table handler immediately resolves the deadlock situation by rolling back one of the transactions and releasing its locks, thereby permitting the other one to proceed to its logical end. As in the previous sample output, the client owning the cancelled transaction is notified of the rollback via an error message.
FIGURE1 A transactional deadlock
NOTE
A postmortem of the previous deadlock example would reveal that, more than a database issue, it was a result of poor application design. The order in which the tables were manipulated by the two clients was completely different, resulting in the creation of a deadlock. Most often, deadlocks can be avoided through careful planning and design at the application level. This can ensure that resources are shared gracefully between competing processes and that circular chains (such as the one previously demonstrated) are detected and resolved at the earliest possible opportunity.
A developer can do a number of fundamental things at the application level to avoid deadlocks: obtain all needed locks at the beginning of a session, always process tables in the same order, or include built-in recovery routines to try the transaction again in case it is cancelled by the RDBMS to resolve a deadlock situation.
Leave a Reply