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…