Mckoi SQL Database
Home / Documentation / FAQ / Support / Download

Transactions Index

  1. Transactions Introduction
  2. Serializable Isolation and the Multi-Version Data Model
  3. Concurrent Commit conflicts
  4. Failure recovery
  5. Using transactions with the Mckoi JDBC driver
  6. Transaction performance
  7. Transaction Examples

1. Transactions Introduction

A transaction is an isolated sequence of queries that can either all be saved to the database or all cancelled and ignored. The COMMIT or ROLLBACK commands are used to complete a transaction. When a transaction is committed any changes within a transaction are made permanent. When a transaction is rolled back all changes are lost and the database reverts back to the state of the last successfully committed transaction.

Mckoi SQL Database supports a highly concurrent transactional model. If concurrent transactions make incompatible changes to the database (for example, the same row is updated by concurrent transactions) the engine will roll back and generate an exception during the commit.

2. Serializable Isolation and the Multi-Version Data Model

There are four transaction isolation levels defined by the SQL standard. Each isolation level provides varying degrees of protection from seeing changes made by concurrent connections. The Mckoi database engine supports the strongest isolation level defined by the standard - TRANSACTION_SERIALIZABLE. This isolation level prevents a transaction from seeing all types of concurrent changes. The Mckoi database engine achieves this through a multi-version data model that efficiently manages and isolates multiple views of the underlying data.

During a transaction the connection sees a version (or snapshot) of the database that is isolated from any changes made by other connections. Additionally, any changes made within the context of a transaction are isolated from the rest of the database. This means that while a transaction is open the view a connection has of the database is blind from changes made by other concurrent connections.

The multi-version data model allows the Mckoi database engine to avoid all inter-transactional table/row locking and deadlock issues. No tables or rows are locked between concurrent transactions. While one transaction is reading from a table, another transaction may update the table at the same time. Any data consistency conflicts (for example, two connections committing a change that deletes the same row from a table) are detected when a transaction is committed.

3. Concurrent Commit conflicts

When a transaction is committed the engine examines all the changes made to the database since the transaction started. If there are any changes that conflict the transaction is rolled back. There are four types of concurrent conflicts that can occur during a commit.

1. Row Change Conflict - A row was deleted or updated by this transaction that was also deleted or updated by another connection after this transaction started.

2. Updated Table Dropped Conflict - A table that was updated by this transaction was dropped/altered by another connection after this transaction started.

3. Table Dropped Conflict - This transaction dropped/altered a table that was modified by another connection after this transaction started.

4. Read Dirty Rows Conflict - This transaction selected rows from a table that was modified by another connection after this transaction started. This conflict only happens if the transaction additionally writes data to a table.

Conflicts 1, 2 and 3 are required checks for maintaining data consistency. Conflict 4 is for strictness to the serializable isolation level. For the reason why we need to detect conflict 4, consider the following concurrent transaction sequence;

    | Transaction 1 Starts |
    | Update Table A       |
    |                      | Transaction 2 Starts |
    | Commit Transaction 1 |                      |
                           | Read Table A         |
                           | Update Table B       |
                           | Commit Transaction 2 |
Transaction 2 will see the view of table A before Transaction 1 started. It reads data from table A and updates table B with information dependant on table A. In a strictly serializable system (where transactions are not able to run concurrently) Transaction 2 should have seen the contents of Table A when transaction 1 ends and so the read of table A and the subsequent write to table B is considered erroneous.

NOTE: You may decide it is not necessary for your application to detect conflict 4 and it can be disabled by including the line 'transaction_error_on_dirty_select=disabled' in your database configuration file. Turning off the detection of this conflict is quite safe and may even offer your application a greater degree concurrency.

Note that a serializable conflict can only happen if the transaction writes data (a read-only transaction of SELECTs can not cause a conflict). Also note that a transaction exception is only generated during a commit. This means the transaction retry code need only repeat on a failure to commit. When a transaction conflict occurs, the transaction is immediately rolled back and any changes are lost. The database will be left in the state of the last successfully committed transaction.

4. Failure recovery

