Idle Transactions Kill Databases

Hung transactions in MySQL— and how to deal with them

If you have a long-running transaction, especially with ISOLATION LEVEL set to REPEATABLE READ, your database is susceptible to seeing considerable performance degradation and a possible crash. Why? Because of MVCC.

A database can only support true SERIALIZIABILITY, when there is single thread is connecting to the database so that all the commits are already in order. For instance, VoltDB does it –

By using serialized processing, VoltDB ensures transactional consistency without the overhead of locking, latching, and transaction logs, while partitioning lets the database handle multiple requests at a time. As a general rule of thumb, the more processors (and therefore the more partitions) in the cluster, the more transactions VoltDB completes per second, providing an easy, almost linear path for scaling an application’s capacity and performance.

But most of the traditional databases have to support concurrency and hence they are forced to use either a 2PL strategy or MVCC. While the implementation of the former is pretty much the same in all major relational databases, MVCC implementation differs quite a lot across databases. MySQL and PostgreSQL differ in their implementation of MVCC.

To support the requirements of all active transactions, databases which implement MVCC has to maintain versions of all the rows which were changed within the duration of any active transaction. After a row version is not in use by any transaction at all, the database management system has a method of getting rid of the row so that the space used by that row can be reclaimed for another row. With MVCC, essentially, a database management system has to implement a garbage collection mechanism to reclaim space.

MySQL

In MySQL, undo records are stored the system tablespace, i.e., ibdata1. It is a page-organized space. The undo space is transactional – and all the pages are stored in the buffer pool for a short period of time, until they are permanently moved to disk. InnoDB keeps a copy of every row that has changed. For instance, when a record is deleted, there might be other transactions which might still need to see this record being deleted, due to transaction isolation. So, only an internal delete flag is flipped. The records themselves are not wiped out from the disk.