The Race Condition I Encountered in My Work

Oops.

Photo by Braden Collum on Unsplash

This week I encountered a bug in my application with an incorrect DB update.

What’s the code?

The code roughly went like this:

public void updateValue(Connection conn, int id, String newValue) throws SQLException {
    // Select the row with the given ID
    PreparedStatement selectStmt = conn.prepareStatement("SELECT * FROM mytable WHERE id = ?");
    selectStmt.setInt(1, id);
    ResultSet rs = selectStmt.executeQuery();
    
    // Get the current value of the row
    String currentValue = "";
    if (rs.next()) {
        currentValue = rs.getString("column1");
    } else {
        throw new SQLException("No row found with ID " + id);
    }
    
    // Modify the value
    String updatedValue = currentValue + newValue;
    
    // Prepare a statement to update the row with the new value
    PreparedStatement updateStmt = conn.prepareStatement("UPDATE mytable SET column1 = ? WHERE id = ?");
    updateStmt.setString(1, updatedValue);
    updateStmt.setInt(2, id);
    
    // Execute the update statement
    int numRowsUpdated = updateStmt.executeUpdate();
    if (numRowsUpdated == 0) {
        throw new SQLException("No rows updated for ID " + id);
    }
}

While running and testing the application with 30 threads, we realized the table value is…