How does a relational database execute SQL statements and prepared statements

Introduction In this article, we are going to see how a relational database executes SQL statements and prepared statements.

How do PostgreSQL advisory locks work

Introduction PostgreSQL, like many modern RDBMS, offers both MVCC (Multi-Version Concurrency Control) and explicit pessimistic 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 this article, I’m going to explain how the MVCC (Multi-Version Concurrency Control) mechanism works using PostgreSQL as a reference implementation. 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,… Read More

How to encrypt and decrypt data with Hibernate

Introduction Today, one of my Twitter followers sent me the following StackOverflow question, and, while answering it, I realized that it definitely deserves a post of its own. In this post, I will explain how you can encrypt and decrypt data with Hibernate.

The anatomy of Connection Pooling

Introduction All projects I’ve been working on have used database connection pooling and that’s for very good reasons. Sometimes we might forget why we are employing one design pattern or a particular technology, so it’s worth stepping back and reason on it. Every technology or technological decision has both upsides and downsides, and if you can’t see any drawback you need to wonder what you are missing. The database connection life-cycle Every database read or write operation requires a connection. So let’s see how database connection flow looks like: The flow goes… Read More