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.

Continue reading “How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL and MySQL”

Advertisements

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.

Continue reading “How do PostgreSQL advisory locks work”

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.

Continue reading “How does MVCC (Multi-Version Concurrency Control) work”

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

Continue reading “How does database pessimistic locking interact with INSERT, UPDATE, and DELETE SQL statements”

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 root object’s version to increment? I’ve read about OPTIMISTIC_FORCE_INCREMENT but I think this does increment the version regardless of if entities were changed or not. Since reads shouldn’t be conflicting with other reads in most scenarios, this doesn’t seem so useful either.

I could always increment the version inside every mutating behavior of the root, but that is quite error-prone. I’ve also thought of perhaps using AOP to do this, but before looking into it, I wanted to know if there were any easy way to do that. If there were a way to check if an object graph is dirty, then it would make it quite easy to implement as well.

What a brilliant question! This post is going to demonstrate how easy you can implement such a requirement when using Hibernate.

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