READ UNCOMITTED in PostgreSQL vs MySQL
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:
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:
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:
[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:
[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
$ 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
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
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
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
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
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'
}