How does a relational database work

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”

Advertisements

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”

A beginner’s guide to the Phantom Read anomaly, and how it differs between 2PL and MVCC

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

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 Phantom Read anomaly without resorting to pessimistic locking.

Continue reading “A beginner’s guide to the Phantom Read anomaly, and how it differs between 2PL and MVCC”

A beginner’s guide to SQL injection and how you should prevent it

Introduction

One of my readers asked me to answer the following StackOverflow question. Right from the start, I noticed that the entity query was constructed by concatenating strings, which can expose your application to SQL Injection attacks.

Unfortunately, I’ve been seeing this problem over and over throughout my career, and not all developers are aware of the serious risk associated to SQL Injection. For this reason, this post is going to demonstrate what damage can SQL Injection do to your system.

Continue reading “A beginner’s guide to SQL injection and how you should prevent it”