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

A beginner’s guide to the Write Skew 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), so handling the Write Skew anomaly can differ from one database to the other. 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 Write Skew anomaly without resorting to pessimistic locking.

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.

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.

A beginner’s guide to Read and Write Skew phenomena

Introduction In my article about ACID and database transactions, I introduced the three phenomena described by the SQL standard: dirty read non-repeatable read phantom read While these are good to differentiate the four isolation levels (Read Uncommitted, Read Committed, Repeatable Read and Serializable), in reality, there are more phenomena to take into consideration as well. The 1995 paper (A Critique of ANSI SQL Isolation Levels) introduces the other phenomena that are omitted from the standard specification. In my High-Performance Java Persistence book, I decided to insist on the Transaction chapter as it… Read More

How does Hibernate TRANSACTIONAL CacheConcurrencyStrategy work

Introduction In my previous post, I introduced the READ_WRITE second-level cache concurrency mechanism. In this article, I am going to continue this topic with the TRANSACTIONAL strategy.

Things to consider before jumping to application-level caching

Introduction Relational database transactions are ACID and the strong consistency model simplifies application development. Because enabling Hibernate caching is one configuration away, it’s very appealing to turn to caching whenever the data access layer starts showing performance issues. Adding a caching layer can indeed improve application performance, but it has its price and you need to be aware of it.

How to fix optimistic locking race conditions with pessimistic locking

Recap In my previous post, I explained the benefits of using explicit optimistic locking. As we then discovered, there’s a very short time window in which a concurrent transaction can still commit a Product price change right before our current transaction gets committed. This issue can be depicted as follows: Alice fetches a Product She then decides to order it The Product optimistic lock is acquired The Order is inserted in the current transaction database session The Product version is checked by the Hibernate explicit optimistic locking routine The price engine manages… Read More

A beginner’s guide to Java Persistence locking

Implicit locking In concurrency theory, locking is used for protecting mutable shared data against hazardous data integrity anomalies. Because lock management is a very complex problem, most applications rely on their data provider implicit locking techniques. Delegating the whole locking responsibility to the database system can both simplify application development and prevent concurrency issues, such as deadlocking. Deadlocks can still occur, but the database can detect and take safety measures (arbitrarily releasing one of the two competing locks).

A beginner’s guide to transaction isolation levels in enterprise Java

Introduction A relational database strong consistency model is based on ACID transaction properties. In this post we are going to unravel the reasons behind using different transaction isolation levels and various configuration patterns for both resource local and JTA transactions. Isolation and consistency In a relational database system, atomicity and durability are strict properties, while consistency and isolation are more or less configurable. We cannot even separate consistency from isolation as these two properties are always related. The lower the isolation level, the less consistent the system will get. From the least… Read More