@Transactional(isolation = Isolation.SERIALIZABLE, propagation = Propagation.REQUIRES_NEW) not working as expected

Question

I have a usecase as explained below which I am trying to accomplish using transaction in SERIALIZABLE or REPEATALBE READ ISOLATION.

It is a bookstore system. Simplest with no user management and price deduction constraint. The book store count of copies with it. For each order request, I need to check the count of copies available and deduct it by 1 if it is > 0.

For this, I tried to use transaction in which I 1. read the book object and get count 2. if count > 0 updates the count ( count -1 ) in db.

Both of these have to be done in a single transaction to confirm atomicity/ consistency.

I have written this method in my spring boot service :

@Transactional(isolation = Isolation.REPEATABLE_READ, propagation = Propagation.REQUIRES_NEW)
    @Override
    public Book updateBookQuantity(String isbn) {
        Book book = null;

        Optional<Book> bookRecord = bookRepository.findById(isbn);
        try {
            System.out.println("threadname on hold= " + Thread.currentThread().getId());
            Thread.sleep(10000);
        } catch (InterruptedException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("threadname resumed = " + Thread.currentThread().getId());

        if (bookRecord.isPresent()) {
            book = bookRecord.get();
            System.out.println("book count == " + Thread.currentThread().getId() + "  "+ book.getQuantity());

            if (book.getQuantity() > 0) {
                book = bookRecord.get();
                book.setQuantity(bookRecord.get().getQuantity() - 1);
                bookRepository.save(book);
            } else {
                throw new BookOutOfStockException(book.getTitle());
            }
        }
        return book;
    }

Now where am I going wrong with this as when tried to run it using Rest api ( hit 2 times simultaneously) . Both the threads run parallely, both read the count as 1 and updated it to 0. Ideally one should have passed and other should have thrown the BookOutOfStockException exception. I have not configured anything specific to transaction manager, its all handled by spring boot only.

Where is this code going wrong ? Are configuration the reason for this? Note : using spring-data-jpa , database : mysql

Answer 1

Using InnoDB engine resolved the issue. To change the engine, use the correct dialect . spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

MyIASM does not support transactions. so that is the reason why no transactions are getting created in the above problem. Innodb supports transaction and foreign key as well.