How does the 2PL (Two-Phase Locking) algorithm work

Introduction The 2PL (Two-Phase Locking) algorithm is one of the oldest concurrency control mechanisms used by relational database systems to guarantee data integrity. In this article, I’m going to explain how the 2PL algorithm works and how you can implement it in any programming language.

How to implement a database job queue using SKIP LOCKED

Introduction In this article, we are going to see how we can implement a database job queue using SKIP LOCKED. I decided to write this article while answering this Stack Overflow question asked by Rafael Winterhalter. Since SKIP LOCKED is a lesser-known SQL feature, it’s a good opportunity to show you how to use it and why you should employ it, especially when implementing a job queue task.

How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL and MySQL

Introduction Last week, Burkhard Graves asked me to answer the following StackOverflow question: And, since he wasn’t convinced about my answer: I decided to turn it into a dedicated article and explain how UPSERT and MERGE work in the top 4 most common relational database systems: Oracle, SQL Server, PostgreSQL, and MySQL.

How do PostgreSQL advisory locks work

Introduction PostgreSQL, like many modern RDBMS, offers both MVCC (Multi-Version Concurrency Control) and explicit pesimistic locking for various use cases when you want a custom concurrency control mechanism. However, PostgreSQL also offers advisory locks which are very convenient to implement application-level concurrency control patterns. In this article, we are going to explain how PostgreSQL advisory locks work and how you should use them.

How does MVCC (Multi-Version Concurrency Control) work

Introduction In Concurrency Control theory, there are two ways you can deal with conflicts: You can avoid them, by employing a pessimistic locking mechanism (e.g. Read/Write locks, Two-Phase Locking) You can allow conflicts to occur, but you need to detect them using an optimistic locking mechanism (e.g. logical clock, MVCC) Because MVCC (Multi-Version Concurrency Control) is such a prevalent Concurrency Control technique (not only in relational database systems, in this article, I’m going to explain how it works.

How does database pessimistic locking interact with INSERT, UPDATE, and DELETE SQL statements

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).

How to increment the parent entity version whenever a child entity gets modified with JPA and Hibernate

Introduction StackOverflow and the Hibernate forum are gold mines. Yesterday, I bumped on the following question on our forum: Usually, the rationale behind clustering objects together is to form a transactional boundary inside which business invariants are protected. I’ve noticed that with the OPTIMISTIC locking mode changes to a child entity will not cause a version increment on the root. This behavior makes it quite useless to cluster objects together in the first place. Is there a way to configure Hibernate so that any changes to an object cluster will cause the… Read More

How does Hibernate TRANSACTIONAL CacheConcurrencyStrategy work

Introduction In my previous post, I introduced the READ_WRITE second-level cache concurrency mechanism. In this article, I am going to continue this topic with the TRANSACTIONAL strategy.

How does Hibernate READ_WRITE CacheConcurrencyStrategy work

Introduction In my previous post, I introduced the NONSTRICT_READ_WRITE second-level cache concurrency mechanism. In this article, I am going to continue this topic with the READ_WRITE strategy.

How does Hibernate NONSTRICT_READ_WRITE CacheConcurrencyStrategy work

Introduction In my previous post, I introduced the READ_ONLY CacheConcurrencyStrategy, which is the obvious choice for immutable entity graphs. When cached data is changeable, we need to use a read-write caching strategy and this post will describe how NONSTRICT_READ_WRITE second-level cache works.