What is a SQL Transaction?

A SQL transaction defines a unit of work to be performed against a database, consisting of one or more operations can be performed in a defined sequence and guaranteed to either succeed entirely or fail entirely - meaning all operations either succeed or fail together, and no intermediate state is allowed.

Let's take a look at an example transaction:

BEGIN;
SELECT ID,NAME FROM TABLE_A;
INSERT INTO TABLE_B ('Hello', 'World');
UPDATE TABLE_A SET NAME = 'JOHN' WHERE ID = 33;
COMMIT;

The transaction unit of work resides within the BEGIN and COMMIT statements and has three operations:

1. Query TABLE_A
2. Insert into TABLE_B
3. Update TABLE_A

If for some reason the update in step 3 fails, the insert in step 2 will also fail. All operations will succeed or fail as a single unit.

Transaction management

There are three SQL command keywords that can be used to manage transactions:

• BEGIN - defines the start of a transaction. All SQL commands after a BEGIN and before a COMMIT will be treated as a single unit of work
• COMMIT - defines the end of a transaction. Specifies that all SQL commands in the transaction should be "committed" (i.e., performed) together
• ROLLBACK - undoes all update (i.e., DML) operations performed since the last specified COMMIT or ROLLBACK command

MVCC

Transactions are supported via MVCC (Multiversion Concurrency Control), which is a method of controlling consistency of data accessed concurrently. In other words, MVCC is a means by which multiple users can manipulate the same data at the same time without that data being set to a value not intended by either user. MVCC implements snapshot isolation, which ensures that concurrent transactions will always see a consistent snapshot of the accessed data.

When a transaction begins, it will receive a consistent snapshot of data which it alone can read and update. Any update performed will be checked against other concurrent transactions; if another update is detected on the same data entry, the transaction will fail. Otherwise, the update will become visible only if and when the transaction succeeds and commits.

Enabling MVCC

In order to enable transaction support on a table, you must turn on snapshot isolation. This can be done via the ATOMICITY flag in a CREATE TABLE DDL command:

CREATE TABLE TABLE_A (
  ID int PRIMARY KEY,
  NAME varchar
) WITH "ATOMICITY=TRANSACTIONAL_SNAPSHOT";

Executing a transaction

In order to execute a transaction, you must submit the entirety of the transaction text in a single API request via the DML REST API endpoint. The transaction must be well-formed and include the BEGIN command at the start and COMMIT command at the end.

As an example, the SQL text should look like the following:

BEGIN;
SELECT ID,NAME FROM TABLE_A;
UPDATE TABLE_A SET NAME = 'JOHN' WHERE ID = 33;
UPDATE TABLE_A SET NAME = 'MARY' WHERE ID = 34;
COMMIT;