Prerequisites

I will not be going through the developer setup (installing java, postgres, maven, etc..) but the sample code assumes you have a postgres database setup and running with the following table definition. It is just a users table that stores the id, name and balance of each user. We will be using the balance field to test our locking code. Execute this sql to create the table.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  balance DECIMAL(10,2) NOT NULL
);

The code also assumes you have a user with id 1 inserted. Execute this sql to populate the table with dummy data.

INSERT INTO users (id, name, balance) VALUES
    (1, 'johnDoe', 100),
    (2, 'janeSmith', 100),
    (3, 'bobJohnson', 100)
ON CONFLICT DO NOTHING;

Implementing Pessimistic Locking With Spring JPA

Step 1: Database Properties

In application.properties, add configuration to connect to your database.

spring.datasource.url=jdbc:postgresql://localhost:5432/your_database
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

Step 2: Create Entities, JPA Repositories and Transactional Service Methods.

Create an entity class to map to the users table. Can use Lombok’s @Data to generate getters/setters or add them manually.

import lombok.Data;
import javax.persistence.*;
import java.math.BigDecimal;
@Data
@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "name")
    private String name;
    
    @Column(name = "balance")
    private BigDecimal balance;
}

Create a JPA repository to interact with database. We will create a method called findByIdForUpdate with @Lock annotation which will allow us to lock the database row when fetching it (since we have the intention to update it).

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import javax.persistence.LockModeType;
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT u FROM User u WHERE u.id = :id")
    User findByIdForUpdate(Long id);
}

Create a service class that will use the jpa repository for application operations. We are creating two methods here.

The first is decreaseBalanceWithoutLocking. This methods takes in the id of the user to update, and the amount to decrease their balance. It uses the findByIdForUpdate method (which acquires a lock) internally to find the user and then updates their balance accordingly.

The second is decreaseBalanceWithoutLocking. This methods takes in the id of the user to update, and the amount to decrease their balance. It uses the findById method (which does NOT acquires a lock) internally to find the user and then updates their balance accordingly.

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class UserService {
    private final UserRepository userRepository;
    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }
    @Transactional
    public void decreaseBalanceWithLocking(Long id, BigDecimal amount) {
        User user = userRepository.findByIdForUpdate(id);
        System.out.println("User: " + user);
        BigDecimal newBalance = user.getBalance().subtract(amount);
        user.setBalance(newBalance);
    }
    @Transactional
    public void decreaseBalanceWithoutLocking(Long id, BigDecimal amount) {
        User user = userRepository.getById(id);
        System.out.println("User: " + user);
        BigDecimal newBalance = user.getBalance().subtract(amount);
        user.setBalance(newBalance);
    }
}

Step 3: Threaded Code to Test Pessimistic Locking

Now, below is code to “test” the two decrease methods and ensure the locking is actually working. I have this code executing on startup by using Spring’s CommandLineRunner interface.

package com.example.locking;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import java.math.BigDecimal;
@SpringBootApplication
public class Main implements CommandLineRunner {
    @Autowired
    UserService userService;
    @Autowired
    UserRepository userRepository;
    public static void main(String[] args) {
        SpringApplication.run(Main.class, args);
    }
    @Override
    public void run(String... args) throws Exception {
        //Execute locking and non locking versions
        testThreadingWithoutLocking();
        testThreadingWithLocking();
    }
    private void testThreadingWithoutLocking() throws InterruptedException {
        System.out.println("Executing non locking code...");
        User user = userRepository.findById(1L).orElse(null);
        System.out.println("Starting balance: " + user.getBalance());
        // Start threads to concurrently update the user balance
        Thread thread1 = new Thread(() -> userService.decreaseBalanceWithoutLocking(1L, BigDecimal.valueOf(1)));
        Thread thread2 = new Thread(() -> userService.decreaseBalanceWithoutLocking(1L, BigDecimal.valueOf(1)));
        Thread thread3 = new Thread(() -> userService.decreaseBalanceWithoutLocking(1L, BigDecimal.valueOf(1)));
        thread1.start();
        thread2.start();
        thread3.start();
        // Wait for the threads to complete
        thread1.join();
        thread2.join();
        thread3.join();
        // Retrieve the updated user
        User updatedUser = userRepository.findById(1L).orElse(null);
        System.out.println("Ending balance: " + updatedUser.getBalance());
    }
    private void testThreadingWithLocking() throws InterruptedException {
        System.out.println("Executing locking code...");
        User user = userRepository.findById(1L).orElse(null);
        System.out.println("Starting balance: " + user.getBalance());
        // Start threads to concurrently update the user balance
        Thread thread1 = new Thread(() -> userService.decreaseBalanceWithLocking(1L, BigDecimal.valueOf(1)));
        Thread thread2 = new Thread(() -> userService.decreaseBalanceWithLocking(1L, BigDecimal.valueOf(1)));
        Thread thread3 = new Thread(() -> userService.decreaseBalanceWithLocking(1L, BigDecimal.valueOf(1)));
        thread1.start();
        thread2.start();
        thread3.start();
        // Wait for the threads to complete
        thread1.join();
        thread2.join();
        thread3.join();
        // Retrieve the updated user
        User updatedUser = userRepository.findById(1L).orElse(null);
        System.out.println("Ending balance: " + updatedUser.getBalance());
    }
}

