Non-repeatable read isolation level with Wildfly and MySQL

Question

I'm implementing Non-repeatable read isolation level on WildFly 9.0.2 in a @Stateless rest resource

1. Thread A is reading a Account entity, prints the balance and then do some other work (sleep).
2. Thread B comes in and read the same Account entity, prints the balance and calculates the balance via calculateBalance() method and then update the entity. It reads the entity again and prints out the balance.
3. Thread A then reads the entity and prints out the balance.

According to my understanding of the Non-Repeatable read level Thread B should block until Thread A completely finish (Exit the transaction/Stateless rest resource).

Here is the printout:

• Thread A: printBalance=500
• Thread B: printBalance=500
• Thread B: printBalance=600
• Thread A: printBalance=500

From that I can see Thread B is not blocking and is allowed to run even though Thread A is still busy.

Below is the code:

    @GET
    @Path("/{accountId}/{threadName}")
    public Response calculcateBalance(@PathParam("accountId") Long accountId, @PathParam("threadName") String threadName) {

        Account account = em.find(Account.class, accountId);
        printBalance(account,threadName);

        if ("ThreadA".equals(threadName)) {
            sleepSeconds(10);
        } else if ("ThreadB".equals(threadName)) {
            account.calculateBalance();
            em.merge(account);
        }

    account = em.find(Account.class, accountId);
    printBalance(account,threadName);

    return Response.ok().build();
}

If I change the isolation level to Serializable everything blocks.

Is my understanding of Non-repeatable read wrong? Should Thread B not be blocked until Thread A is finished?

Answer 1

It depends on the underlying database system. If you were using SQL Server, which uses 2PL by default, the Thread A would acquire a shared lock upon reading that row and the Thread B would be preventing from writing that row (until Thread A releases the shared lock).

Oracle, PostgreSQL, and MySQL use MVCC and the Repeatable Read doesn't use locking because readers don't block writers and writers don't block readers. In MVCC, anomalies are detected and if Thread B modifies that row, then Thread A would detect that change and would abort its transactions.

So, in MVCC, anomalies are detected rather than prevented.