Transaction isolation ANSI REPEATABLE_READ in DB2 not locking rows as expected

Question

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.

What is your Db2-server operating system? (Z/OS, i-series, Linux/Unix/Windows) and your Db2-server version ? — mao, Jun 15 '18 at 13:37
repeatable read sounds like no one can change the table while your reading it. Sounds like IBM just locks the table too. — danny117, Jun 15 '18 at 16:19

score 0 · Answer 1 · answered Jun 15 '18 at 17:25

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.

I am using RS above as specified in the Spring related annotation, but the query does not seem to actually lock the rows returned in the first select statement. That is why I still get deadlock issues when running the transaction concurrently. — Daniel, Jun 15 '18 at 17:57

Transaction isolation ANSI REPEATABLE_READ in DB2 not locking rows as expected

1 Answers1