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”

MySQL metadata locking and database transaction ending

Introduction

As previously explained, every SQL statement must be executed in the context of a database transaction. For modifying statements (e.g. INSERT, UPDATE, DELETE), row-level locks must be taken to ensure recoverability and avoid the data anomalies.

Next, I’ll demonstrate what can happen when a database transaction is not properly ended.

Continue reading “MySQL metadata locking and database transaction ending”

How does aggressive connection release work in Hibernate

Hibernate connection providers

Hibernate needs to operate both in Java EE and stand-alone environments, and the database connectivity configuration can be done either declaratively or programmatically.

To accommodate JDBC Driver connections as well as RESOURCE_LOCAL and JTA DataSource configurations, Hibernate defines its own connection factory abstraction, represented by the org.hibernate.engine.jdbc.connections.spi.ConnectionProvider interface.

public interface ConnectionProvider 
    extends Service, Wrapped {

    public Connection getConnection() 
        throws SQLException;

    public void closeConnection(Connection connection) 
        throws SQLException;
    
    public boolean supportsAggressiveRelease();
}

Continue reading “How does aggressive connection release work in Hibernate”