Postgresql – DB lock with any isolation is not working as expected in Spring boot JPA application

I have a simple database table (PostgreSql) with the following data

In the spring boot JPA application, the corresponding Entity class for this table is defined as follows

import java.time.LocalDateTime;

import org.hibernate.annotations.UpdateTimestamp;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Table(name = "counter", schema = "demo")
public class CounterRow {

    @Id
    private int row_id;
    private int counter;
    private String last_user;
    
    @UpdateTimestamp
    private LocalDateTime last_updated;
}

And the repository is defined as follows

import org.ravi.entity.CounterRow;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

public interface CounterRepository extends JpaRepository<CounterRow, Integer> {
    
    @Override
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Optional<CounterRow> findById(Integer id);
}

Now, I have initialized two threads for increasing the counter concurrently and my aim is achieve the synchronization between the two threads at the DB level but not at the application level (i.e., not using synchronized keyword or any other distributed lock mechanism)

If each thread increments the counter by 5 times (in a loop), I would expect the final value of the counter be incremented by 10 compared to its current value. However, I see that it is incremented only by 5 times assuming that this could be due to improper DB synchronization.

This is the class that runs the updater threads

package org.ravi;

import org.ravi.entity.CounterRow;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import jakarta.annotation.PostConstruct;
import lombok.extern.slf4j.Slf4j;

@Component
@Slf4j
public class DbSyncTester {

    @Autowired
    private DbUpdater dbUpdater;

    @PostConstruct
    public void start() {
        final Thread thread1 = getThread("Updater_Thread_1");
        final Thread thread2 = getThread("Updater_Thread_2");
        thread1.start();
        thread2.start();
    }

    private Thread getThread(String threadName) {
        return new Thread(getDbTask(), threadName);
    }

    private Runnable getDbTask() {
        return () -> {
            int loopCounter = 0;
            while (loopCounter++ < 5) {
                dbUpdater.updateDb();
            }
        };
    }
}

DbUpdater class

package org.ravi;

import org.ravi.entity.CounterRow;
import org.ravi.repository.CounterRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import lombok.extern.slf4j.Slf4j;

/**
 * @author teju
 */
@Component
@Slf4j
public class DbUpdater {

    @Autowired
    private CounterRepository repository;

    @Transactional(readOnly = false, isolation = Isolation.SERIALIZABLE, propagation = Propagation.REQUIRED)
    public void updateDb() {
        log.info("Fetching the counter from the DB");
        CounterRow counterRow = repository.findById(1).get();
        log.info("Counter value: {}", counterRow.getCounter());
        counterRow.setCounter(counterRow.getCounter() + 1);
        counterRow.setLast_user(Thread.currentThread().getName());
        repository.save(counterRow);
        log.info("Updated the counter in the DB to {}", counterRow.getCounter());
    }
}

I have added the below line of code on the method hoping to achieve the synchronization at DB level, but no luck.

@Transactional(readOnly = false, isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)

I have also tried with Isolation.SERIALIZABLE, still doesn’t work.

Please help me in understanding the issue with the DB level locks using the above code.