If the database shuts down unexpectedly any uncommitted transactions are automatically rolled back. The next time the database is started it will revert to the state of the last successfully committed transaction. The engine supports a very robust and redundant file structure that provides fast recovery after a system failure.

5. Using transactions with the Mckoi JDBC driver

As defined in the JDBC specification, a connection initially starts with auto-commit mode turned on. Auto-commit mode implies that a COMMIT is performed after each query. To gain the full benefit from transactions this is turned off by calling setAutoCommit(false) in the JDBC java.sql.Connection instance.

The first access to the database implicitly opens a new transaction on the connection. The transaction remains open until a COMMIT or ROLLBACK occurs. To commit a transaction, call commit() in your JDBC Connection instance. To rollback a transaction call rollback() in the JDBC Connection instance (see the examples below).

An advanced feature of the Mckoi JDBC interface is the ability to concurrently evaluate queries inside an open transaction. Concurrent transaction table reads are permitted, but table writes require exclusive access to a table. Inner transaction locking is automatic and deadlock free. Note that the multi-version data model requires no locks between transactions so a table can be read/written to concurrently from multiple transactions, but not concurrently from inside one transaction.

Another advanced feature is that a ResultSet may remain open after a transaction has committed or even rolled back, and the data will remain the same as returned by the originating query (even if some of the data in the ResultSet has not been fetched from the database yet).

6. Transaction performance

The commit command has a small performance overhead. For durability reasons, a commit requires a file synchronize on all updated tables. The consequence of this is that the performance of data manipulation queries can be poor when auto-commit is enabled. INSERT, DELETE and UPDATE are much faster without the commit. A significant gain in performance and safety is found by turning auto-commit off during large batches of these types of queries and committing all the changes in a single transaction with one call to commit.

7. Transaction Examples

The following example demonstrates a transaction sequence and a concurrent conflict;

  // Create two JDBC connections to the database.
  Connection connection1 = ....
  Connection connection2 = ....
  // Create two statements for each connection.
  Statement s1 = connection1.createStatement();
  Statement s2 = connection2.createStatement();
  // Turn auto commit off.
  // Create a table, populate it with two values, and commit
  // the transaction.
  s1.executeQuery("CREATE TABLE Test ( col INTEGER )");
  s1.executeQuery("INSERT INTO Test VALUES ( 10 ), ( 20 )");
  // After the commit, insert a third value into the table
  // from connection1.
  s1.executeQuery("INSERT INTO Test VALUES ( 15 )");
  // This result will contain 3 values.
  ResultSet result1 = s1.executeQuery(
                  "SELECT * FROM Test ORDER BY col");
  // Perform the same query, but from connection 2.
  // This result will contain 2 values.  It won't see the
  // uncommitted insert made by connection 1.
  ResultSet result2 = s2.executeQuery(
                  "SELECT * FROM Test ORDER BY col");
  // Commit the change make on connection 1
  // Perform query on connection 2 again.
  // This result will still only contain 2 values because
  // connection 2 is isolated from all committed changes
  // made to the database.
  result2 = s2.executeQuery(
                  "SELECT * FROM Test ORDER BY col");
  // Insert a value into the table on connection 2
  s2.executeQuery("INSERT INTO Test VALUES ( 25 )");
  // Commit the changes made in connection 2.
  // A transaction error will occur here (conflict type 4)
  // We selected from a table that had concurrent changes
  // made to it.

The following code demonstrates how to retry a failed commit;

  // Create the connection statement
  Statement s1 = connection1.createStatement();
  boolean committed = false;
  while (!committed) {
    // Update the salary of all employees whose name starts
    // with 'T'.
    s1.executeQuery("UPDATE Employee SET salary = salary * 1.25 " +
                    " WHERE name LIKE 'T%' ");
    try {
      // Attempt to commit.
      // Success.
      committed = true;
    catch (SQLException e) {
      // Failed to commit, so retry the query
  // Successfully committed.

Last Updated: Mon Aug 16 00:27:18 PDT 2004
Mckoi SQL Database Copyright © 2000 - 2004 Diehl and Associates, Inc. All rights reserved.