Transaction
Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.
A transaction has 4 key properties, called ACID.
💠A - Atomiticy
Let's consider two accounts: Account-A and Account B.
Current State: Account-A has $1000, Account-B has $550
Let's transfer $100 from Account-A to Account-B. We can divide the whole transaction into 3 steps:
Step I: Check Account-A has at least $100
Step II: Debit $100 from Account-A
Step III: Credit $100 to Account-B
During the transaction, it could be possible that Step I and Step II are successful but Step III is unsuccessful.
So the new state will be: Account-A has $900, Account-B has $550.
Atomicity says this is an unsuccessful transaction so roll the state back to the old state (i.e. Current State).
Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.
🔖Atomicity is all about doing all or nothing.
💠C - Consistency
Consistency means keeping the data in the correct state.
The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.
This can be achieved by applying constraints to our data.
e.g. account balance can not be negative or someone can not withdraw more money than the account has.
🔖Consistency → Correctness of data in the database
💠I - Isolation
Isolation is about - how concurrent transactions will act on each other.
Transactions are protected (isolated) from each other while they are in progress; they cannot interfere with each other or see each other's uncommitted data. This isolation is achieved through the locking mechanism.
Suppose at an identical point in time two persons (i.e. two transactions) are trying to withdraw money from Account-A.
Current State: Account-A has $1000.
Transaction 1: withdraw $500
Transaction 2: withdraw $1000
If both transactions are allowed to be performed then a total of $1500 will be debited from Account-A!
Isolation prevents this situation from happening! It will say, ok, let's not allow both transactions to update the same data at the same time. let execute transactions in sequential orders: Transaction 1 → Transaction 2.
So after Transaction 1, the current state will be: Accout-A has $500
now during Transaction 2, it will check, that the account does not have sufficient balance, so Transaction 2 will fail and data will remain consistent.
This is the highest Isolation level, called SERIALIZABLE. We will see details about different Isolation levels in the next post.
💠D - Durability
After committing a transaction, data should be persisted even if some unwanted (e.g. natural disaster or system crash, etc.) situation arises. Data should not be lost.
The results of transactions are durable: once a commit operation succeeds, the changes made by that transaction are safe from power failures, system crashes, race conditions, or other potential dangers that many non-database applications are vulnerable to.
Normally taking logs and data backup helps us to make our DB durable.
We can create DB replicas to ensure durability in a distributed system. If one node goes down still data can be fetched from other nodes.
References
- MySQL Documentation