The short story: the default Postgres transaction isolation level can lead to trouble in a web application. I’m showing here a way to change it in a Spring/JPA application.
The long story: One of the applications I worked recently on consists of a rich client which issues an HTTP request for every little thing it does to the backend. The Spring/JPA backend will then execute the requested operation in a transaction and commit the results at the end. One of the use cases foresees that the backend will check for an entry in the database and if that entry does not exist then create and return it.
Now it turned out that the chatty client I mentioned earlier was issuing two slightly different requests to the backend which lead to that usecase: two transactions simultaneously looking for the same entry. Normally that would not worry anyone, since transaction isolation should take care of that. But what I observed quite differed from theory:
Transaction 1: Look for entry -> Entry not found -> Create entry
Transaction 2: Look for entry -> Entry not found -> Create entry
In an isolated transaction environment Transaction 2 should either see the entry created in transaction 1 and thus return it, or not see it and thus (successfully) create it. But it turns out that transaction 2 didn’t see the entry created by transaction 1, tried to (re)create it and then failed with an unique constraint exception.
The postgres documentation [1] states that the default transaction isolation is READ COMMITTED which does not guarantee that reads are isolated.
Changing the transaction isolation to SERIALIZABLE solved the issue at possibly some performance penalties, but at least now transaction 2 sees the entry created by transaction 1.
In Spring and JPA it is actually enough to just set up the correct isolation level:
public class PostgresJpaDialect extends HibernateJpaDialect{
@Override
public Object beginTransaction(EntityManager entityManager,
TransactionDefinition definition) throws PersistenceException,
SQLException, TransactionException {
DefaultTransactionDefinition dtd = new DefaultTransactionDefinition(definition);
dtd.setIsolationLevel(TransactionDefinition.ISOLATION_SERIALIZABLE);
return super.beginTransaction(entityManager, definition);
}
}
And instruct the entity manager to use it:
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> ...
Resources
[1] PostreSQL Set Transaction
George's Techblog 