Idle Transactions Kill Databases
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
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.
PostgreSQL differ in their implementation of
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.
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.