BlogtimeException

READ UNCOMITTED in PostgreSQL vs MySQL

Behrang Saeedzadeh Feb 11th, 2019

Some time ago I was thinking about potential challenges of building a news/activity feed similar to Twitter using a relational database. While traditional relational database systems are not suitable for building Twitter-scale feeds, they still are a sensisble choice for systems with relatively small number of users and activities.

Note to self: evaluate VoltDB.

Anyway. In order to reduce contention, when reading items to be displayed in a user’s feed, choosing a "READ UNCOMITTED" isolation level will lead to higher performance and less contention/locking compared to other isolation levels. The problem is that a user might see some items in his feed at a given point in time and see them gone after a refresh due to rollback of their corresponding transactions.

However, some relational databases such as PostgreSQL use Multi Version Concurrency Control (MVCC) to control concurrent access to data. Unlike some other concurrency control models, reads and writes do not block each other. Instead, each transaction sees a snapshot of the database at a particular point in time (e.g. transaction start time).

Consequently, for PostgreSQL, "READ UNCOMITTED" and "READ COMMITTED" isolation levels behave identically. Does that mean PostgreSQL is capable of providing lower or identical latency compared to MySQL while offering better consistency? Let’s try.

READ UNCOMMITTED: MySQL vs PostgreSQL

Episode 1: PostgreSQL

First, let’s verify that our assumption about PostgreSQL’s implementation of “READ UNCOMITTED” is correct.

To do that, we have a program that starts a new transaction and writes a new row into a test table once every 250ms for 60 times but instead of committing it, it terminates by rolling the transaction back:

Listing 1. Rollbacking Populator
connection.setAutoCommit(false);

try (var insertStmt = connection.prepareStatement("INSERT INTO messages(body) VALUES(?)")) {
    for (int i = 0; i < 60; i++) {
        System.out.printf("[%s] Writing item #%d...\n", Thread.currentThread().getName(), i + 1);
        insertStmt.setString(1, "Message " + i);
        insertStmt.executeUpdate();
        Thread.sleep(250);
    }
}

System.out.printf("[%s] Rolling back transaction.\n", Thread.currentThread().getName());
connection.rollback();
connection.close();

We also have another program that, in another transaction with the "DIRTY READ" isolation level, continuously runs a SELECT query against the same table and prints the number of rows it sees:

Listing 2. Dirty Read Poller
connection.setAutoCommit(true);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

try (var select = connection.createStatement()) {
    for (int i = 0; i < 70; i++) {
        System.out.printf("\t\t[%s] Reading all items, attempt #%d...\n", Thread.currentThread().getName(), i + 1);
        var resultSet = select.executeQuery("SELECT * from messages");

        if (!resultSet.isBeforeFirst()) {
            System.out.printf("\t\t[%s] Fetched no items.\n", Thread.currentThread().getName());
        } else {
            int count = 0;
            while (resultSet.next()) {
                count += 1;
            }
            System.out.printf("\t\t[%s] Fetched: %d items.\n", Thread.currentThread().getName(), count);
        }

        Thread.sleep(250);
    }

}

connection.close();

If we run the program, we notice that our poller does not see any of the rows written into the table, despite having an isolation level of TRANSACTION_READ_UNCOMMITTED — all thanks to MVCC:

Listing 3. Sample output - PostgreSQL
[PostgresPopulator] Writing item #1...
[PostgresPopulator] Writing item #2...
[PostgresPopulator] Writing item #3...
[PostgresPopulator] Writing item #4...
        [PostgresDirtyReadPoller] Reading all items, attempt #1...
        [PostgresDirtyReadPoller] Fetched no items.
...
[PostgresPopulator] Writing item #60...
        [PostgresDirtyReadPoller] Reading all items, attempt #57...
        [PostgresDirtyReadPoller] Fetched no items.
[PostgresPopulator] Rolling back transaction.
        [PostgresDirtyReadPoller] Reading all items, attempt #58...
        [PostgresDirtyReadPoller] Fetched no items.
        [PostgresDirtyReadPoller] Reading all items, attempt #59...
        [PostgresDirtyReadPoller] Fetched no items.
        [PostgresDirtyReadPoller] Reading all items, attempt #60...
        [PostgresDirtyReadPoller] Fetched no items.
...

Episode 2: MySQL

If we run the same programs against a MySQL database, we will see an entirely different result:

Listing 4. Sample output - MySQL
[PostgresPopulator] Writing item #1...
[PostgresPopulator] Writing item #2...
[PostgresPopulator] Writing item #3...
[PostgresPopulator] Writing item #4...
        [PostgresDirtyReadPoller] Reading all items, attempt #1...
        [PostgresDirtyReadPoller] Fetched: 4 items.
[PostgresPopulator] Writing item #5...
        [PostgresDirtyReadPoller] Reading all items, attempt #2...
        [PostgresDirtyReadPoller] Fetched: 5 items.
...
[PostgresPopulator] Writing item #60...
        [PostgresDirtyReadPoller] Reading all items, attempt #57...
        [PostgresDirtyReadPoller] Fetched: 60 items.
[PostgresPopulator] Rolling back transaction.
        [PostgresDirtyReadPoller] Reading all items, attempt #58...
        [PostgresDirtyReadPoller] Fetched no items.
        [PostgresDirtyReadPoller] Reading all items, attempt #59...
        [PostgresDirtyReadPoller] Fetched no items.

As MySQL does not use MVCC and due to us setting the isolation level to TRANSACTION_READ_UNCOMMITTED, our poller sees uncomitted records for a period of time. Then as soon as the populator’s transaction is rolled back, our poller no longer sees any records.

Wrap up

We, obviously, have confirmed that PostgreSQL’s MVCC prevents "dirty reads". In an upcoming post, we will compare performance of our PostgreSQL vs MySQL in the “READ UNCOMITTED” isolation level and see how does it stack up against MySQL.

Appendix A: Codes and Scripts

Running PostgreSQL and MySQL using Docker

Listing 5. Running PostgreSQL and MySQL using Docker
$ docker run -p 33306:3306 \
             --name dirty-read-mysql \
             -e MYSQL_ROOT_PASSWORD=password \
             -e MYSQL_DATABASE=dirty-read-test \
             -d mysql:latest

$ docker run -p 55432:5432 \
             --name dirty-read-pg \
             -e POSTGRES_PASSWORD=password \
             -e POSTGRES_DB=dirty-read-test \
             -d postgres:latest

Rollbacking Populator

Listing 6. RollbackingPopulator
package org.behrang.dirtyread;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class RollbackingPopulator implements AutoCloseable, Runnable {

    private final Connection connection;

    private final String dropTableSql;

    private final String createTableSql;

    private final String insertSql;

    public RollbackingPopulator(final String url, final String dropTableSql, final String createTableSql, final String insertSql) throws SQLException {
        this.connection = DriverManager.getConnection(url);
        this.dropTableSql = dropTableSql;
        this.createTableSql = createTableSql;
        this.insertSql = insertSql;
    }

    public void populate() throws SQLException, InterruptedException {
        connection.setAutoCommit(false);

        connection.createStatement().executeUpdate(dropTableSql);
        connection.createStatement().executeUpdate(createTableSql);
        connection.commit();

        try (var insertStmt = connection.prepareStatement(insertSql)) {
            for (int i = 0; i < 60; i++) {
                System.out.printf("[%s] Writing item #%d...\n", Thread.currentThread().getName(), i + 1);
                insertStmt.setString(1, "Message " + i);
                insertStmt.executeUpdate();
                Thread.sleep(250);
            }
        }

        System.out.printf("[%s] Rolling back transaction.\n", Thread.currentThread().getName());
        connection.rollback();
        connection.close();
    }

    @Override
    public void run() {
        try {
            populate();
        } catch (SQLException | InterruptedException e) {
            try {
                close();
            } catch (Exception ignore) {
            }

            throw new RuntimeException(e);
        }
    }

    @Override
    public void close() throws Exception {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }

}

Dirty Read Poller

