Running Transactions In Splice Machine

Splice Machine is a fully transactional database that supports ACID transactions. This allows you to perform actions such as commit and rollback; in a transactional context, this means that the database does not make changes visible to others until a commit has been issued.

This topic includes brief overview information about transaction processing with Splice Machine, in these sections:

• Transactions Overview
  • ACID Transactions describes what ACID transactions are and why they’re important.
  • MVCC and Snapshot Isolation describes what snapshot isolation is and how it works in Splice Machine.
• Using Transactions
  • Committing and Rolling Back Transaction Changes introduces autocommit, commit, and rollback of transactions.
  • A Simple Transaction Example presents an example of a transaction using the splice> command line interface.
  • Using Savepoints describes how to use savepoints within transactions.

Transactions Overview

A transaction is a unit of work performed in a database; to maintain the integrity of the database, each transaction must:

• complete in its entirety or have no effect on the database
• be isolated from other transactions that are running concurrently in the database
• produce results that are consistent with existing constraints in the database
• write its results to durable storage upon successful completion

ACID Transactions

The properties that describe how transactions must maintain integrity are Atomicity, Consistency, Isolation, and Durability. Transactions adhering to these properties are often referred to as ACID transactions. Here’s a summary of ACID transaction properties:

MVCC and Snapshot Isolation

Splice Machine employs a lockless snapshot isolation design that uses Multiple Version Concurrency Control (MVCC) to create a new version of the record every time it is updated and enforce consistency. Database systems use concurrency control systems to manage concurrent access. The simplest control method is to use locks that make sure that the writer is finished before any reader can proceed; however, this approach can be very slow. With snapshot isolation, each transaction has its own virtual snapshot of the database, which means that multiple transactions can operate concurrently without creating deadlock conditions.

When Splice Machine needs to update an item in the database, it doesn’t actually overwrite the old data value. Instead, it creates a new version with a new timestamp. Which means that readers have access to the data that was available when they began reading, even if that data has been updated by a writer in the meantime. This is referred to as point-in-time consistency and ensures that:

• Every transaction runs in its own transactional context, which includes a snapshot of the database from when the transaction began.
• Every read made during a transaction will see a consistent snapshot of the database.
• A transaction can only commit its changes if they do not conflict with updates that have been committed while the transaction was running.

Reading and Writing Database Values During a Transaction

When you begin a transaction, you start working within a transactional context that includes a snapshot of the database. The operations that read and write database values for your transaction modify your transactional context. When your transaction is complete, you can commit those modifications to the database. The commit of your transaction’s changes succeeds unless a write-write conflict occurs, which happens when your transaction attempts to commit an update to a value, and another update to that value has already been committed by a transaction that started before your transaction.

This means that the following statements are true with regard to reading values from and writing values to the database during a transaction:

• When you read a value during a transaction, you get the value that was most recently set within your transactional context. If you’ve not already set the value within your context, then this is the value that had been most recently committed in the database before your transaction began (and before your transactional context was established).
• When you write a value during a transaction, the value is set within your transactional context. It is only written to the database when you commit the transaction; that time is referred to as the commit timestamp for your transaction. The value changes that you commit then become visible to transactions that start after your transaction’s commit timestamp (until another transaction modifies the value).
• If two parallel transactions attempt to change the same value, then a write-write conflict occurs, and the commit of the transaction that started later fails.

A Snapshot Isolation Example

The following diagram shows an example of snapshot isolation for a set of transactions, some of which are running in parallel:

Here’s a tabular version of the same transactional timeline, showing the values committed in the database over time, with added commentary:

Using Transactions

This section describes using transactions in your database, in these subsections:

• Committing and Rolling Back Transaction Changes introduces autocommit, commit, and rollback of transactions.
• A Simple Transaction Example presents an example of a transaction using the splice> command line interface.
• Using Savepoints describes how to use savepoints within transactions.
• Using Rollback versus Rollback to Savepoint discusses the differences between rolling back a transaction, and rolling back to a savepoint.

Committing and Rolling Back Transaction Changes

Within a transactional context, how the changes that you make are committed to the database depends on whether autocommit is enabled or disabled:

Autocommit is enabled by default. You typically disable autocommit when you want a block of operations to be committed atomically (all at once) instead of committing changes to the database after each operation.

You can turn autocommit on and off by issuing the autocommit on or autocommit off commands at the splice> prompt.

For more information, see these topics in the Command Line Reference section of this book:

• autocommit command
• commit command
• rollback command

A Simple Transaction Example

Here is a simple example. Enter the following commands to see commit and rollback in action:

splice> create table myTbl (i int);
splice> autocommit off;                  - commits must be made explicitly
splice> insert into myTbl  values 1,2,3; - inserted but not visible to others
splice> commit;                          - now committed to the database
splice> select * from myTbl;             - verify table contents
splice> insert into myTbl  values 4,5;   - insert more datasplice> select * from myTbl;             - verify table contents
splice> rollback;                        - roll back latest insertions
splice> select * from myTbl;             - and verify again
...

You can turn autocommit back on by issuing the command: autocommit on;

Using Savepoints

Splice Machine supports the JDBC 3.0 Savepoint API, which adds methods for setting, releasing, and rolling back to savepoints within a transaction. Savepoints give you additional control over transactions by allowing you to define logical rollback points within a transaction, which effectively allows you to specify sub-transactions (also known as nested transactions).

You can specify multiple savepoints within a transaction. Savepoints are very useful when processing large transactions: you can implement error recovery schemes that allow you to rollback part of a transaction without having to abort the entire transaction.

You can use these commands to work with Savepoints:

• create a savepoint with the savepoint command
• release a savepoint with the release savepoint command
• roll a transaction back to an earlier savepoint with the rollback to savepoint command

Example

First we’ll create a table, turn autocommit off, and insert some data into the table. We then create a savepoint, and verify the contents of our table:

splice> CREATE TABLE myTbl(i int);
0 rows inserted/updated/deleted
splice> AUTOCOMMIT OFF;
splice> INSERT INTO myTbl VALUES 1,2,3;
3 rows inserted/updated/deleted
splice> SAVEPOINT savept1;
0 rows inserted/updated/deleted
splice> SELECT * FROM myTbl;
I
-----------
1
2
3

3 rows selected

Next we add new values to the table and again verify its contents:

splice> INSERT INTO myTbl VALUES 4,5;
2 rows inserted/updated/deleted
splice> SELECT * FROM myTbl;
I
-----------
1
2
3
4
55 rows selected

Now we roll back to our savepoint, and verify that the rollback worked:

splice> ROLLBACK TO SAVEPOINT savept1;
0 rows inserted/updated/deleted
splice> SELECT * FROM myTbl;
I
-----------
1
2
3

3 rows selected

And finally, we commit the transaction:

COMMIT;

Using Rollback Versus Rollback to Savepoint

There’s one important distinction you should be aware of between rolling back to a savepoint versus rolling back the entire transaction:

• When you perform a rollback, Splice Machine aborts the entire transaction and creates a new transaction,
• When you perform a rollback to savepoint, Splice Machine rolls back part of the changes, but does not create a new transaction.

See Also

• autocommit command
• commit command
• release savepoint command
• rollback command
• rollback to savepoint command
• savepoint command