Transaction isolation level REPEATABLE_READ using hibernate and jdbc

Question

I am trying to develop a demo to cement my understanding of the REPEATABLE_READ isolation level. I have tried demos using hibernate and jdbc. JDBC works as expected, however hibernate does not.

My expectation is that, if i read a database row within one session, and i try to write the same row from another session, it should block until the first session finishes.

Hibernate:- When i 'get' an object (row) in Thread 0 and sleep, and within Thread 1 we try to update the same object (row), Thread 1 should wait till Thread 0 commits.

class HibernateExample7_IsolationRR_Read_Write_Thread implements Runnable {

    private static final Logger logger = LoggerFactory.getLogger(HibernateExample7_IsolationRR_Read_Write_Thread.class);

    SessionFactory sessionFactory;
    int sleep;
    int newnumberplate;
    public HibernateExample7_IsolationRR_Read_Write_Thread(SessionFactory sessionFactory, int sleep, int newnumberplate) {
        this.sessionFactory = sessionFactory;
        this.sleep = sleep;
        this.newnumberplate = newnumberplate;
    }

    public void run() {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();
        logger.info("TRYING TO GET VEHICLE 1");
        Vehicle vid1 = (Vehicle) session.get(Vehicle.class, Integer.valueOf(1));
        logger.info("GOT VEHICLE 1");
        vid1.setNumberplate(this.newnumberplate);
        logger.info("SET NUMBERPLATE FOR 1");
        try {
            Thread.sleep(sleep);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        tx.commit();
        session.close();
        logger.info("COMMITED");
    }
}

hibernate.connection.url is jdbc:derby:isolation;create=true and hibernate.connection.isolation is 2. I make two objects of HibernateExample7_IsolationRR_Thread, and i create a scenario where Thread 0 gets the vehicle and sleeps for long time. Meanwhile Thread 1 tries to get and update Vehicle. It successfully commits even before Thread 0 commits.

Due to the REPEATABLE_READ isolation level, shouldn't T1 wait on a commit. If not then it's a READ_COMMITED isolation level not a REPEATABLE_READ isolation level.

What version if hsqldb are you using? – sharakan Feb 23 at 3:56

JB Nizet · Answer 1 · 2013-02-23 08:42:20Z

Your expectation is wrong. Repeatable read doesn't mean that a transaction should block while another transaction reads the same row. It means that reading the same row twice in the same transaction should return the same data, even if another transaction has committed a change to this row between the first and the second read.

You get that (more or less) for free with Hibernate, since the second read will return the data from the first-level cache, without going to the database at all.

See http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Non-repeatable_reads

Also, you're making your test with a database that doesn't support this isolation level:

http://www.hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#N15385

Nitpicky: he'd be hitting second level cache, right? Each thread has its own session. — sharakan, Feb 23 at 13:50
@sharakan: No. A single thread, getting the same entity twice in the same transaction, would hit the database the first time, and the first-level cache the second time. It would thus have repeatable reads, since the first-level cache will return the same data that was returned the first time (unless some changes have been made to the entity between the reads, of course). — JB Nizet, Feb 23 at 13:52
Agreed, but he has two seperate threads, each of which call .openSession, and therefore have separate first level caches. — sharakan, Feb 23 at 15:07
That's exactly my point. Each thread will have its own first level cache, and will thus be unaffected by what other threads do, which will lead to repeatable read inside a single thread, thanks to the first level cache not being shared with other threads. — JB Nizet, Feb 23 at 15:12
Ah, I see what you mean. I thought when you said this: "...since the second read will return the data from the first-level cache, without going to the database at all." you were referring to the read from the second thread in his example. My mistake. — sharakan, Feb 23 at 15:16

Kumar's Blog

Tuesday, December 31, 2013

Transaction isolation level REPEATABLE_READ using hibernate and jdbc

1 Answer

No comments: