Transactions - Isolation

Content:

  1. Introduction
  2. Concept
  3. Example
  4. Performance

Introduction:

This article will cover the isolation aspect.

Concept:

Example:

Let us look at a very simple example: read a value from the database and write back an updated value.

SQL DDL:

CREATE TABLE concdata (id INTEGER NOT NULL, val INTEGER, PRIMARY KEY(id))

We will test two different SQL DML constructs:

SELECT val FROM concdata WHERE id = ?
UPDATE concdata SET val = ? WHERE id = ?

and:

SELECT val FROM concdata WHERE id = ? FOR UPDATE
UPDATE concdata SET val = ? WHERE id = ?

We will test 1000 operations in 50 threads.

We will test 9 different transactional contexts:

The test is actually rather simple and one could expect it to be easy to get correct results. But the reality is different.

The good flow is:

And a bad flow is:

We will distibguish between 3 outcomes:

wrong result
updates are lost
error
application detect an error
correct results
no updates are lost

Summary of results:

Transaction Isolation Level Retry logic in place SELECT ... + UPDATE ... SELECT ... FOR UPDATE + UPDATE ...
(no transaction) wrong result wrong result some databases
correct result some databases
Read Uncommitted No wrong result correct result
Read Committed wrong result correct result
Repeatable Read wrong result some databases
error some databases
error some databases
correct result some databases
Serializable error error some databases
correct result some databases
Read Uncommitted Yes wrong result correct result
Read Committed wrong result correct result
Repeatable Read wrong result some databases
correct result some databases
correct result
Serializable correct result correct result

Fundamentaly only two approaches work across databases:

The second only works if the database support SELECT FOR UPDATE.

I do not understand why plain SELECT + transaction with transaction isolation level repeatable read + retry logic in place does not always work. I believe it should work by the definitions. But it is an observable fact that it does not work with MySQL.

Test code in Java:

package tx.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLTransientException;
import java.sql.Statement;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

import org.apache.commons.dbcp2.DriverManagerConnectionFactory;
import org.apache.commons.dbcp2.PoolableConnection;
import org.apache.commons.dbcp2.PoolableConnectionFactory;
import org.apache.commons.dbcp2.PoolingDriver;
import org.apache.commons.pool2.impl.GenericObjectPool;


