4.4. Transactions

4.4.1. Overview

A transaction is a block of work that will not be visible until a COMMIT has been issued. Or alternatively a ROLLBACK issued will completely undo any changes for the entire block.

Tesseract uses a MVCC (Multiversion Concurrency Control) methodology for record visibility. There is a lot of information on how MVCC works so for the purpose of the following example I will cover the important bits.

Most people that have used a database before expect “autocommit” where any statement that is not explicitly in a transaction is automatically committed. This is different to what the SQL standard states where START TRANSACTION is implicit but the COMMIT is not.

4.4.2. Internals

Following on, rather than surrounding every non-explicit transaction with a START TRANSACTION and COMMIT we define two states; in or not in a transaction.

Each connection maintains its current transaction ID. The TransactionManager maintains a set of all the transaction ID for all the connections that are explicitly in a transaction. Another way to look at it is when we say “in a transaction” we mean the current connection’s transaction ID is in this set.

Each record has two special properties (amongst others) - prefixed with a colon:

  • xid: The transaction ID when the record was created.
  • xex: The transaction ID when the record was deleted. This will be 0 initially.

A record is visible only when all the following are true:

  1. The xid is not in the active transactions.
  2. The xex is 0 OR xex is not in the active transactions.

4.4.3. Collisions

One implicit limitation of transactions is that a connection must see the same database state, no matter how long it has been running or how many modifications to the databases have been made. But at the same time it must guarantee that multiple transactions are editing the most recent version of rows (updating an expired row would have the changes lost).

At the moment tesseract handles collisions by simply throwing the error:

Transaction failed. Will ROLLBACK.

For the transaction that does not hold the lock for the row. This is crude solution as we should wait for that lock to be released but that’s an improvement for another day.

4.4.4. Deadlocks

A deadlock should be impossible given that a transaction will immediately fail and ROLLBACK. When we have a scheduler for transactions then this will be a legitimate concern.