SQL Transactions
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:
- Query
TABLE_A
- Insert into
TABLE_B
- 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 aBEGIN
and before aCOMMIT
will be treated as a single unit of workCOMMIT
- defines the end of a transaction. Specifies that all SQL commands in the transaction should be "committed" (i.e., performed) togetherROLLBACK
- undoes all update (i.e., DML) operations performed since the last specifiedCOMMIT
orROLLBACK
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;
Updated 11 months ago