public class IsolationTest {
    private static AtomicInteger good = new AtomicInteger();
    private static AtomicInteger bad = new AtomicInteger();
    private static AtomicInteger retries = new AtomicInteger();
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:apache:commons:dbcp:mypool");
    }
    public static interface Approach {
        public abstract String getName();
        public abstract boolean isSupported(Connection con) throws SQLException;
        public abstract void runtest() throws SQLException;
    }
    public static class StandardNone implements Approach {
        @Override
        public String getName() {
            return "SELECT + UPDATE @ No transaction";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return true;
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(true);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ?");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    sel.setInt(1,  1);
                    ResultSet rs = sel.executeQuery();
                    rs.next();
                    int val = rs.getInt(1);
                    val = val + 1;
                    upd.setInt(1,  val);
                    upd.setInt(2,  1);
                    upd.executeUpdate();
                }
            }
        }
    }
    public static class StandardRU implements Approach {
        @Override
        public String getName() {
            return "SELECT + UPDATE @ Read Uncommitted";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ?");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    sel.setInt(1,  1);
                    ResultSet rs = sel.executeQuery();
                    rs.next();
                    int val = rs.getInt(1);
                    val = val + 1;
                    upd.setInt(1,  val);
                    upd.setInt(2,  1);
                    upd.executeUpdate();
                    con.commit();
                }
            }
        }
    }
    public static class StandardRC implements Approach {
        @Override
        public String getName() {
            return "SELECT + UPDATE @ Read Committed";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ?");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    sel.setInt(1,  1);
                    ResultSet rs = sel.executeQuery();
                    rs.next();
                    int val = rs.getInt(1);
                    val = val + 1;
                    upd.setInt(1,  val);
                    upd.setInt(2,  1);
                    upd.executeUpdate();
                    con.commit();
                }
            }
        }
    }
    public static class StandardRR implements Approach {
        @Override
        public String getName() {
            return "SELECT + UPDATE @ Repeatable Read";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ?");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    sel.setInt(1,  1);
                    ResultSet rs = sel.executeQuery();
                    rs.next();
                    int val = rs.getInt(1);
                    val = val + 1;
                    upd.setInt(1,  val);
                    upd.setInt(2,  1);
                    upd.executeUpdate();
                    con.commit();
                }
            }
        }
    }
    public static class StandardS implements Approach {
        @Override
        public String getName() {
            return "SELECT + UPDATE @ Serializable";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ?");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    sel.setInt(1,  1);
                    ResultSet rs = sel.executeQuery();
                    rs.next();
                    int val = rs.getInt(1);
                    val = val + 1;
                    upd.setInt(1,  val);
                    upd.setInt(2,  1);
                    upd.executeUpdate();
                    con.commit();
                }
            }
        }
    }
    public static class StandardRUretry implements Approach {
        @Override
        public String getName() {
            return "SELECT + UPDATE @ Read Uncommitted (with retry)";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ?");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    boolean done;
                    do {
                        try {
                            sel.setInt(1,  1);
                            ResultSet rs = sel.executeQuery();
                            rs.next();
                            int val = rs.getInt(1);
                            val = val + 1;
                            upd.setInt(1,  val);
                            upd.setInt(2,  1);
                            upd.executeUpdate();
                            con.commit();
                            done = true;
                        } catch(SQLException ex) {
                            if(ex instanceof SQLTransientException ||
                               ex.getSQLState().equals("40001")) {
                                retries.addAndGet(1);
                                con.rollback();
                                done = false;
                            } else {
                                throw ex;
                            }
                        }
                    } while(!done);
                }
            }
        }
    }
    public static class StandardRCretry implements Approach {
        @Override
        public String getName() {
            return "SELECT + UPDATE @ Read Committed (with retry)";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ?");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    boolean done;
                    do {
                        try {
                            sel.setInt(1,  1);
                            ResultSet rs = sel.executeQuery();
                            rs.next();
                            int val = rs.getInt(1);
                            val = val + 1;
                            upd.setInt(1,  val);
                            upd.setInt(2,  1);
                            upd.executeUpdate();
                            con.commit();
                            done = true;
                        } catch(SQLException ex) {
                            if(ex instanceof SQLTransientException ||
                               ex.getSQLState().equals("40001")) {
                                retries.addAndGet(1);
                                con.rollback();
                                done = false;
                            } else {
                                throw ex;
                            }
                        }
                    } while(!done);
                }
            }
        }
    }
    public static class StandardRRretry implements Approach {
        @Override
        public String getName() {
            return "SELECT + UPDATE @ Repeatable Read (with retry)";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ?");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    boolean done;
                    do {
                        try {
                            sel.setInt(1,  1);
                            ResultSet rs = sel.executeQuery();
                            rs.next();
                            int val = rs.getInt(1);
                            val = val + 1;
                            upd.setInt(1,  val);
                            upd.setInt(2,  1);
                            upd.executeUpdate();
                            con.commit();
                            done = true;
                        } catch(SQLException ex) {
                            if(ex instanceof SQLTransientException ||
                               ex.getSQLState().equals("40001")) {
                                retries.addAndGet(1);
                                con.rollback();
                                done = false;
                            } else {
                                throw ex;
                            }
                        }
                    } while(!done);
                }
            }
        }
    }
    public static class StandardSretry implements Approach {
        @Override
        public String getName() {
            return "SELECT + UPDATE @ Serializable (with retry)";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ?");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    boolean done;
                    do {
                        try {
                            sel.setInt(1,  1);
                            ResultSet rs = sel.executeQuery();
                            rs.next();
                            int val = rs.getInt(1);
                            val = val + 1;
                            upd.setInt(1,  val);
                            upd.setInt(2,  1);
                            upd.executeUpdate();
                            con.commit();
                            done = true;
                        } catch(SQLException ex) {
                            if(ex instanceof SQLTransientException ||
                               ex.getSQLState().equals("40001")) {
                                retries.addAndGet(1);
                                con.rollback();
                                done = false;
                            } else {
                                throw ex;
                            }
                        }
                    } while(!done);
                }
            }
        }
    }
    public static class ForUpdateNone implements Approach {
        @Override
        public String getName() {
            return "SELECT FOR UPDATE + UPDATE @ No transaction";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsSelectForUpdate();
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(true);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ? FOR UPDATE");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    sel.setInt(1,  1);
                    ResultSet rs = sel.executeQuery();
                    rs.next();
                    int val = rs.getInt(1);
                    val = val + 1;
                    upd.setInt(1,  val);
                    upd.setInt(2,  1);
                    upd.executeUpdate();
                }
            }
        }
    }
    public static class ForUpdateRU implements Approach {
        @Override
        public String getName() {
            return "SELECT FOR UPDATE + UPDATE @ Read Uncommitted";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsSelectForUpdate() && con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ? FOR UPDATE");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    sel.setInt(1,  1);
                    ResultSet rs = sel.executeQuery();
                    rs.next();
                    int val = rs.getInt(1);
                    val = val + 1;
                    upd.setInt(1,  val);
                    upd.setInt(2,  1);
                    upd.executeUpdate();
                    con.commit();
                }
            }
        }
    }
    public static class ForUpdateRC implements Approach {
        @Override
        public String getName() {
            return "SELECT FOR UPDATE + UPDATE @ Read Committed";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsSelectForUpdate() && con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ? FOR UPDATE");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    sel.setInt(1,  1);
                    ResultSet rs = sel.executeQuery();
                    rs.next();
                    int val = rs.getInt(1);
                    val = val + 1;
                    upd.setInt(1,  val);
                    upd.setInt(2,  1);
                    upd.executeUpdate();
                    con.commit();
                }
            }
        }
    }
    public static class ForUpdateRR implements Approach {
        @Override
        public String getName() {
            return "SELECT FOR UPDATE + UPDATE @ Repeatable Read";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsSelectForUpdate() && con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ? FOR UPDATE");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    sel.setInt(1,  1);
                    ResultSet rs = sel.executeQuery();
                    rs.next();
                    int val = rs.getInt(1);
                    val = val + 1;
                    upd.setInt(1,  val);
                    upd.setInt(2,  1);
                    upd.executeUpdate();
                    con.commit();
                }
            }
        }
    }
    public static class ForUpdateS implements Approach {
        @Override
        public String getName() {
            return "SELECT FOR UPDATE + UPDATE @ Serializable";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsSelectForUpdate() && con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ? FOR UPDATE");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    sel.setInt(1,  1);
                    ResultSet rs = sel.executeQuery();
                    rs.next();
                    int val = rs.getInt(1);
                    val = val + 1;
                    upd.setInt(1,  val);
                    upd.setInt(2,  1);
                    upd.executeUpdate();
                    con.commit();
                }
            }
        }
    }
    public static class ForUpdateRUretry implements Approach {
        @Override
        public String getName() {
            return "SELECT FOR UPDATE + UPDATE @ Read Uncommitted (with retry)";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsSelectForUpdate() && con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ? FOR UPDATE");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    boolean done;
                    do {
                        try {
                            sel.setInt(1,  1);
                            ResultSet rs = sel.executeQuery();
                            rs.next();
                            int val = rs.getInt(1);
                            val = val + 1;
                            upd.setInt(1,  val);
                            upd.setInt(2,  1);
                            upd.executeUpdate();
                            con.commit();
                            done = true;
                        } catch(SQLException ex) {
                            if(ex instanceof SQLTransientException ||
                               ex.getSQLState().equals("40001")) {
                                retries.addAndGet(1);
                                con.rollback();
                                done = false;
                            } else {
                                throw ex;
                            }
                        }
                    } while(!done);
                }
            }
        }
    }
    public static class ForUpdateRCretry implements Approach {
        @Override
        public String getName() {
            return "SELECT FOR UPDATE + UPDATE @ Read Committed (with retry)";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsSelectForUpdate() && con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ? FOR UPDATE");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    boolean done;
                    do {
                        try {
                            sel.setInt(1,  1);
                            ResultSet rs = sel.executeQuery();
                            rs.next();
                            int val = rs.getInt(1);
                            val = val + 1;
                            upd.setInt(1,  val);
                            upd.setInt(2,  1);
                            upd.executeUpdate();
                            con.commit();
                            done = true;
                        } catch(SQLException ex) {
                            if(ex instanceof SQLTransientException ||
                               ex.getSQLState().equals("40001")) {
                                retries.addAndGet(1);
                                con.rollback();
                                done = false;
                            } else {
                                throw ex;
                            }
                        }
                    } while(!done);
                }
            }
        }
    }
    public static class ForUpdateRRretry implements Approach {
        @Override
        public String getName() {
            return "SELECT FOR UPDATE + UPDATE @ Repeatable Read (with retry)";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsSelectForUpdate() && con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ? FOR UPDATE");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    boolean done;
                    do {
                        try {
                            sel.setInt(1,  1);
                            ResultSet rs = sel.executeQuery();
                            rs.next();
                            int val = rs.getInt(1);
                            val = val + 1;
                            upd.setInt(1,  val);
                            upd.setInt(2,  1);
                            upd.executeUpdate();
                            con.commit();
                            done = true;
                        } catch(SQLException ex) {
                            if(ex instanceof SQLTransientException ||
                            ex.getSQLState().equals("40001")) {
                                retries.addAndGet(1);
                                con.rollback();
                                done = false;
                            } else {
                                throw ex;
                            }
                        }
                    } while(!done);
                }
            }
        }
    }
    public static class ForUpdateSretry implements Approach {
        @Override
        public String getName() {
            return "SELECT FOR UPDATE + UPDATE @ Serializable (with retry)";
        }
        @Override
        public boolean isSupported(Connection con) throws SQLException {
            return con.getMetaData().supportsSelectForUpdate() && con.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE);
        }
        @Override
        public void runtest() throws SQLException {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ? FOR UPDATE");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    boolean done;
                    do {
                        try {
                            sel.setInt(1,  1);
                            ResultSet rs = sel.executeQuery();
                            rs.next();
                            int val = rs.getInt(1);
                            val = val + 1;
                            upd.setInt(1,  val);
                            upd.setInt(2,  1);
                            upd.executeUpdate();
                            con.commit();
                            done = true;
                        } catch(SQLException ex) {
                            if(ex instanceof SQLTransientException ||
                               ex.getSQLState().equals("40001")) {
                                retries.addAndGet(1);
                                con.rollback();
                                done = false;
                            } else {
                                throw ex;
                            }
                        }
                    } while(!done);
                }
            }
        }
    }
    private static int getVal(Connection con) throws SQLException {
        try(Statement stmt = con.createStatement()) {
            try(ResultSet rs = stmt.executeQuery("SELECT val FROM concdata WHERE id = 1")) {
                rs.next();
                return rs.getInt(1);
            }
        }
    }
    private static void test(Approach ap) throws SQLException, InterruptedException {
        try(Connection con = getConnection()) {
            try(Statement stmt = con.createStatement()) {
                try {
                    stmt.executeUpdate("DROP TABLE concdata");
                } catch(SQLException ex) { }
                stmt.executeUpdate("CREATE TABLE concdata (id INTEGER NOT NULL, val INTEGER, PRIMARY KEY(id))");
                stmt.executeUpdate("INSERT INTO concdata VALUES(1, 0)");
            }
            if(ap.isSupported(con)) {
                long t1 = System.currentTimeMillis();
                good.set(0);
                bad.set(0);
                retries.set(0);
                ExecutorService es = Executors.newFixedThreadPool(50);
                for(int i = 0; i < 1000; i++) {
                    es.submit(() -> { try { ap.runtest(); good.addAndGet(1); } catch(SQLException ex) { bad.addAndGet(1); }});
                }
                es.shutdown();
                es.awaitTermination(1, TimeUnit.HOURS);
                long t2 = System.currentTimeMillis();
                System.out.printf("  %s : %d (%d ms, %d good updates, %d errors, %d retries)\n", ap.getName(), getVal(con), t2 - t1, good.get(), bad.get(), retries.get());
            } else {
                System.out.printf("  %s : not supported\n", ap.getName());
            }
            try(Statement stmt = con.createStatement()) {
                stmt.executeUpdate("DROP TABLE concdata");
            }
        }
    }
    private static void test(String db) throws SQLException, InterruptedException {
        System.out.printf("%s:\n", db);
        test(new StandardNone());
        test(new StandardRU());
        test(new StandardRC());
        test(new StandardRR());
        test(new StandardS());
        test(new StandardRUretry());
        test(new StandardRCretry());
        test(new StandardRRretry());
        test(new StandardSretry());
        test(new ForUpdateNone());
        test(new ForUpdateRU());
        test(new ForUpdateRC());
        test(new ForUpdateRR());
        test(new ForUpdateS());
        test(new ForUpdateRUretry());
        test(new ForUpdateRCretry());
        test(new ForUpdateRRretry());
        test(new ForUpdateSretry());
    }
    public static void test(String db, String url, String usr, String pwd) throws Exception {
        Class.forName("org.apache.commons.dbcp2.PoolingDriver");
        PoolingDriver driver = (PoolingDriver)DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        PoolableConnectionFactory pcf = new PoolableConnectionFactory(new DriverManagerConnectionFactory(url, usr, pwd), null);
        GenericObjectPool<PoolableConnection> cp = new GenericObjectPool<>(pcf);
        cp.setMinIdle(55);
        cp.setMaxTotal(55);
        pcf.setPool(cp);
        driver.registerPool("mypool", cp);
        test(db);
        driver.closePool("mypool");
    }
    public static void main(String[] args) throws Exception {
        test("MySQL", "jdbc:mysql://localhost/Test", "root", "hemmeligt");
        test("PostgreSQL", "jdbc:postgresql://localhost/Test" , "postgres", "hemmeligt");
        test("IBM DB2", "jdbc:db2://localhost:50000/Test", "arne", "hemmeligt");
        test("MS SQLServer", "jdbc:sqlserver://localhost;database=Test;integratedSecurity=true;", null, null);
    }
}

