A beginner’s guide to the Phantom Read 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).

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 Phantom Read anomaly without resorting to pessimistic locking.

Continue reading “A beginner’s guide to the Phantom Read anomaly, and how it differs between 2PL and MVCC”

Advertisements

How to replace the TABLE identifier generator with either SEQUENCE or IDENTITY in a portable way

Introduction

As previously explained, the TABLE identifier generator does not scale, so you should avoid id. However, some enterprise applications might need to run on both MySQL (which does not support database sequences), as well as Oracle, PostgreSQL and SQL Server 2012.

This is article is going to explain how easily you can achieve this goal using the JPA mapping overriding.

Continue reading “How to replace the TABLE identifier generator with either SEQUENCE or IDENTITY in a portable way”

Book Review – SQL Antipatterns

Introduction

I’ve just finished the wonderful SQL Antipatterns book by Bill Karwin. The book is a must-have reference for any developer that has to interact with a relational database system.

This post is a review of what this book is all about and why you should be interested in reading it.

Continue reading “Book Review – SQL Antipatterns”