Introduction In this article, we are going to see what Serializability means and what guarantees does it offer. Relational database systems provide a Serializable isolation level that’s supposed to provide transaction Serializability. However, as you will soon see, some databases even provide Strict Serializability, which is a combination of Serializability and Linearizability.
Introduction Linearizability is a lesser-known, yet omnipresent property of a data registry in the context of read and write operations that might happen concurrently. This article aims to explain what linearizability consists of, and why it’s more prevalent that you might have previously thought.
Introduction While doing my High-Performance Java Persistence training, I came to realize that it’s worth explaining how a relational database works, as otherwise, it is very difficult to grasp many transaction-related concepts like atomicity, durability, and checkpoints. In this post, I’m going to give a high-level explanation of how a relational database works internally while also hinting some database-specific implementation details.
Introduction Relational database systems employ various Concurrency Control mechanisms to provide transactions with ACID property guarantees. While isolation levels are one way of choosing a given Concurrency Control mechanism, you can also use explicit locking whenever you want a finer-grained control to prevent data integrity issues. As previously explained, there are two types of explicit locking mechanisms: pessimistic (physical) and optimistic (logical). In this post, I’m going to explain how explicit pessimistic locking interacts with non-query DML statements (e.g. insert, update, and delete).
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.
Introduction As previously explained, every SQL statement must be executed in the context of a database transaction. For modifying statements (e.g. INSERT, UPDATE, DELETE), row-level locks must be taken to ensure recoverability and avoid the data anomalies. Next, I’ll demonstrate what can happen when a database transaction is not properly ended.
Introduction In my article about ACID and database transactions, I introduced the three phenomena described by the SQL standard: dirty read non-repeatable read phantom read While these are good to differentiate the four isolation levels (Read Uncommitted, Read Committed, Repeatable Read and Serializable), in reality, there are more phenomena to take into consideration as well. The 1995 paper (A Critique of ANSI SQL Isolation Levels) introduces the other phenomena that are omitted from the standard specification. In my High-Performance Java Persistence book, I decided to insist on the Transaction chapter as it… Read More
Introduction Having introduced Hibernate explicit locking support, as well as Cascade Types, it’s time to analyze the CascadeType.LOCK behavior. A Hibernate lock request triggers an internal LockEvent. The associated DefaultLockEventListener may cascade the lock request to the locking entity children. Since CascadeType.ALL includes CascadeType.LOCK too, it’s worth understanding when a lock request propagates from a Parent to a Child entity.
Introduction Java Persistence API comes with a thorough concurrency control mechanism, supporting both implicit and explicit locking. The implicit locking mechanism is straightforward and it relies on: Optimistic locking: Entity state changes can trigger a version incrementation Row-level locking: Based on the current running transaction isolation level, the INSERT/UPDATE/DELETE statements may acquire exclusive row locks While implicit locking is suitable for many scenarios, an explicit locking mechanism can leverage a finer-grained concurrency control. In my previous posts, I covered the explicit optimistic lock modes: OPTIMISTIC OPTIMISTIC_FORCE_INCREMENT PESSIMISTIC_FORCE_INCREMENT In this post, I am… Read More
Introduction In my previous post, I introduced the OPTIMISTIC_FORCE_INCREMENT Lock Mode and we applied it for propagating a child entity version change to a locked parent entity. In this post, I am going to reveal the PESSIMISTIC_FORCE_INCREMENT Lock Mode and compare it with its optimistic counterpart.