Listing 7. DirtyReadPoller
package org.behrang.dirtyread;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DirtyReadPoller implements AutoCloseable, Runnable {

    private final Connection connection;

    private final String selectStatement;

    public DirtyReadPoller(final String url, final String selectStatement) throws SQLException {
        this.connection = DriverManager.getConnection(url);
        this.selectStatement = selectStatement;
    }

    public void poll() throws SQLException, InterruptedException {
        Thread.sleep(1_000);

        connection.setAutoCommit(true);
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

        try (var select = connection.createStatement()) {
            for (int i = 0; i < 70; i++) {
                System.out.printf("\t\t[%s] Reading all items, attempt #%d...\n", Thread.currentThread().getName(), i + 1);
                var resultSet = select.executeQuery(selectStatement);

                if (!resultSet.isBeforeFirst()) {
                    System.out.printf("\t\t[%s] Fetched no items.\n", Thread.currentThread().getName());
                } else {
                    int count = 0;
                    while (resultSet.next()) {
                        count += 1;
                    }
                    System.out.printf("\t\t[%s] Fetched: %d items.\n", Thread.currentThread().getName(), count);
                }

                Thread.sleep(250);
            }

        }

        connection.close();
    }

    @Override
    public void run() {
        try {
            poll();
        } catch (SQLException | InterruptedException e) {
            try {
                close();
            } catch (Exception ignore) {
            }

            throw new RuntimeException(e);
        }
    }

    @Override
    public void close() throws Exception {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

PostgreSQL Experiment

Listing 8. PostgresDirtyReadExperiment
package org.behrang.dirtyread.postgres;

import org.behrang.dirtyread.DirtyReadPoller;
import org.behrang.dirtyread.RollbackingPopulator;

public class PostgresDirtyReadExperiment {

    private static final String JDBC_URL =
            "jdbc:postgresql://localhost:55432/" +
                    "dirty-read-test?" +
                    "user=postgres&" +
                    "password=password&" +
                    "ssl=false&" +
                    "sslmode=disable";

    private static final String DROP_STMT =
            "DROP TABLE IF EXISTS messages";

    private static final String CREATE_STMT =
            "CREATE TABLE IF NOT EXISTS messages(" +
                    "id SERIAL PRIMARY KEY, body varchar(255)" +
                    ")";

    private static final String INSERT_STMT =
            "INSERT INTO messages(body) VALUES(?)";

    private static final String SELECT_STMT =
            "SELECT * from messages";

    public static void main(String[] args) throws Exception {
        var populatorThread = new Thread(new RollbackingPopulator(
                JDBC_URL,
                DROP_STMT,
                CREATE_STMT,
                INSERT_STMT
        ));

        populatorThread.setName("PostgresPopulator");

        var pollerThread = new Thread(new DirtyReadPoller(JDBC_URL, SELECT_STMT));
        pollerThread.setName("PostgresDirtyReadPoller");

        populatorThread.start();
        pollerThread.start();

        populatorThread.join();
        pollerThread.join();
    }
}

MySQL Experiment

Listing 9. MySQLDirtyReadExperiment
package org.behrang.dirtyread.mysql;

import org.behrang.dirtyread.DirtyReadPoller;
import org.behrang.dirtyread.RollbackingPopulator;

import java.sql.SQLException;

public class MySQLDirtyReadExperiment {

    private static final String JDBC_URL =
            "jdbc:mysql://localhost:33306/dirty-read-test?" +
                    "user=root&" +
                    "password=password&" +
                    "useSSL=false&" +
                    "allowPublicKeyRetrieval=true&" +
                    "sslMode=DISABLED";

    private static final String DROP_STMT =
            "DROP TABLE IF EXISTS messages";

    private static final String CREATE_STMT =
            "CREATE TABLE IF NOT EXISTS messages(" +
                    "id int NOT NULL AUTO_INCREMENT PRIMARY KEY, body varchar(255)" +
                    ")";

    private static final String INSERT_STMT =
            "INSERT INTO messages(body) VALUES(?)";

    private static final String SELECT_STMT =
            "SELECT * from messages";



    public static void main(String[] args) throws Exception {
        var populatorThread = new Thread(new RollbackingPopulator(
                JDBC_URL,
                DROP_STMT,
                CREATE_STMT,
                INSERT_STMT
        ));

        populatorThread.setName("MySQLPopulator");

        var pollerThread = new Thread(new DirtyReadPoller(JDBC_URL, SELECT_STMT));
        pollerThread.setName("MySQLDirtyReadPoller");

        populatorThread.start();
        pollerThread.start();

        populatorThread.join();
        pollerThread.join();
    }
}

Gradle Script

Listing 10. build.gradle
plugins {
    id 'java'
}

group 'org.behrang.examples'
version '1.0-SNAPSHOT'

sourceCompatibility = 1.11

repositories {
    mavenCentral()
}

dependencies {
    compile group: 'org.postgresql', name: 'postgresql', version: '42.2.5'
    compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.15'

    testCompile group: 'junit', name: 'junit', version: '4.12'
}