What Are ACID Transactions?
In the context of database operations, a transaction refers to an operation that is considered to be a single logical unit of work and aims to leave the underlying system in a consistent state.
Consistency is maintained by ensuring that all the operations are executed upon successful completion, or none are performed when any of the operations fail for one reason or another.
The ACID properties
Database transactions should be compliant to the so-called ACID properties. Such systems are called transactional and ensure that every operation, including read, write or update comply to ACID properties. ACID acronym stands for Atomiciy, Consistency, Isolation and Durability.
Atomicity: **** This property refers to a transaction that is treated as a single unit of work. If any operation defined within the transaction fails, then no operation will be committed and previously executed operations will be rolled back (if any) and the underlying system will be reverted back to its previous state. Atomicity prevents data loss or data corruption since transactions will only be committed if every single statement is applied successfully.
Consistency: This property ensures that all transactions adhere predefined rules such that every transaction is executed in a predictable way that will always leave the underlying system in a consistent state once it gets committed.
Isolation: A transactional database system allows multiple users to interact with it at the same time. This property ensures that concurrent transactions initiated by multiple users won't interfere with each other such that data integrity is maintained. Any operation within a single transaction will therefore not be visible outside of that context, until the transaction is committed.
Durability: This last property ensures that whenever a transaction gets committed it will be saved and its effects will be permanent even in the event of a system failure.
How to write transactions in SQL
Now that we have a basic understanding of the four ACID properties, let's write a Sql transaction to see them in action. Note that I will be using BigQuery syntax which may also work for many other SQL flavours but this is not guaranteed.
In the example shared below, we create a transaction that cosnists of two statements. The first statement will insert a new record into the table mydataset.mytable whilst the second statement will (intentionally) raise an error since it attempts to perform a division by zero.
In the second part of our code snippet, we define the rollback logc that will revert all changes made prior to the failure. Since the SELECT 1/0 statement will raise an execption, the INSERT INTO statement will be rolled back and the new record won't be added to the destination table.
BEGIN
BEGIN TRANSACTION;
INSERT INTO mydataset.mytable VALUES (1, 100, 'pending');
-- Raise an exception by attempting a division by zero
SELECT 1/0;
COMMIT TRANSACTION;
EXCEPTION WHEN ERROR THEN
-- Roll back the transaction if an exception is raised
SELECT @@error.message;
ROLLBACK TRANSACTION;
END;Final Thoughts
ACID properties are among the most fundamental concepts that transactional database systems must adhere to in order to ensure and maintain data integrity, consistency and reliability. Furthermore, these concepts enable multiple users to interact with the underlying systems concurrently without risking these characteristics.
If your day-to-day work requires interaction with transactional database systems it's extremely important to familiarize yourself with these concepts. ACID properties offered by database transactions will ensure data integrity and allow you to handle failures and errors in a proper way that won't affect other operations and users.
