Concurrency Control

The Concurrency Challenge

When multiple processes or threads access the same database simultaneously, race conditions can lead to data corruption, lost updates, and inconsistent results. This guide demonstrates common concurrency problems and their solutions.

Understanding Race Conditions

The Read-Modify-Write Problem

Consider a simple counter that should increment 1000 times. Here’s the buggy implementation:

ContadorEnBD.java

public static void main(String[] args) {
    final String sqlConsulta = "SELECT cuenta FROM contadores WHERE nombre=?";
    final String sqlActualización = "UPDATE contadores SET cuenta=? WHERE nombre=?";
    final String claveContador = "contador1";

    try {
        Connection connection = DriverManager.getConnection(
            "jdbc:hsqldb:file:./contadores_atomico;hsqldb.lock_file=false", "SA", "");
        
        PreparedStatement consulta = connection.prepareStatement(sqlConsulta);
        PreparedStatement actualización = connection.prepareStatement(sqlActualización);
        int cuenta = 0;

        consulta.setString(1, claveContador);
        actualización.setString(2, claveContador);
        
        for (int i = 0; i < 1000; i++) {
            // READ
            ResultSet res = consulta.executeQuery();
            if (res.next()) {
                cuenta = res.getInt(1) + 1;
                
                // MODIFY (in memory)
                // Write happens here - RACE CONDITION!
                
                // WRITE
                actualización.setInt(1, cuenta);
                actualización.executeUpdate();
            } else {
                System.out.println("Error: No se encontró el contador");
                break;
            }
        }
        System.out.println("Valor final: " + cuenta);

    } catch (SQLException e) {
        System.out.println("Error SQL: " + e.getMessage());
        e.printStackTrace();
    }
}

What Goes Wrong?

Process A Reads

Process A reads counter value: 100

Process B Reads (Before A Writes)

Process B also reads counter value: 100

Process A Writes

Process A increments to 101 and writes it

Process B Writes (Lost Update!)

Process B also increments to 101 and writes it—overwriting A’s update!

Lost Update Problem: When two processes run this code simultaneously, the final count may be significantly less than 2000 because updates overwrite each other.

Solution 1: Atomic SQL Operations

The simplest and most efficient solution is to perform the increment entirely in SQL:

ContadorEnBDsoloUpdateSql.java

