As you move from the carefree READ UNCOMMITTED isolation level to the more secure SERIALIZABLE level, the performance of the RDBMS is affected as well. The reason for this is fairly simple: The greater the data integrity you demand from the system, the more work it has to do and the slower it runs. Therefore, as a database administrator or a system analyst, you will usually have to walk a tightrope between the RDBMS’s isolation requirements and its performance.
At the SERIALIZABLE level of isolation, the RDBMS executes transactions sequentially and, thereby, offers the highest level of protection against data corruption. However, because this often involves waiting for locks set by other transactions to be released, it can significantly reduce the speed of your application. At the other end of the spectrum, the READ UNCOMMITTED isolation level allows parallel transactions to see the unsaved changes made by each other, providing much improved performance at a greater risk of inconsistent data. Figure 9-1 illustrates the inverse relationship between transaction security and performance.
MySQL defaults to the REPEATABLE READ isolation level. This isolation level is suitable for most applications, and you would usually only need to alter it if your application has specific need of a higher or lower level. There is no standard formula for deciding what isolation level is right for your application—most often, it is a subjective decision reached on the basis of the application’s tolerance for errors and of the application developer’s judgment of the impact of potentially incorrect data. This selection of isolation level need not even be standard across an application. It’s quite likely, for example, that different transactions within the same application might require different isolation levels based on the tasks each is performing.
Figure 1. The relationship between transaction isolation levels and performance
In the final part of this article series, we’ll discuss the importance of avoiding deadlocks.
Leave a Reply