Ensuring the integrity of the data is a fundamental responsibility of each developer. Fortunately, SQL databases give us the tools to ensure our database stays consistent and accurate.
A crucial scenario to consider is when two SQL queries depend on each other. A typical example is transferring money between two bank accounts. Suppose we have two bank accounts, each holding $1000. Transferring $500 from one account to the other involves two steps:
- reducing the first account’s balance by $500,
- adding $500 to the second account’s balance.
If the first operation fails, data integrity is intact, and the total sum in both accounts remains $2000. The worst-case scenario occurs when only part of the process succeeds:
- we withdraw $500 from the first account,
- we fail to deposit the money into the second account because it was recently closed.
The total in both accounts is now just $1500, with the missing $500 unaccounted for in either account.
The ACID properties of transactions
Thankfully, transactions offer a solution to the above issue. A transaction can contain multiple SQL queries and ensures the following:
Atomicity
A transaction either fully succeeds or completely fails.
Consistency
The transaction transitions the database from one valid state to another.
Isolation
Multiple transactions can be executed simultaneously without losing data consistency. In our example, the second transaction should detect the transferred money in one account but not both.
Durability
Once a transaction is committed, the changes to the database are permanent.
Transactions with PostgreSQL
To initiate a transaction block, we start with the BEGIN statement. Next, we write the queries we want to include in the transaction and finish with the COMMIT keyword to save our changes.
1BEGIN;
2
3UPDATE bank_accounts
4SET balance = 500
5WHERE id = 1;
6
7UPDATE bank_accounts
8SET balance = 1500
9WHERE id = 2;
10
11COMMIT;