public class ContadorEnBDsoloUpdateSql {
    public static void main(String[] args) {
        final String claveContador = "contador1";
        // The SQL UPDATE is atomic at the database level
        final String sqlActualización = "UPDATE contador SET cuenta=cuenta+1 WHERE nombre='" + claveContador + "';";
    
        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver");  
            Connection connection = DriverManager.getConnection(  
                "jdbc:hsqldb:file:./contadores_atomico;hsqldb.lock_file=false", "contadores", "987654321");  
            PreparedStatement actualización = connection.prepareStatement(sqlActualización);
            
            for (int i=0; i<1000; i++) {
                if (actualización.executeUpdate() != 1) break;
            }
            
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Why This Works: The database guarantees that UPDATE contador SET cuenta=cuenta+1 is atomic. The read, increment, and write all happen as one indivisible operation.

Advantages of Atomic SQL

No Race Conditions

The database handles locking internally.

Best Performance

No explicit locks or transactions needed.

Simple Code

Single SQL statement, easy to understand.

Works at Scale

Handles any number of concurrent processes.

Solution 2: Transactions with Row Locking

When you need to read the value before updating, use FOR UPDATE to lock the row:

ContadorSqlTransaccional.java

public class ContadorSqlTransaccional {
    public static void main(String[] args) throws ClassNotFoundException {
        // FOR UPDATE locks the row for reading
        String sqlConsulta = "select nombre,cuenta from contadores where nombre='contador1' for update;";
        String sqlActualización = "update contadores set cuenta=? where nombre='contador1';";
        
        Class.forName("org.hsqldb.jdbc.JDBCDriver");
        
        try (Connection connection = DriverManager.getConnection(
                "jdbc:hsqldb:file:./contadores_atomico;hsqldb.lock_file=false", "contadores", "987654321"))
        {
            PreparedStatement consulta = connection.prepareStatement(sqlConsulta);
            PreparedStatement actualización = connection.prepareStatement(sqlActualización);
            int cuenta = 0;
            
            for (int i=0; i<1000; i++) {
                connection.setAutoCommit(false);
                ResultSet res = consulta.executeQuery();
                if (res.next()) {
                    cuenta = res.getInt(2);
                    cuenta++;
                    actualización.setInt(1, cuenta);
                    actualización.executeUpdate();
                }
                else break;
                connection.commit();
                connection.setAutoCommit(false);
            } // for
            System.out.println("Valor final: " + cuenta);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

How FOR UPDATE Prevents Race Conditions

Process A Executes

setAutoCommit(false) - Start transaction
SELECT ... FOR UPDATE - Lock row (Process B must wait)
Read value: 100
Increment: 101
UPDATE to 101
commit() - Release lock

Process B Waits

setAutoCommit(false) - Start transaction
SELECT ... FOR UPDATE - BLOCKS waiting for Process A’s lock
Lock acquired after A commits
Read value: 101 (A’s update)
Increment: 102
UPDATE to 102
commit() - Release lock

The key insight: FOR UPDATE creates an exclusive lock. Only one transaction at a time can hold the lock, forcing serial execution of the critical section.

Solution 3: Updatable ResultSets

Combining updatable ResultSets with row locking:

ContadorSqlTransaccionalResUpdateable.java

String sqlConsulta = "select nombre,cuenta from contadores where nombre='contador1' for update;";

Class.forName("org.hsqldb.jdbc.JDBCDriver");

try (Connection connection = DriverManager.getConnection(
        "jdbc:hsqldb:file:./contadores_atomico;hsqldb.lock_file=false", "contadores", "987654321"))
{
    PreparedStatement consulta = connection.prepareStatement(
        sqlConsulta,
        ResultSet.FETCH_FORWARD,
        ResultSet.CONCUR_UPDATABLE
    );
    int cuenta = 0;
    
    for (int i=0; i<1000; i++) {
        connection.setAutoCommit(false);
        ResultSet res = consulta.executeQuery();
        if (res.next()) {
            cuenta = res.getInt(2);
            cuenta++;
            // Update directly through ResultSet
            res.updateInt(2, cuenta);
            res.updateRow();
        }
        else break;
        connection.commit();
        connection.setAutoCommit(false);
    }
    System.out.println("Valor final: " + cuenta);
}

Requirement: This approach requires the row to have a primary key. As noted in the source: “Exije que nombre sea clave primaria !!!!”

Solution 4: Application-Level Transactions

The ContadorBuggy class shows proper transaction handling:

ContadorBuggy.java

public class ContadorBuggy {
    public static void main(String[] args) {
        String url = "jdbc:hsqldb:file:./contadores_atomico;hsqldb.lock_file=false";
        
        try (Connection con = DriverManager.getConnection(url, "SA", "")) {
            con.setAutoCommit(false);
            
            inicializarBD(con);
            
            String sqlActualiza = "update contadores set cuenta = cuenta + 1 where nombre='contador1'";
            
            try (PreparedStatement actualiza = con.prepareStatement(sqlActualiza)) {
                for (int i = 0; i < 1000; i++) {
                    actualiza.executeUpdate();
                }
                con.commit();
                
            } catch (SQLException e) {
                con.rollback();
                throw e;
            }
            
            // Verificar resultado
            try (Statement consulta = con.createStatement();
                 ResultSet res = consulta.executeQuery("select cuenta from contadores where nombre='contador1'")) {
                if (res.next()) {
                    System.out.println("HSQLDB - Valor final: " + res.getInt(1));
                }
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This wraps all 1000 updates in a single transaction. While it prevents partial failures, it doesn’t prevent concurrent access issues unless combined with proper locking.

Comparing Solutions

Solution	Pros	Cons	Best For
Atomic SQL	Fastest, simplest, most reliable	Limited to operations SQL can express	Simple calculations, counters
FOR UPDATE	Flexible, allows complex logic	Slower, explicit locking	Complex business logic requiring read-then-update
Updatable ResultSets	Convenient for multiple columns	Requires primary keys, less portable	Updating multiple columns based on reads
Long Transactions	Ensures consistency across multiple operations	Can cause blocking, deadlocks	Related operations that must succeed/fail together

Concurrency Patterns

Pattern: Optimistic Locking

// Add version column to track changes
String sql = "UPDATE products SET price = ?, version = version + 1 " +
             "WHERE id = ? AND version = ?";

pstmt.setDouble(1, newPrice);
pstmt.setInt(2, productId);
pstmt.setInt(3, currentVersion);

int rowsUpdated = pstmt.executeUpdate();
if (rowsUpdated == 0) {
    // Someone else updated it first - retry or fail
    throw new ConcurrentModificationException();
}

When to Use Optimistic Locking

Good for:

High-read, low-write scenarios
When conflicts are rare
Web applications with long user think time

Avoid when:

High contention (frequent conflicts)
Updates must never fail

Pattern: Pessimistic Locking

connection.setAutoCommit(false);

// Lock immediately
String sql = "SELECT * FROM products WHERE id = ? FOR UPDATE";
ResultSet rs = pstmt.executeQuery();

// Now you have exclusive access
if (rs.next()) {
    // Do complex calculations
    double newPrice = calculatePrice(rs.getDouble("price"));
    
    // Update
    updateStmt.setDouble(1, newPrice);
    updateStmt.executeUpdate();
}

connection.commit(); // Release lock

When to Use Pessimistic Locking

Good for:

High contention scenarios
Complex calculations between read and write
When update failures are unacceptable

Avoid when:

Long processing time (blocks other users)
Low contention (overhead not needed)

Testing Concurrent Code

To test race conditions, run multiple instances simultaneously:

# Terminal 1
java ContadorEnBD &

# Terminal 2
java ContadorEnBD &

# Wait for both to complete
wait

# Check final count
java -cp . VerifyCounter

If the implementations are correct, running two processes simultaneously should produce a final count of 2000. With the buggy version, you’ll see counts less than 2000.

Common Concurrency Antipatterns

Antipattern 1: Sleeping to “Fix” Race Conditions

ResultSet rs = stmt.executeQuery("SELECT count FROM table");
Thread.sleep(100); // This doesn't help!
int newCount = rs.getInt(1) + 1;
stmt.executeUpdate("UPDATE table SET count = " + newCount);

This just makes the race condition window longer!

Antipattern 2: Application-Level Locks for Database Operations

synchronized (this) { // Wrong level of locking!
    ResultSet rs = stmt.executeQuery("SELECT count FROM table");
    int newCount = rs.getInt(1) + 1;
    stmt.executeUpdate("UPDATE table SET count = " + newCount);
}

Synchronized blocks only work within one JVM. Use database-level locking instead.

Antipattern 3: Retrying Without Backoff

while (true) {
    try {
        // Try update
        if (optimisticUpdate()) break;
    } catch (SQLException e) {
        // Immediate retry can make contention worse!
    }
}

Add exponential backoff to reduce contention:

int retries = 0;
while (retries < MAX_RETRIES) {
    try {
        if (optimisticUpdate()) break;
    } catch (SQLException e) {
        Thread.sleep(100 * (1 << retries)); // Exponential backoff
        retries++;
    }
}

Deadlock Prevention

Deadlocks occur when two transactions wait for each other:

Transaction A locks Row 1

Waiting to lock Row 2

Transaction B locks Row 2

Waiting to lock Row 1

Deadlock!

Both transactions are blocked forever.

Prevention Strategies

Consistent Lock Order

Always acquire locks in the same order (e.g., by ID ascending).

Short Transactions

Minimize time between acquiring and releasing locks.

Deadlock Detection

Catch deadlock exceptions and retry with backoff.

Lock Timeout

Set timeouts to prevent infinite waiting.

try {
    connection.setAutoCommit(false);
    // Acquire locks in consistent order
    lockRow(Math.min(id1, id2));
    lockRow(Math.max(id1, id2));
    // Perform updates
    connection.commit();
} catch (SQLException e) {
    if (isDeadlock(e)) {
        connection.rollback();
        // Retry with exponential backoff
    }
}

Best Practices Summary

Prefer Atomic SQL

Use UPDATE table SET col = col + 1 whenever possible.

Use FOR UPDATE Sparingly

Only when you need to read before writing complex calculations.

Keep Transactions Short

Long transactions increase contention and deadlock risk.

Test with Concurrent Load

Always test with multiple simultaneous connections.

Handle Deadlocks

Implement retry logic with exponential backoff.

Monitor Lock Wait Times

Use database monitoring to identify contention hotspots.

Next Steps

CRUD Operations

Review fundamental database operations.

Transactions

Learn more about transaction management.

Getting Started

Core Components

Database Operations

Examples

API Reference

The Concurrency Challenge

Understanding Race Conditions

The Read-Modify-Write Problem

What Goes Wrong?

Solution 1: Atomic SQL Operations

Advantages of Atomic SQL

No Race Conditions

Best Performance

Simple Code

Works at Scale

Solution 2: Transactions with Row Locking

How FOR UPDATE Prevents Race Conditions

Solution 3: Updatable ResultSets

Solution 4: Application-Level Transactions

Comparing Solutions

Concurrency Patterns

Pattern: Optimistic Locking

Pattern: Pessimistic Locking

Testing Concurrent Code

Common Concurrency Antipatterns

Deadlock Prevention

Prevention Strategies

Consistent Lock Order

Short Transactions

Deadlock Detection

Lock Timeout

Best Practices Summary

Next Steps

CRUD Operations

Transactions

Build docs developers (and LLMs) love

Getting Started

Core Components

Database Operations

Examples

API Reference

​The Concurrency Challenge

​Understanding Race Conditions

​The Read-Modify-Write Problem

​What Goes Wrong?

​Solution 1: Atomic SQL Operations

​Advantages of Atomic SQL

No Race Conditions

Best Performance

Simple Code

Works at Scale

​Solution 2: Transactions with Row Locking

​How FOR UPDATE Prevents Race Conditions

​Solution 3: Updatable ResultSets

​Solution 4: Application-Level Transactions

​Comparing Solutions

​Concurrency Patterns

​Pattern: Optimistic Locking

​Pattern: Pessimistic Locking

​Testing Concurrent Code

​Common Concurrency Antipatterns

​Deadlock Prevention

​Prevention Strategies

Consistent Lock Order

Short Transactions

Deadlock Detection

Lock Timeout

​Best Practices Summary

​Next Steps

CRUD Operations

Transactions

Build docs developers (and LLMs) love

The Concurrency Challenge

Understanding Race Conditions

The Read-Modify-Write Problem

What Goes Wrong?

Solution 1: Atomic SQL Operations

Advantages of Atomic SQL

Solution 2: Transactions with Row Locking

How FOR UPDATE Prevents Race Conditions

Solution 3: Updatable ResultSets

Solution 4: Application-Level Transactions

Comparing Solutions

Concurrency Patterns

Pattern: Optimistic Locking

Pattern: Pessimistic Locking

Testing Concurrent Code

Common Concurrency Antipatterns

Deadlock Prevention

Prevention Strategies

Best Practices Summary

Next Steps