CODEWITHSUNDEEP
javadatabasehibernatejpatransactions
psh
psh

Reputation: 71

Transaction Isolation Level

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

Database structure:

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:

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?

Upvotes: 7

Views: 1972

Answers (2)

Nobody
Nobody

Reputation: 690

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.

Upvotes: 2

home
home

Reputation: 12538

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.

Upvotes: 4

Related Questions