Flyway Database Schema Migrations

Introduction In this article, I’m going to explain how Flyway works and how you can use it to run automated database schema migrations using incremental SQL migration scripts. Flyway is an open-source project created by Axel Fontaine and later acquired by Red Gate. The database migrations can be defined either as SQL scripts or JDBC-based classes.

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.

PostgreSQL trigger consistency check

Introduction In this article, we are going to see how to implement a non-trivial consistency check using a PostgreSQL INSERT and UPDATE trigger. By using a database trigger after executing an INSERT or UPDATE, we can ensure that the sum of salaries in a given department does not exceed the maximum budget allocated for the given department.

A beginner’s guide to database deadlock

Introduction In this article, we are going to see how a deadlock can occur in a relational database system, and how Oracle, SQL Server, PostgreSQL, or MySQL recover from a deadlock situation.

Hibernate database schema multitenancy

Introduction As I explained in this article, multitenancy is an architectural pattern which allows you to isolate customers even if they are using the same hardware or software components. There are multiple ways you can achieve multitenancy, and in this article, we are going to see how you can implement a multitenancy architecture using the database schema as the unit of isolation.

Hibernate database catalog multitenancy

Introduction As I explained in this article, multitenancy is an architectural pattern which allows you to isolate customers even if they are using the same hardware or software components. There are multiple ways you can achieve multitenancy, and in this article, we are going to see how you can implement a multitenancy architecture using the database catalog as the unit of isolation.

A beginner’s guide to database multitenancy

Introduction In software terminology, multitenancy is an architectural pattern which allows you to isolate customers even if they are using the same hardware or software components. Multitenancy has become even more attractive with the widespread adoption of cloud computing. A relational database system provides a hierarchy structure of objects which, typically, looks like this: catalog -> schema -> table. In this article, we are going to see how we can use each of these database object structures to accommodate a multitenancy architecture.

A beginner’s guide to Phantom Read anomaly

Introduction Database transactions are defined by the four properties known as ACID. The Isolation Level (I in ACID) allows you to trade off data integrity for performance. The weaker the isolation level, the more anomalies can occur, and in this article, we are going to describe the Phantom Read phenomenon.

A beginner’s guide to Non-Repeatable Read anomaly

Introduction Database transactions are defined by the four properties known as ACID. The Isolation Level (I in ACID) allows you to trade off data integrity for performance. The weaker the isolation level, the more anomalies can occur, and in this article, we are going to describe the Non-Repeatable Read phenomenon.

A beginner’s guide to Dirty Read anomaly

Introduction Database transactions are defined by the four properties known as ACID. The Isolation Level (I in ACID) allows you to trade off data integrity for performance. The weaker the isolation level, the more anomalies can occur, and in this article, we are going to describe the Dirty Read phenomenon.