Transaction Isolation Level

Question

I will try to describe my problem in JPA transaction isolation levels.

Database structure:

Table1 -> with PK defined as date ('ddMMyyyy')
Table2 -> with FK to Table1

JPA( isolation level :: read_commited) - code:

    Query query = em.createQuery("from Table1 trd where trd.id = :d");
    query.setParameter("d", date);

    Table1 t = null;
    try{
        t = (Table1) query.getSingleResult();
    }catch(javax.persistence.NoResultException e){
        t = null;
    }

    if(t==null){
        t=new Table1 (date);
        em.persist(trd);
    }

    for(Table2 q:tables2){
        q.setTable1(t);
        em.merge(q);
    }

So procedure check if the record exists in db and if not create new one. Method is completely corect if system in based on just one thread. Otherwise there is possible situation like this:

Thread 1 : Check if entity represent by date exists in database
Thread 2 : Do exactly the same

Both of them think that such record has not exist yet, so add new one. Everything is ok until the moment of commiting transactions. First one will be commited without any exception, buy the second one rise exception related with primary key duplication.

Is any possibility to preserve such situation except changing isolation level to SERIALIZABLE?

score 4 · Answer 1 · answered Jul 07 '11 at 22:00

Yes, in general isolation level = SERIALIZABLE should solve your problem. Do not underestimate side effects when changing isolation level to the most rigid option. It may influence database utilization as well as request throughput. Maybe you can explicitly commit your TRX after creating T1, then open another TRX: EntityManager.getTransaction().commit()

You still have to catch the duplicate key exception.

score 2 · Answer 2 · answered Jul 08 '11 at 17:48

This is one of the hardest problems to solve with databases in a highly concurrent environment. In the end, your only real solution is to catch the exception and deal with it appropriately.

It's hard to tell what that might be, based on the code you provided. If this is a webapp, most likely you would want to catch the duplicate key exception and display some useful message to the end user. Such as "This record was already created", or something along those lines.

Transaction Isolation Level

2 Answers2