In a multi-user database imagine the following situation. While a transaction is reading multiple rows, let’s say from row 1 to row 1.000.000, another transaction updates row 1 and row 900.000 . We now assume that the first transaction finishes some time after the second one finishes. A problem could now be that transaction one has read the first row before the update of the second transaction and read row 900.000 AFTER the update of transaction 2.
To circumvent this dirty read one can either issue row locks or, as all major database manufacturers have been doing for years, use MVCC. By using this concept, it is guaranteed that readers never block writers and writers never block readers, while the user(s) can read from the database at any time. The idea is to have multiple versions of the data and give each version a timestamp. A read transaction can then only read data with timestamps smaller than its own timestamp.
With the example from above, this would mean that the first transaction would only access versions of data from before the second transaction, while the second transaction can insert new data at the same time with new timestamps. The picture below shows a transaction with timestamp TS=3 reading rows from a database where newer transactions have already inserted newer versions of data.
MVCC example with a transaction reading rows while other transactions are updating.
A database with 100K or 1M users distributed across hundreds of servers, the transactional approach of locking is the main obstacle in keeping down response times. The amount of time to decide when and where to lock and not being able to read while a data set is locked, demonstrates the biggest advantage of MVCC. There is always a consistent version of all data sets of the database availabe to read at any time.
At Triona, along other databases, we use MariaDB which uses an enhanced version of the InnoDB storage engine called Percona XtraDB which includes an advanced MVCC architecture. If you want to be sure that MVCC is enabled, then open up the SQL command line interface and enter “show engines”. Under “Engine” it should say “InnoDB” and below “Comment” Percona-XtraDB among other info.
