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?

Answer 1

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.