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
COMMIT statements and has three operations:
- Insert into
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.
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
BEGINand before a
COMMITwill 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
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.
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";
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 22 days ago