Console output looks like the below. You can see for the non-locking version, the starting balance was 68 and even though all three threads decreased the balance, the final result was 67. The locking version had the threads execute once after another and the balance went from 67 to 64, which is intended outcome.

Executing non locking code...
Starting balance: 68.00
User: User(id=1, name=johnDoe, balance=68.00)
User: User(id=1, name=johnDoe, balance=68.00)
User: User(id=1, name=johnDoe, balance=68.00)
Ending balance: 67.00
Executing locking code...
Starting balance: 67.00
User: User(id=1, name=johnDoe, balance=67.00)
User: User(id=1, name=johnDoe, balance=66.00)
User: User(id=1, name=johnDoe, balance=65.00)
Ending balance: 64.00

Implementing Pessimistic Locking With JDBC

Threaded Code to Implement and Test Pessimistic Locking

The JDBC version below has all the code in a single file for simplicity.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
@SpringBootApplication
public class Main implements CommandLineRunner {
    @Autowired
    UserService userService;
    @Autowired
    UserRepository userRepository;
    public static void main(String[] args) {
        SpringApplication.run(Main.class, args);
    }
    @Override
    public void run(String... args) throws Exception {
        //JDBC
        testThreadingJDBC(false);
        testThreadingJDBC(true);
    }
    private void testThreadingJDBC(boolean shouldLock) {
        List<Thread> threads = new ArrayList<>();
        String FIND_USER_SQL;
        String DB_URL = "jdbc:postgresql://localhost:5446/postgresdatabaselocking";
        String DB_USER = "postgres";
        String DB_PW = "password";
        String UPDATE_BALANCE_SQL = "UPDATE users SET balance = ? WHERE id = ?";
        int userId = 1;
        if (shouldLock){
            FIND_USER_SQL = "SELECT * FROM users WHERE id = ? FOR UPDATE";
            System.out.println("Executing locking jdbc code...");
        }
        else {
            FIND_USER_SQL = "SELECT * FROM users WHERE id = ?";
            System.out.println("Executing non locking jdbc code...");
        }
        User user = userRepository.findById(1L).orElse(null);
        System.out.println("Starting balance: " + user.getBalance());
        for (int i = 0; i < 3; i++) {
            //Create separate thread each with own database connection. If reuse connection, will not get locking behavior.
            Thread thread = new Thread(() -> {
                try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PW)) {
                    connection.setAutoCommit(false);
                    // Run select statement, if using for update acquire a lock
                    try (PreparedStatement selectStatement = connection.prepareStatement(FIND_USER_SQL)) {
                        selectStatement.setInt(1, userId);
                        ResultSet resultSet = selectStatement.executeQuery();
                        if (resultSet.next()) {
                            // Update the balance
                            try (PreparedStatement updateStatement = connection.prepareStatement(UPDATE_BALANCE_SQL)) {
                                BigDecimal newBalance = resultSet.getBigDecimal("balance").subtract(BigDecimal.ONE);
                                updateStatement.setBigDecimal(1, newBalance);
                                updateStatement.setInt(2, userId);
                                int rowsAffected = updateStatement.executeUpdate();
                                if (rowsAffected > 0) {
                                    connection.commit();
                                    System.out.println("Balance updated!");
                                } else {
                                    connection.rollback();
                                    System.out.println("Balance update failed!");
                                }
                            }
                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            });
            threads.add(thread);
        }
        // Start all the threads
        for (Thread thread : threads) {
            thread.start();
        }
        // Wait for all the threads to finish
        for (Thread thread : threads) {
            try {
                thread.join();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        //Print out end balance.
        User updatedUser = userRepository.findById(1L).orElse(null);
        System.out.println("Ending balance: " + updatedUser.getBalance());
    }
}

Explanation of code
Focusing on the testThreadingJDBC method, the code is as follows

• Create a thread for each connection to the database.
• Each thread finds the user with id 1 and decreases their balance by 1. The locking version uses select for update to find the user while the non locking version does not.
• Starts and joins the threads to ensure they finish.

Note: I used the earlier created userRepository (Spring JPA) to save time in fetching the user balances but of course you can use JDBC or simply inspect the database table to confirm the user balance.

Output of code
You can see (similar to JPA version) the user balance for the non locking version only decreases by 1 although there were three (concurrent) updates, while the user balance for the locking version decreased by 3 as intended.

Executing non locking jdbc code...
Starting balance: 12.00
Balance updated!
Balance updated!
Balance updated!
Ending balance: 11.00
Executing locking jdbc code...
Starting balance: 11.00
Balance updated!
Balance updated!
Balance updated!
Ending balance: 8.00

More Reading:

• https://blog.mimacom.com/handling-pessimistic-locking-jpa-oracle-mysql-postgresql-derbi-h2/