CODEWITHSUNDEEP
spring-transactionsdb2-400isolation-levelpessimistic-locking
Daniel
Daniel

Reputation: 744

Transaction isolation ANSI REPEATABLE_READ in DB2 not locking rows as expected

I have a situation with DB2 isolation levels I cannot explain.

I have a transaction with isolation level REPEATABLE_READ, which in DB2 corresponds to READ_STABILITY.

I have basic understanding on DB2 S-, U-, and X-locks.

When I execute the following Spring transaction in parallell, I can cause a deadlock (error from DB2 is FILE IN USE):

@Transactional(isolation = Isolation.REPEATABLE_READ, propagation = Propagation.REQUIRES_NEW)
public long getNext(int x) {
    final Stuff stuff = entityManager
            .createQuery("from Stuff where type = :type", Stuff.class)
            .setParameter("cardType", cardType)
            .getSingleResult();

    stuff.setData(stuff.getData() + x);
    return stuff.getData()+1;
}

I would expect that REPEATABLE_READ isolation would set a DB2 U-lock on the row returned so the parallell transaction gets queued up.

Instead, to get it working, I need to add a pessimistic write lock:

@Transactional(isolation = Isolation.REPEATABLE_READ, propagation = Propagation.REQUIRES_NEW)
public long getNext(int x) {
    final Stuff stuff = entityManager
            .createQuery("from Stuff where type = :type", Stuff.class)
            .setParameter("cardType", cardType)
            .setLockMode(LockModeType.PESSIMISTIC_WRITE)
            .getSingleResult();


    stuff.setData(stuff.getData() + x);
    return stuff.getData()+1;
}

The above query generates the correct locking SQL and transactions work without deadlock:

select * from .... for update with rs;

The question is, why use REPEATABLE_READ when I still need to do manual locking? My understanding is that REPEATABLE_READ must handle necessary row locking on its own to ensure repeatable reads.

Upvotes: 0

Views: 508

Answers (1)

Charles
Charles

Reputation: 23823

Db2 for i supports repeatable read (RR) and read stability (RS).

Repeatable Read

In the SQL 2003 Core standard, Repeatable Read is called Serializable.
Db2® for i supports repeatable read through COMMIT(*RR). Repeatable read isolation level is supported by locking the tables containing any rows that are read or updated.

Repeatable Read is the highest level of isolation.

If you just want to lock the rows you touch, I think you want read stability, (RS). Which is a step down. The difference is RS allows for phantom reads

Phantom. Unit of work UW1 reads the set of n rows that satisfies some search condition. Unit of work UW2 then INSERTs one or more rows that satisfies the search condition. UW1 then repeats the initial read with the same search condition and obtains the original rows plus the inserted rows.

Upvotes: 0

Related Questions