Optimistic vs. Pessimistic Locking
Introduction In this article, I’m going to explain what is the difference between optimistic and pessimistic locking, as well as when you should employ one or the other concurrency control strategies.
A beginner’s guide to Phantom Read anomaly
Introduction Database transactions are defined by the four properties known as ACID. The Isolation Level (I in ACID) allows you to trade off data integrity for performance. The weaker the isolation level, the more anomalies can occur, and in this article, we are going to describe the Phantom Read phenomenon.
A beginner’s guide to the Write Skew anomaly, and how it differs between 2PL and MVCC
Introduction Unlike SQL Server which, by default, relies on the 2PL (Two-Phase Locking) to implement the SQL standard isolation levels, Oracle, PostgreSQL, and MySQL InnoDB engine use MVCC (Multi-Version Concurrency Control), so handling the Write Skew anomaly can differ from one database to the other. However, providing a truly Serializable isolation level on top of MVCC is really difficult, and, in this post, I’ll demonstrate that it’s very difficult to prevent the Write Skew anomaly without resorting to pessimistic locking.
