Transactions and ACID Properties Explained
In database systems, a transaction is a sequence of operations that are executed as a single unit. Transactions are fundamental in ensuring the integrity and consistency of data in databases, especially in scenarios where multiple users or processes are interacting with the same data concurrently.
The key properties of transactions that guarantee data reliability and correctness are often referred to as the ACID properties.
1. What is a Transaction?
A transaction is essentially a logical unit of work that contains one or more operations (such as insert, update, or delete) that need to be executed together. These operations must be performed in a way that ensures the database remains in a consistent state before and after the transaction.
For example:
A bank transaction might involve transferring money from one account to another. This would include two operations: deducting the amount from one account and adding it to another.
A transaction can involve changes to one or more tables, rows, or columns in the database.
2. ACID Properties
The ACID properties are a set of four properties that ensure that database transactions are processed reliably and ensure the integrity of the database, even in the event of errors, power failures, or other system issues. These properties are:
A – Atomicity
Definition: Atomicity refers to the "all-or-nothing" principle. A transaction is atomic, meaning that either all of its operations are completed successfully, or none of them are applied to the database. If any part of the transaction fails, the entire transaction is rolled back, and the database is left unchanged.
Key Point: Even if a transaction contains multiple operations (e.g., multiple INSERT, UPDATE, or DELETE commands), either all of them are executed, or none of them are executed.
Example:
In a bank transfer scenario, if transferring $100 from Account A to Account B involves two steps:
Deduct $100 from Account A.
Add $100 to Account B.
If, for any reason, the second step (adding money to Account B) fails, the entire transaction is rolled back, and no money is transferred.
Why it matters: Atomicity prevents partial updates to the database, which could lead to data inconsistency. If part of the transaction is executed and another part fails, the database would be left in an inconsistent state.
C – Consistency
Definition: Consistency ensures that a transaction takes the database from one valid state to another valid state. It enforces rules such as referential integrity and data validation constraints (e.g., foreign keys, data types, and unique constraints).
Key Point: A transaction must always leave the database in a consistent state, even if it contains errors. If a transaction violates a consistency rule, it will not be allowed to commit (i.e., apply the changes to the database).
Example:
If you have a constraint that no account balance can go below zero, a transaction that attempts to withdraw more money than the account balance would violate the consistency rule, and the transaction would be aborted.
Why it matters: Without consistency, you could end up with data corruption, such as invalid foreign key relationships, negative balances in a bank account, or other invalid data states. Consistency helps maintain the integrity of the database schema.
I – Isolation
Definition: Isolation ensures that transactions are executed independently of one another. Even if multiple transactions are happening concurrently, each transaction should appear to be the only one running in the system. This property protects transactions from interference and ensures they do not see each other’s intermediate states.
Key Point: Isolation can be adjusted to different levels, depending on how much isolation you need. The most common isolation levels in databases are:
Read Uncommitted: Transactions can read uncommitted changes made by other transactions.
Read Committed: Transactions can only read committed changes (i.e., changes that are saved).
Repeatable Read: Transactions can read the same data multiple times without interference from other transactions.
Serializable: The highest level of isolation, where transactions are executed as if they were run serially (one after the other).
Example:
Consider two transactions: Transaction 1 is transferring money from Account A to Account B, and Transaction 2 is transferring money from Account C to Account D. If both transactions are executed concurrently, Isolation ensures that they do not interfere with each other.
Without proper isolation, Transaction 2 might read data that Transaction 1 has not committed yet (e.g., reading a balance from Account A before it is updated).
Why it matters: Isolation prevents issues like "dirty reads" (reading data from uncommitted transactions), "non-repeatable reads" (data changes between reads in the same transaction), and "phantom reads" (new rows appear due to a concurrent insert in another transaction).
D – Durability
Definition: Durability ensures that once a transaction has been committed, it is permanently recorded in the database, even if there is a system failure (e.g., a power outage or crash). Committed data is written to persistent storage (e.g., disk), so it will survive any future failures.
Key Point: Once a transaction is successfully committed, the changes are stored safely and can be retrieved even after a crash or restart.
Example:
After a bank transaction transferring money from Account A to Account B is committed, even if the system crashes immediately afterward, the data (including the deduction from Account A and addition to Account B) will not be lost.
Why it matters: Durability ensures that committed transactions are never lost and can be recovered. Without durability, data loss could occur after a system failure, leading to inconsistencies and corruption.
3. Example of ACID in Action: Bank Transfer
Let's walk through a bank transfer example to illustrate how the ACID properties work:
Transaction: Transfer $100 from Account A to Account B.
Atomicity:
Both operations (deducting $100 from Account A and adding $100 to Account B) must either both succeed or both fail. If there's an error after deducting from Account A but before adding to Account B, the entire transaction will be rolled back, and no change will occur.
Consistency:
The transaction will only succeed if the bank's rules (e.g., Account A cannot go below a balance of $0) are adhered to. If, for instance, Account A does not have sufficient funds, the transaction will be aborted, ensuring the database remains in a valid state.
Isolation:
If another transaction is attempting to withdraw money from Account A concurrently, the isolation property ensures that each transaction is unaware of the other’s intermediate state. It will not read inconsistent or partially updated data, preventing issues like reading an old balance before the transfer.
Durability:
Once the transaction completes and is committed, the changes (the transfer of $100) will persist in the database, even if the system crashes immediately afterward. The committed state is saved to durable storage (disk), ensuring that the data is never lost.
4. Transaction Management in SQL
In SQL, transactions can be managed with the following commands:
START TRANSACTION (or BEGIN in some databases): Initiates a new transaction.
COMMIT: Commits the transaction, making all changes permanent.
ROLLBACK: Rolls back the transaction, undoing any changes made since the last commit.
SAVEPOINT: Creates a point within a transaction to which you can later roll back without affecting the entire transaction.
Example in SQL:
BEGIN;
-- Deducting money from Account A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
-- Adding money to Account B
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
-- Check if the transaction should be committed or rolled back
-- For example, if Account A doesn't have enough balance, rollback
IF (SELECT balance FROM accounts WHERE account_id = 'A') < 0
BEGIN
ROLLBACK;
END
ELSE
BEGIN
COMMIT;
END
In this example:
The BEGIN statement starts a transaction.
After executing the updates, if the condition fails (e.g., insufficient funds), ROLLBACK undoes the changes.
If the condition passes, COMMIT makes the changes permanent.
5. Conclusion
The ACID properties — Atomicity, Consistency, Isolation, and Durability — are fundamental to ensuring that transactions in a database are executed reliably and securely. By maintaining these properties, you can ensure that your database remains consistent, robust, and resilient in the face of errors, concurrency issues, or system failures.
Learn Full Stack JAVA Course in Hyderabad
Read More
One-to-Many and Many-to-Many Mappings in Hibernate
Using JPA and Hibernate for ORM
Connecting Java Applications to MySQL
Visit Our Quality Thought Institute in Hyderabad
Subscribe by Email
Follow Updates Articles from This Blog via Email
No Comments