Tag PostgreSQL

How do PostgreSQL advisory locks work

vladmihalcea 5 Comments

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” →

Advertisements

How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate

vladmihalcea 4 Comments

Introduction

Dealing with time zones is always challenging. As a rule of thumb, it’s much easier if all date/time values are stored in the UTC format, and, if necessary, dealing with time zone conversations in the UI only.

This article is going to demonstrate how you can accomplish this task with JDBC and the awesome hibernate.jdbc.time_zone configuration property.

Continue reading “How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate” →

How does MVCC (Multi-Version Concurrency Control) work

vladmihalcea 15 Comments

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 a relational database work

vladmihalcea 14 Comments

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.

Continue reading “How does a relational database work” →