Raw results:

MySQL:
  SELECT + UPDATE @ No transaction : 21 (687 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Uncommitted : 25 (1768 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Committed : 23 (1598 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Repeatable Read : 21 (674 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Serializable : 22 (763 ms, 22 good updates, 978 errors, 0 retries)
  SELECT + UPDATE @ Read Uncommitted (with retry) : 24 (945 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Committed (with retry) : 20 (653 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Repeatable Read (with retry) : 21 (653 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Serializable (with retry) : 1000 (27305 ms, 1000 good updates, 0 errors, 46800 retries)
  SELECT FOR UPDATE + UPDATE @ No transaction : 42 (921 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Uncommitted : 1000 (7171 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Committed : 1000 (8373 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Repeatable Read : 1000 (7767 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Serializable : 1000 (7030 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Uncommitted (with retry) : 1000 (7741 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Committed (with retry) : 1000 (7732 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Repeatable Read (with retry) : 1000 (7804 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Serializable (with retry) : 1000 (7436 ms, 1000 good updates, 0 errors, 0 retries)
PostgreSQL:
  SELECT + UPDATE @ No transaction : 34 (1161 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Uncommitted : 50 (1164 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Committed : 26 (1060 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Repeatable Read : 187 (433 ms, 187 good updates, 813 errors, 0 retries)
  SELECT + UPDATE @ Serializable : 109 (528 ms, 109 good updates, 891 errors, 0 retries)
  SELECT + UPDATE @ Read Uncommitted (with retry) : 43 (1075 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Committed (with retry) : 52 (1451 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Repeatable Read (with retry) : 1000 (3013 ms, 1000 good updates, 0 errors, 24419 retries)
  SELECT + UPDATE @ Serializable (with retry) : 1000 (2694 ms, 1000 good updates, 0 errors, 20663 retries)
  SELECT FOR UPDATE + UPDATE @ No transaction : 29 (748 ms, 277 good updates, 723 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Uncommitted : 1000 (1612 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Committed : 1000 (1520 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Repeatable Read : 112 (439 ms, 112 good updates, 888 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Serializable : 94 (390 ms, 94 good updates, 906 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Uncommitted (with retry) : 1000 (1600 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Committed (with retry) : 1000 (1446 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Repeatable Read (with retry) : 1000 (2595 ms, 1000 good updates, 0 errors, 17664 retries)
  SELECT FOR UPDATE + UPDATE @ Serializable (with retry) : 1000 (2668 ms, 1000 good updates, 0 errors, 18079 retries)
IBM DB2:
  SELECT + UPDATE @ No transaction : 54 (860 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Uncommitted : 44 (581 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Committed : 49 (527 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Repeatable Read : 22 (26585 ms, 22 good updates, 978 errors, 0 retries)
  SELECT + UPDATE @ Serializable : 22 (22027 ms, 22 good updates, 978 errors, 0 retries)
  SELECT + UPDATE @ Read Uncommitted (with retry) : 41 (472 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Committed (with retry) : 46 (325 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Repeatable Read (with retry) : 1000 (1004834 ms, 1000 good updates, 0 errors, 46720 retries)
  SELECT + UPDATE @ Serializable (with retry) : 1000 (1015904 ms, 1000 good updates, 0 errors, 46707 retries)
  SELECT FOR UPDATE + UPDATE @ No transaction : 1000 (902 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Uncommitted : 1000 (943 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Committed : 1000 (1146 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Repeatable Read : 1000 (1127 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Serializable : 1000 (1535 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Uncommitted (with retry) : 1000 (1248 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Read Committed (with retry) : 1000 (1166 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Repeatable Read (with retry) : 1000 (1253 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT FOR UPDATE + UPDATE @ Serializable (with retry) : 1000 (1232 ms, 1000 good updates, 0 errors, 0 retries)
MS SQLServer:
  SELECT + UPDATE @ No transaction : 39 (566 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Uncommitted : 32 (533 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Committed : 80 (443 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Repeatable Read : 33 (16020 ms, 33 good updates, 967 errors, 0 retries)
  SELECT + UPDATE @ Serializable : 36 (3586 ms, 36 good updates, 964 errors, 0 retries)
  SELECT + UPDATE @ Read Uncommitted (with retry) : 24 (392 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Read Committed (with retry) : 45 (332 ms, 1000 good updates, 0 errors, 0 retries)
  SELECT + UPDATE @ Repeatable Read (with retry) : 1000 (105421 ms, 1000 good updates, 0 errors, 43548 retries)
  SELECT + UPDATE @ Serializable (with retry) : 1000 (104203 ms, 1000 good updates, 0 errors, 42845 retries)
  SELECT FOR UPDATE + UPDATE @ No transaction : not supported
  SELECT FOR UPDATE + UPDATE @ Read Uncommitted : not supported
  SELECT FOR UPDATE + UPDATE @ Read Committed : not supported
  SELECT FOR UPDATE + UPDATE @ Repeatable Read : not supported
  SELECT FOR UPDATE + UPDATE @ Serializable : not supported
  SELECT FOR UPDATE + UPDATE @ Read Uncommitted (with retry) : not supported
  SELECT FOR UPDATE + UPDATE @ Read Committed (with retry) : not supported
  SELECT FOR UPDATE + UPDATE @ Repeatable Read (with retry) : not supported
  SELECT FOR UPDATE + UPDATE @ Serializable (with retry) : not supported

Performance:

So transaction isolation level serializable with retry logic seems to work correct all the time.

That raises the question: why not just always use that.

The answer is that high transaction isolation level impact performance negatively.

The problem is not the overhead of managing the locks. The problem is that if there is contention aka conflicting access to a resources then some operations will be waiting for other operations to complete and release the resources.

Illustration:

locking high level

This does not look so bad, but typical the application and the database are two different processes very often running on diferent systems:

locking low level

so that:

time resource locked = time to execute select + time to execute updates + 4 trips between application and database

Those round trips are very fast if the application server and the database server are standing next to each other.

Those round trips are a performance killer if the application server and the database server are very far from each other.

The math is simple:

distance minimum time one trip
(speed of light)
time 4 trips upper limit on throughput
(due to only distance)
0 km / 0 mi 0 ms 0 ms Infinite
300 km / 200 mi 1 ms 4 ms 250 ops/sec
15000 ops/min
3000 km / 2000 mi 10 ms 40 ms 25 ops/sec
1500 ops/min

The overhead exist no matter how the transaction isolation is implemented:

locking without timeout
treads get blocked and wait until resource becomes available
locking with timeout
threads block, timeout with exception and retries until resource becomes available
multi version rows
threads go ahead, exception at commit due to row having been modified by another thread and retries until success

Let us see an example.

We will use the same test setup as above.

SQL DDL:

CREATE TABLE concdata (id INTEGER NOT NULL, val INTEGER, PRIMARY KEY(id))

SQL DML:

SELECT val FROM concdata WHERE id = ?
UPDATE concdata SET val = ? WHERE id = ?

For without conflict/contention we will operate on a random row among 1000 rows.

For with conflict/contention we will operate on a single row.

Without conflict/contention:

0 ms block 10 ms block 25 ms block 50 ms block 100 ms block
1 thread 75 ops/sec 33 ops/sec 27 ops/sec 16 ops/sec 9 ops/sec
5 threads 311 ops/sec 153 ops/sec 102 ops/sec 66 ops/sec 34 ops/sec
10 threads 479 ops/sec 224 ops/sec 171 ops/sec 120 ops/sec 75 ops/sec
25 threads 581 ops/sec 708 ops/sec 387 ops/sec 280 ops/sec 170 ops/sec
50 threads 979 ops/sec 1089 ops/sec 622 ops/sec 588 ops/sec 322 ops/sec
100 threads 1087 ops/sec 1253 ops/sec 1222 ops/sec 597 ops/sec 485 ops/sec

With conflict/contention:

0 ms block 10 ms block 25 ms block 50 ms block 100 ms block
1 thread 100 ops/sec 35 ops/sec 27 ops/sec 17 ops/sec 9 ops/sec
5 threads 73 ops/sec 35 ops/sec 27 ops/sec 13 ops/sec 9 ops/sec
10 threads 57 ops/sec 32 ops/sec 25 ops/sec 17 ops/sec 9 ops/sec
25 threads 32 ops/sec 30 ops/sec 23 ops/sec 15 ops/sec 9 ops/sec
50 threads 34 ops/sec 30 ops/sec 23 ops/sec 12 ops/sec 8 ops/sec
100 threads 20 ops/sec 25 ops/sec 17 ops/sec 14 ops/sec 7 ops/sec
Theoretical max Infinite 100 ops/sec 40 ops/sec 20 ops/sec 10 ops/sec

We see that:

Test code in Java:

package tx.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLTransientException;
import java.sql.Statement;
import java.util.Random;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

import org.apache.commons.dbcp2.DriverManagerConnectionFactory;
import org.apache.commons.dbcp2.PoolableConnection;
import org.apache.commons.dbcp2.PoolableConnectionFactory;
import org.apache.commons.dbcp2.PoolingDriver;
import org.apache.commons.pool2.impl.GenericObjectPool;

public class PerfTest2 {
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:apache:commons:dbcp:mypool");
    }
    private static Random rng = new Random();
    private static void test(boolean conflict, int delay) {
        try {
            try(Connection con = getConnection()) {
                con.setAutoCommit(false);
                con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                try(PreparedStatement sel = con.prepareStatement("SELECT val FROM concdata WHERE id = ?");
                    PreparedStatement upd = con.prepareStatement("UPDATE concdata SET val = ? WHERE id = ?")) {
                    int ix;
                    if(conflict) {
                        ix = 1;
                    } else {
                        synchronized(rng) {
                            ix = rng.nextInt(1000) + 1;
                        }
                    }
                    boolean done;
                    do {
                        try {
                            sel.setInt(1,  ix);
                            ResultSet rs = sel.executeQuery();
                            rs.next();
                            int val = rs.getInt(1);
                            val = val + 1;
                            Thread.sleep(delay);
                            upd.setInt(1,  val);
                            upd.setInt(2,  ix);
                            upd.executeUpdate();
                            con.commit();
                            done = true;
                        } catch(SQLException ex) {
                            if(ex instanceof SQLTransientException ||
                               ex.getSQLState().equals("40001")) {
                                con.rollback();
                                done = false;
                            } else {
                                throw ex;
                            }
                        }
                    } while(!done);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
    private static final int N = 500;
    private static void test(boolean conflict, int nthreads, int delay) throws SQLException, InterruptedException {
        try(Connection con = getConnection()) {
            try(Statement stmt = con.createStatement()) {
                try {
                    stmt.executeUpdate("DROP TABLE concdata");
                } catch(SQLException ex) { }
                stmt.executeUpdate("CREATE TABLE concdata (id INTEGER NOT NULL, val INTEGER, PRIMARY KEY(id))");
                for(int i = 0; i < 1000; i++) {
                    stmt.executeUpdate("INSERT INTO concdata VALUES(" + (i + 1) + ", 0)");
                }
            }
            long t1 = System.currentTimeMillis();
            ExecutorService es = Executors.newFixedThreadPool(nthreads);
            for(int i = 0; i < N; i++) {
                es.submit(() -> { test(conflict, delay); });
            }
            es.shutdown();
            es.awaitTermination(1, TimeUnit.HOURS);
            long t2 = System.currentTimeMillis();
            System.out.printf("%s - %d threads and %d ms delay : %d ms (%.1f ops/sec)\n", conflict ? "Conflict" : "No conflict",
                                                                                          nthreads, delay,
                                                                                          t2 - t1,
                                                                                          N * 1000.0 / (t2 - t1));
            try(Statement stmt = con.createStatement()) {
                stmt.executeUpdate("DROP TABLE concdata");
            }
        }
    }
    private static void test(String db) throws SQLException, InterruptedException {
        System.out.printf("%s:\n", db);
        for(boolean conflict : new boolean[] { false, true } ) {
            for(int nthread : new int[] { 1, 5, 10, 25, 50, 100 }) {
                for(int delay : new int[] { 0, 10, 25, 50, 100 }) {
                    test(conflict, nthread, delay);
                }
            }
        }
    }
    public static void test(String db, String url, String usr, String pwd) throws Exception {
        Class.forName("org.apache.commons.dbcp2.PoolingDriver");
        PoolingDriver driver = (PoolingDriver)DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        PoolableConnectionFactory pcf = new PoolableConnectionFactory(new DriverManagerConnectionFactory(url, usr, pwd), null);
        GenericObjectPool<PoolableConnection> cp = new GenericObjectPool<>(pcf);
        cp.setMinIdle(105);
        cp.setMaxTotal(105);
        pcf.setPool(cp);
        driver.registerPool("mypool", cp);
        test(db);
        driver.closePool("mypool");
    }
    public static void main(String[] args) throws Exception {
        test("MySQL", "jdbc:mysql://localhost/Test", "root", "hemmeligt");
    }
}

Raw results:

MySQL:
No conflict - 1 threads and 0 ms delay : 6660 ms (75.1 ops/sec)
No conflict - 1 threads and 10 ms delay : 15171 ms (33.0 ops/sec)
No conflict - 1 threads and 25 ms delay : 18702 ms (26.7 ops/sec)
No conflict - 1 threads and 50 ms delay : 32186 ms (15.5 ops/sec)
No conflict - 1 threads and 100 ms delay : 56492 ms (8.9 ops/sec)
No conflict - 5 threads and 0 ms delay : 1607 ms (311.1 ops/sec)
No conflict - 5 threads and 10 ms delay : 3258 ms (153.5 ops/sec)
No conflict - 5 threads and 25 ms delay : 4923 ms (101.6 ops/sec)
No conflict - 5 threads and 50 ms delay : 7595 ms (65.8 ops/sec)
No conflict - 5 threads and 100 ms delay : 14860 ms (33.6 ops/sec)
No conflict - 10 threads and 0 ms delay : 1045 ms (478.5 ops/sec)
No conflict - 10 threads and 10 ms delay : 2235 ms (223.7 ops/sec)
No conflict - 10 threads and 25 ms delay : 2926 ms (170.9 ops/sec)
No conflict - 10 threads and 50 ms delay : 4170 ms (119.9 ops/sec)
No conflict - 10 threads and 100 ms delay : 6676 ms (74.9 ops/sec)
No conflict - 25 threads and 0 ms delay : 860 ms (581.4 ops/sec)
No conflict - 25 threads and 10 ms delay : 706 ms (708.2 ops/sec)
No conflict - 25 threads and 25 ms delay : 1292 ms (387.0 ops/sec)
No conflict - 25 threads and 50 ms delay : 1788 ms (279.6 ops/sec)
No conflict - 25 threads and 100 ms delay : 2950 ms (169.5 ops/sec)
No conflict - 50 threads and 0 ms delay : 511 ms (978.5 ops/sec)
No conflict - 50 threads and 10 ms delay : 459 ms (1089.3 ops/sec)
No conflict - 50 threads and 25 ms delay : 804 ms (621.9 ops/sec)
No conflict - 50 threads and 50 ms delay : 851 ms (587.5 ops/sec)
No conflict - 50 threads and 100 ms delay : 1554 ms (321.8 ops/sec)
No conflict - 100 threads and 0 ms delay : 460 ms (1087.0 ops/sec)
No conflict - 100 threads and 10 ms delay : 399 ms (1253.1 ops/sec)
No conflict - 100 threads and 25 ms delay : 409 ms (1222.5 ops/sec)
No conflict - 100 threads and 50 ms delay : 837 ms (597.4 ops/sec)
No conflict - 100 threads and 100 ms delay : 1030 ms (485.4 ops/sec)
Conflict - 1 threads and 0 ms delay : 4980 ms (100.4 ops/sec)
Conflict - 1 threads and 10 ms delay : 14149 ms (35.3 ops/sec)
Conflict - 1 threads and 25 ms delay : 18465 ms (27.1 ops/sec)
Conflict - 1 threads and 50 ms delay : 29108 ms (17.2 ops/sec)
Conflict - 1 threads and 100 ms delay : 56782 ms (8.8 ops/sec)
Conflict - 5 threads and 0 ms delay : 6811 ms (73.4 ops/sec)
Conflict - 5 threads and 10 ms delay : 14319 ms (34.9 ops/sec)
Conflict - 5 threads and 25 ms delay : 18519 ms (27.0 ops/sec)
Conflict - 5 threads and 50 ms delay : 38733 ms (12.9 ops/sec)
Conflict - 5 threads and 100 ms delay : 57038 ms (8.8 ops/sec)
Conflict - 10 threads and 0 ms delay : 8760 ms (57.1 ops/sec)
Conflict - 10 threads and 10 ms delay : 15582 ms (32.1 ops/sec)
Conflict - 10 threads and 25 ms delay : 19783 ms (25.3 ops/sec)
Conflict - 10 threads and 50 ms delay : 29637 ms (16.9 ops/sec)
Conflict - 10 threads and 100 ms delay : 58719 ms (8.5 ops/sec)
Conflict - 25 threads and 0 ms delay : 15512 ms (32.2 ops/sec)
Conflict - 25 threads and 10 ms delay : 16474 ms (30.4 ops/sec)
Conflict - 25 threads and 25 ms delay : 21384 ms (23.4 ops/sec)
Conflict - 25 threads and 50 ms delay : 34541 ms (14.5 ops/sec)
Conflict - 25 threads and 100 ms delay : 56942 ms (8.8 ops/sec)
Conflict - 50 threads and 0 ms delay : 14728 ms (33.9 ops/sec)
Conflict - 50 threads and 10 ms delay : 16767 ms (29.8 ops/sec)
Conflict - 50 threads and 25 ms delay : 21480 ms (23.3 ops/sec)
Conflict - 50 threads and 50 ms delay : 41124 ms (12.2 ops/sec)
Conflict - 50 threads and 100 ms delay : 59643 ms (8.4 ops/sec)
Conflict - 100 threads and 0 ms delay : 25702 ms (19.5 ops/sec)
Conflict - 100 threads and 10 ms delay : 19908 ms (25.1 ops/sec)
Conflict - 100 threads and 25 ms delay : 28676 ms (17.4 ops/sec)
Conflict - 100 threads and 50 ms delay : 34981 ms (14.3 ops/sec)
Conflict - 100 threads and 100 ms delay : 67168 ms (7.4 ops/sec)

Article history:

Version Date Description
1.0 April 24th 2023 Initial version

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj