What are ACID transactions? #database #mysql #db2 #postgresql #aws #mongodb #nosql #dbms #sql #git

ACID Transactions: A Deep Dive
Introduction
In the context of databases, particularly relational databases, ACID (Atomicity, Consistency, Isolation, and Durability) transactions are crucial to maintaining data integrity, especially when handling concurrent operations. These principles ensure that a system's database operations behave in a predictable and reliable way, even in the presence of failures, ensuring that the data remains consistent.

1. Atomicity
Definition: Atomicity guarantees that a transaction is an indivisible unit of work. It either completes entirely or fails completely. There are no partial transactions; if any part of the transaction fails, all previous operations are rolled back, leaving the system in the state it was in before the transaction began.
Example:
Consider a banking transaction where $100 is transferred from Account A to Account B. The transaction involves two steps: debiting Account A and crediting Account B. Atomicity ensures that both operations either occur or neither does. If the debit happens but the credit fails, the entire transaction is rolled back.
Role: Atomicity prevents incomplete updates to the database, which could lead to data corruption or invalid states.
2. Consistency
Definition: Consistency ensures that a transaction brings the database from one valid state to another, adhering to predefined rules (such as constraints, triggers, and referential integrity). After the transaction, all data must be valid according to the schema and rules of the database.
Example:
If a database has a rule that a balance in any account cannot be negative, the database must ensure that after a transaction, this rule is still true. Even if multiple operations are performed, the integrity constraints must hold after each transaction.
Role: Consistency ensures that any transaction leads the system to a valid state according to the defined rules and constraints, thus maintaining the integrity of the database.
3. Isolation
Definition: Isolation controls the concurrent execution of transactions. It ensures that the intermediate state of a transaction is not visible to other transactions until it is completed. Each transaction should behave as though it is executing alone, even if other transactions are being processed simultaneously.
Levels of Isolation:
Read Uncommitted: Transactions can see uncommitted changes made by other transactions, which can lead to problems like dirty reads.
Read Committed: A transaction only sees data committed before the transaction started, preventing dirty reads.
Repeatable Read: A transaction will see the same data when reading multiple times within the transaction, preventing non-repeatable reads.
Serializable: Transactions are completely isolated, ensuring the highest level of isolation, where they behave as if they are executed sequentially rather than concurrently.
Example:
If two transactions are trying to update the same row in a table, isolation ensures that the second transaction will not see any changes made by the first transaction until it is fully committed, thus preventing phenomena like dirty reads or lost updates.
Role: Isolation manages concurrency to prevent anomalies, such as dirty reads, non-repeatable reads, or phantom reads.
4. Durability
Definition: Durability ensures that once a transaction is committed, the changes are permanent and survive system failures such as power loss, crashes, or hardware malfunctions. The system uses mechanisms like write-ahead logging, checkpoints, and redundancy to safeguard committed data.
Example:
After transferring $100 from Account A to Account B, if the system crashes, the change must still be reflected in both accounts once the system is restored. The changes from the transaction must not be lost even after a system failure.
Role: Durability ensures that committed transactions remain permanent, providing a stable and reliable data environment even in the face of failures.
ACID in Practice: How Databases Implement ACID
Relational Databases: Most relational database management systems (RDBMS) like MySQL, PostgreSQL, Oracle, and Microsoft SQL Server implement ACID properties to varying degrees using transaction logs, locking mechanisms, and journaling.
Techniques:
Write-Ahead Logging (WAL): Used to ensure durability by writing changes to a log before applying them to the database. If a crash occurs, the system can use the log to recover the state of committed transactions.