ACID Properties

Every RDBMS vendor gives an ironclad guarantee that all transactions in their RDBMS databases adhere to ACID properties.

A transaction is a single logical unit of work on the data that is stored in a database. You can broadly classify the type of work you can do on the data as:

  • Read
  • Write

Both these operations are done through a transaction in a database. Even though a simple select query does not show the explicit transaction that is spawned behind the scenes by the database, it is still a regular transaction.

In order to maintain data consistency, before and after every transaction, certain rules are applied. These are called ACID properties.

So what exactly are ACID properties?

Transaction

Every transaction on any RDBMS follows ACID rules. Before we dive deep into understanding ACID, let us understand the meaning of a transaction. A transaction is a single logical unit of work that executes one or more of the CRUD (create, read, update, and delete) operations on the data in the various tables.

So when you run your simple SELECT * FROM table query, you have run a transaction on a table. And so is true with your UPDATE, DELETE queries, etc. These transactions are implicit, but you can also start a transaction explicitly. Here is an example:

START TRANSACTION;
SELECT * FROM bank.accounts;
COMMIT;

The above SQL statements begin a transaction explicitly and then run a simple select statement on the accounts table of the bank database. And then the transaction is committed. In fact, for every select statement, an implicit transaction is started and committed behind the scenes. But in this simple example, starting and committing a transaction is not required and does not give us an advantage. However, if your transaction spans multiple tables and if the entire transaction should be considered as one unit of work, then you will need to explicitly start a transaction so that ACID properties are applied to the entire set of CRUD operations as one unit of work.

Let us take an example. Here is a bank database that has a table named Accounts, as shown in the diagram below:

accountID customerID amount
1 1 500
2 2 1500

This table has three columns:

  • accountID - this is the primary key field containing the bank account number.
  • customerID - this field carries the unique ID of the customer. This is a foreign key from the Customers table.
  • amount - this field contains the total account value of the customer.

To keep things simple, this table only contains these three columns. Now, let us assume that the customer with the accountID of '1' is transferring $200 to the customer with the accountID of '2'. So this customer opens his bank account online and puts in a request for a transfer of funds from one account to another.

Behind the scenes in the database, the statements that should be executed to complete this fund transfer are given below:

UPDATE bank.accounts 
SET amount = amount - 200 
WHERE accountID = 1;

UPDATE bank.accounts 
SET amount = amount + 200 
WHERE accountID = 2;

So basically, first the amount of $200 is deducted from account number '1', and the same amount is added to account number '2', keeping it really simple.

However, if the two statements are given in the above form, each of these are atomic operations that are completely independent of each other, which is fine for the most part. However, let us assume that after the first update statement is executed, there is a system failure due to some power outage, and the second update could not be finished, although the first one updated successfully. When the system comes back up, Customer1 will see that his bank account has been debited $200, but Customer2 is saying that his account is not showing the transfer! This is a problem, and the bank management should gear up to hear an earful from Customer 1. This has led to inconsistencies in the database. To avoid these kinds of issues, you should enclose both these updates within a transaction, and your modified SQL queries will be:

START TRANSACTION;
UPDATE bank.accounts 
SET amount = amount - 200 
WHERE accountID = 1;

UPDATE bank.accounts 
SET amount = amount + 200 
WHERE accountID = 2;
COMMIT;

When you enclose any SQL statement between a START TRANSACTION and COMMIT, each of the SQL statements are no longer treated as atomic; the entire set of statements are considered atomic. So in this situation, when the first update finishes and when there is a system outage, the transaction could not reach the commit statement, because of which the transaction automatically aborts. Even though the update statement was successfully executed, it will be rolled back to its original value as it is now following the principle of 'all or nothing' for the entire set of SQL queries, as they are part of one transaction.

So we are ready to formally understand the ACID rules of a transaction:

Atomicity: The entire set of SQL statements in a transaction takes place at once or doesn’t happen at all. There is no midway, i.e., some SQL being executed which are part of a transaction and others not yet executed. Such a transaction never happens in any ACID-compliant database. Each transaction is considered as one unit and either runs to completion or is not executed at all. A transaction uses one of the two operations during its execution:

  • Abort: If a transaction aborts, changes made to the database are not visible. If not committed, it automatically aborts. You can also roll back using the ROLLBACK TRANSACTION SQL statement.
  • Commit: Using a COMMIT statement, a transaction can be completed, and then the changes made by this transaction are visible to other queries and transactions.

Consistency: The database is consistent before and after the transaction. It refers to the correctness of the data. In the above bank transfer example, 200ismovedfromoneaccounttoanother,buttherewasnosituationinwhich200 is moved from one account to another, but there was no situation in which200 got lost or magically arrived from thin air. At every point, you can show how the money moved. So the money was neither created nor destroyed but was moved from one account to another, thereby maintaining the consistency of the data.

Isolation: This property ensures that multiple transactions can occur concurrently without leading to inconsistency in the database state. So, multiple different customers can transfer funds, withdraw or deposit funds, or just read their account without any issues, even if there are many simultaneous transactions going on. Transactions do not step on each other. Transient changes within a transaction will not be visible to any other transaction and are only available to other transactions after the COMMIT is executed. This property also ensures that transactions occurring concurrently will result in a state that is equivalent to a state achieved if these transactions were executed serially in some order. If, however, multiple transactions are trying to access the same data, then the data row/table is locked, and only one transaction is allowed to manipulate the data at a time. Other transactions have to wait till the transaction that picked up the lock commits, and at that time, the lock is released for the other transaction that is next in line for the same data.

Durability: This property ensures that once the transaction has completed execution, the database state is persisted and is never lost, even in the case of any type of system failure.

Deadlock: When there is a circular dependency between 2 or more transactions that are holding different locks, a deadlock occurs. Most databases automatically detect and resolve them by rolling back the cheapest of the two transactions. The cheapest transaction is the transaction that has written the fewer bytes to the transaction log.