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

How does Hibernate guarantee application-level repeatable reads

Introduction In my previous post I described how application-level transactions offer a suitable concurrency control mechanism for long conversations. All entities are loaded within the context of a Hibernate Session, acting as a transactional write-behind cache. A Hibernate persistence context can hold one and only one reference to a given entity. The first level cache guarantees session-level repeatable reads. If the conversation spans over multiple requests we can have application-level repeatable reads. Long conversations are inherently stateful so we can opt for detached objects or long persistence contexts. But application-level repeatable reads… Read More

How to prevent lost updates in long conversations

Introduction All database statements are executed within the context of a physical transaction, even when we don’t explicitly declare transaction boundaries (BEGIN/COMMIT/ROLLBACK). Data integrity is enforced by the ACID properties of database transactions. Logical vs Physical transactions A logical transaction is an application-level unit of work that may span over multiple physical (database) transactions. Holding the database connection open throughout several user requests, including user think time, is definitely an anti-pattern. A database server can accommodate a limited number of physical connections, and often those are reused by using connection pooling. Holding… Read More