Concurrency Issue – Lost Update
Lost update is the key concurrency problem that we try to avoid:

From the sequences above, SELECT-UPDATE transaction A will overwrite the update Transaction B made to the balance. . If the transactions of A and B would serialize properly, the correct balance value after these transactions would be 700. For performance reasons, most RDBMS uses the isolation level “READ COMMITTED” as default. However, this isolation level does not protect any data read by transaction of getting outdated right after reading the value as another transaction can change it. You can use isolation level “REPEATABLE READ” or “SERIALIZABLE” to protect the values read in the transaction of getting outdated via holding shared read lock on these rows up to the end of the transaction. In case you are new to RDBMS, let me give you some background.
There are 3 things we are trying to avoid in reliable RDBMS:
- Dirty read – if transaction A can read uncommitted changes by transaction B, it may be working on the data that may be rolled back if transaction B cannot successfully commit the changes.
- Non-repeatable read – Transaction A issues the same select twice during the transaction may get different results as some other transactions may commit its changes in between.
- Phantom read – Transaction A issues the same select twice during the transaction may get new rows as some other transactions may insert new records that fits the criteria of the query.
To protect the 3 issues above, RDBMS provides us 4 level of isolations:
- READ UNCOMMITTED – lowest level – no protection at all. Dirty, non-repeatable and phantom read are possible.
- READ COMMITTED – default setting – cannot protect non-repeatable and phantom read
- REPEABLABLE READ – phantom read is still possible
- SERIALIZABLE - highest level with largest overhead as it forces all transaction to run in serial fashion (ie. one by one). However, it resolves all the issues above.
Under the hood, RDBMS uses locking scheme to achieve the 4 transaction isolation level above. How? It uses 2 kinds of locks:
- Shared read lock – more than one read concurrently but no write is possible
- Exclusive write lock – once exclusive write lock is acquired, no one can read and write until it is released.
In other words, read locks block write lock and vice versa. In the above Lost Update problem, transaction A can use shared read lock to make sure no write is possible for the data it read during the course of its transaction. So far so good?
. Lets move on!
Using high level of isolation to solve the lost update issue may give you a new set of issues related to locking:
- Long-running transaction – if transaction A takes a long time to commit, other transactions that wants to change the value will be queued up. Normally, if your transaction read info out from db, change it in UI and write it back. You should consider it as long transaction as you never know how long it takes for user to change it in UI (ie. user think time). In this scenario, you will split the transaction into 2. (ie. a transaction to read and a transaction to write back). However, once you split up the transaction, you have no way to guarantee your data of the previous read up-to-date no matter what isolation level you use. How to solve this problem then?
It can be solved by checking the version or timestamp of the data in database before you write it in the write transaction. If the version is the same as the first read transaction, it means no one has changed it even you leave it wide open, then you are safe to change it. Otherwise, an exception should be thrown. This mechanism is called optimistic locking.
- Deadlock – if transaction A needs other resources in order to commit its transaction but these resources are holding up by a transaction that is waiting for A to commit, it will create a typical circular dependency.
Locks are not just for row. It also happens to index. That is to say, for heavy updates to distinct rows of a table, the bottleneck can be index locking.
New Database Currency Control – MVCC
The aim of Multi-Version Concurrency Control (MVCC) is to avoid Writers blocking Readers and vice-versa, by making use of multiple versions of data (ie. No read lock is necessarily). The problem of Writers blocking Readers can be avoided if Readers can obtain access to a previous version of the data that is locked by Writers for modification. That is to say, there may not have concurrent write. However, while MVCC improves database concurrency, its impact on data consistency is more complex. I will discuss about this later. Now lets look at how to implement MVCC.
Challenges in implementing MVCC
- If multiple versions are stored in the database, an efficient garbage collection mechanism is required to get rid of old versions when they are no longer needed.
- The DBMS must provide efficient access methods that avoid looking at redundant versions.
- The DBMS must avoid expensive lookups when determining the relative commit time of a transaction.
There are essentially two approaches to multi-version concurrency.
- The first approach is to store multiple versions of records in the database, and garbage collect records when they are no longer required. This is the approach adopted by PostgreSQL and Firebird/Interbase.
- The second approach is to keep only the latest version of data in the database, but reconstruct older versions of data dynamically as required by exploiting information within the Write Ahead Log. This is the approach taken by Oracle and MySQL/InnoDb.
How does Postgresql implement MVCC
In PostgreSQL, when a row is updated, a new version (called a tuple) of the row is created and inserted into the table. The previous version is provided a pointer to the new version. The previous version is marked “expired”, but remains in the database until it is garbage collected. So, to achieve this, each tuple needs 2 additional fields:
- creation id/ xmin - The ID of the transaction that inserted/updated the row and created this tuple.
- expired id/ xmax - The transaction that deleted the row, or created a new version of this tuple. Initially it is null.
A row is visible if:
- its creation id is a committed transaction (ensure it doesn’t read uncommitted data from other open transaction) AND its creation id is < current transaction id (ensure non-repeatable read)
- its expired id is null (ensure it is not stale data) or > current transaction id b/c if it is deleted by the transaction after the current one, the current transaction should ignore it.
To track the status of transactions, a special table called PG_LOG is maintained. Since Transaction Ids are implemented using a monotonically increasing counter, the PG_LOG table can represent transaction status as a bitmap. This table contains two bits of status information for each transaction; the possible states are in-progress, committed, or aborted.
One of the drawback of MVCC in PostgreSQL is that old version tuples are kept in the same table. To garbage collected these, you can “VACCUM” it.
Reference
Below are the references I used for this artice:
- Paper – Data Access Pattern
- PostgreSQL Ends the Waiting Game
- MVCC implementation algorithms in different databases
- MarkMail – very good postgresql forum
- Postgresql 8.3.6 Manual