ACID Property: Ensuring Data Integrity and Reliability in Database Transactions

In the world of databases, maintaining the integrity and reliability of data is of utmost importance. ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee the reliability and consistency of transactions in a Relational Database Management System (RDBMS). In this article, we will delve into each of the ACID properties and explore their significance in ensuring data integrity.

What is a Transaction?
At its core, a transaction is a logical unit of work consisting of one or more queries. It represents a sequence of operations that should be treated as a single indivisible task. This ensures that either all the changes made within the transaction are applied, or none of them are.

Transaction Lifespan:
A transaction typically follows a specific lifespan, which includes the following key stages:

• Transaction BEGIN: The transaction begins with the initiation of a transaction context. This step marks the starting point of a transaction and sets the boundaries for the subsequent queries to be included in the transaction.
• Transaction COMMIT: Once all the necessary queries have been executed successfully within the transaction, a COMMIT operation is performed. This step finalizes the transaction and makes all the changes made within the transaction permanent, ensuring data consistency.
• Transaction ROLLBACK: In the event of an error or any unexpected condition within the transaction, a ROLLBACK operation is invoked. This action undoes all the changes made within the transaction, reverting the database to its previous state before the transaction began. It ensures data integrity and restores consistency.

It’s important to note that if a transaction ends unexpectedly, such as due to a system crash or failure, the default behavior is to automatically initiate a ROLLBACK operation. This safeguards the data from being left in an inconsistent or incomplete state.

Nature of Transactions:
Transactions are typically used to modify or change data within a database. However, they can also be employed for read-only purposes. For example, when generating a report, you might want to ensure a consistent snapshot of the data based on a specific point in time. This read-only transaction guarantees that the data remains unchanged during the generation of the report.

Example of a Transaction:
Let’s consider an example involving two bank accounts, Account A and Account B. Initially, Account A has a balance of $100, and Account B has a balance of $500.

Suppose we want to transfer $50 from Account A to Account B. The transaction involved in this scenario may consist of the following queries:
1. SELECT balance FROM accounts WHERE account_number = ‘A’ — Fetch the current balance of Account A.
2. UPDATE accounts SET balance = balance — 50 WHERE account_number = ‘A’ — Deduct $50 from Account A.
3. UPDATE accounts SET balance = balance + 50 WHERE account_number = ‘B’ — Add $50 to Account B.

By grouping these queries within a transaction, we ensure that either all the changes are successfully applied (Account A balance decreased and Account B balance increased), or none of them are (in case of a failure, where the transaction is rolled back).

Atomicity

Database transactions are designed to maintain the consistency and integrity of data. One essential property of transactions is atomicity, which guarantees that all queries within a transaction must either succeed or fail together. In this article, we will explore the concept of atomicity, its significance in database operations, and its role in preserving data consistency.

Atomicity ensures that if one query within a transaction fails, all prior successful queries in the same transaction are rolled back, effectively undoing any changes made. This principle prevents the database from being left in an inconsistent state due to partial execution of a transaction. Let’s consider a scenario where a transaction involves multiple queries, such as deducting $100 from Account A and crediting it to Account B.

Suppose, during the execution of this transaction, a query fails, such as an error occurs while crediting Account B. In an atomic transaction, all the prior successful queries (e.g., debiting Account A) would be rolled back. This ensures that the database remains in a consistent state, as if the entire transaction never occurred. Atomicity guarantees that either all the changes made within a transaction are applied successfully or none of them are applied at all.

Atomicity is particularly crucial in situations where there are dependencies between multiple queries within a transaction. For example, if a transaction involves transferring funds between two accounts, it is vital to ensure that both the debit from one account and the credit to the other account occur as an indivisible unit. Atomicity ensures that if any part of the transaction fails, the entire transaction is rolled back, leaving the accounts in their original state.

In addition to handling individual query failures, atomicity also addresses unexpected events that can occur during a transaction. For instance, if the database system crashes before a transaction is committed, an atomic transaction guarantees that all the successful queries within the transaction are rolled back upon system recovery. This ensures that no partial changes are left behind, and the database is restored to a consistent state.

Consider the following scenario: Account A has a balance of $1000, and Account B has a balance of $500. The transaction aims to deduct $100 from Account A and credit it to Account B. If, for some reason, the system crashes after debiting Account A but before crediting Account B, an atomic transaction ensures that the debited amount is rolled back upon system restart. This prevents the loss of data and maintains consistency, as both the accounts’ balances remain unchanged.

Isolation

Isolation is a crucial aspect of database transactions that ensures the consistency and reliability of data. It addresses the question of whether an ongoing transaction can see the changes made by other transactions in progress.

Read phenomena refer to the anomalies or inconsistencies that can arise when multiple transactions are executing concurrently. Let’s explore some common read phenomena and their examples:

1. Dirty Reads:

• Dirty reads occur when a transaction reads uncommitted data from another transaction.

• Example: Suppose the sales table has two rows: Product 1 with a quantity of 10 and a price of $5, and Product 2 with a quantity of 20 and a price of $4. If a transaction reads the sales table while another transaction is still in progress and modifies the quantity or price, the reading transaction may see inconsistent or “dirty” data.

2. Non-repeatable Reads:

• Non-repeatable reads occur when a transaction reads the same data multiple times, but the values change between reads due to updates made by other transactions.

• Example: Consider the sales table mentioned earlier. If a transaction reads the quantity of Product 1 as 10, but another transaction updates it to 15 before the first transaction completes, the first transaction will encounter a non-repeatable read as the value changed between the two reads.

3. Phantom Reads:

• Phantom reads occur when a transaction retrieves a set of rows that satisfy a certain condition, but a new row meeting that condition is added by another transaction before the original transaction completes.

• Example: Continuing with the sales table example, if a transaction retrieves all rows with a quantity less than 15, and while the transaction is in progress, another transaction adds a new row with a quantity of 12, the first transaction will encounter a phantom read as it discovers a new row that did not exist during its initial retrieval.

4. Lost Updates:

• Lost updates occur when two or more transactions attempt to update the same data simultaneously, resulting in one transaction overwriting the changes made by another transaction.

• Example: If two transactions try to update the sales table simultaneously, modifying the quantity or price of the same product, the changes made by one transaction may be lost if the other transaction updates the data before the first transaction commits.

To address these read phenomena and maintain data consistency, DBMS implement different isolation levels for transactions. Let’s explore some commonly used isolation levels:

1. Read Uncommitted:

• No isolation is provided. The transaction can see uncommitted changes made by other transactions.

2. Read Committed:

• Each query within a transaction sees only the committed changes made by other transactions. Uncommitted changes are not visible.

3. Repeatable Read:

• The transaction ensures that once a row is read, it remains unchanged throughout the transaction’s execution. It provides a consistent view of the data during the transaction.

4. Snapshot:

• Each query within a transaction sees a snapshot of the database at the start of the transaction. It provides a consistent view of the data as it existed at the beginning of the transaction.

5. Serializable:

• Transactions are executed as if they were serialized one after the other. It provides the highest level of isolation but can lead to increased concurrency conflicts.

Isolation levels vs read phenomena

wikipedia

Database Implementation of Isolation

Different DBMS implementations handle isolation levels differently based on their concurrency control mechanisms. Two common approaches are pessimistic and optimistic concurrency control:

1. Pessimistic Concurrency Control:

• It involves the use of locks, such as row-level locks, table locks, or page locks, to prevent conflicts and avoid lost updates. It ensures exclusive access to the data being modified.

2. Optimistic Concurrency Control:

• It does not use locks but instead tracks changes made by transactions. If conflicts are detected during commit, the transaction fails and must be retried.

In the Repeatable Read isolation level, the database “locks” the rows it reads, ensuring their consistency throughout the transaction. However, this approach can be costly, especially when dealing with a large number of rows. PostgreSQL addresses this by implementing Repeatable Read as a snapshot, which provides a consistent view of the data at the start of the transaction. This snapshot mechanism in PostgreSQL eliminates the occurrence of phantom reads in the Repeatable Read isolation level.

On the other hand, the Serializable isolation level is typically implemented using optimistic concurrency control. Optimistic concurrency control allows multiple transactions to proceed concurrently without acquiring locks upfront. Instead, it tracks the changes made by each transaction and checks for conflicts during the commit phase. However, it is also possible to implement Serializable isolation pessimistically by using the “SELECT FOR UPDATE” statement, which locks the selected rows explicitly.

Consistency

Consistency is a critical aspect of database management that ensures the reliability and accuracy of data. we will explore the concept of consistency in two key areas: data consistency and consistency in reads. We will also discuss examples and common practices related to achieving consistency in databases.

Consistency in Data:

Consistency in data refers to the state where data adheres to predefined rules and constraints, ensuring its integrity and accuracy. Several factors contribute to data consistency:

1. Defined by the User: Data consistency is typically defined by the user or the application requirements. It involves defining and enforcing rules, constraints, and relationships that data must adhere to, such as data types, ranges, and referential integrity through foreign keys.
2. Referential Integrity: Referential integrity ensures that relationships between entities are maintained correctly. This is often achieved through the use of foreign keys, which enforce constraints between related tables, preventing orphaned or invalid data.
3. Atomicity: Atomicity, as discussed previously, guarantees that all queries within a transaction either succeed or fail together. It ensures that changes to the database are treated as a single, indivisible unit of work.
4. Isolation: Isolation, another property of transactions, ensures that concurrent transactions do not interfere with each other, preserving data integrity and consistency. It prevents anomalies such as dirty reads, non-repeatable reads, phantom reads, and lost updates.

Example of Consistency in Data: Consider a scenario where a database maintains customer records and their corresponding orders. Data consistency in this case would involve ensuring that each order is associated with a valid customer, adhering to referential integrity constraints. Additionally, the atomicity and isolation properties of transactions would ensure that any changes made to customer or order data are consistent and maintain the integrity of the overall database.

Consistency in Reads:

Consistency in reads refers to the behavior of a database system when reading data that has been modified by other concurrent transactions. The level of consistency affects the system as a whole and can vary between different types of databases.

Relational databases and NoSQL databases often have different approaches to consistency:

1. Relational Databases: Relational databases typically aim for strong consistency, ensuring that once a transaction has committed changes, subsequent transactions immediately see those changes. This level of consistency provides a predictable and reliable view of the data. However, achieving strong consistency in distributed or highly concurrent environments can introduce performance challenges.
2. NoSQL Databases: NoSQL databases, on the other hand, often employ an approach called eventual consistency. Under eventual consistency, once a transaction commits changes, there is no immediate guarantee that subsequent transactions will immediately see those changes. Instead, the system eventually propagates and reconciles the changes across all replicas or nodes, providing a consistent view of the data over time.

Example of Consistency in Reads: Consider a distributed NoSQL database used for a social media platform. When a user updates their profile picture, achieving immediate strong consistency across all replicas could be challenging due to the distributed nature of the system. Instead, eventual consistency is employed, where the system ensures that the updated profile picture is eventually propagated to all replicas, providing a consistent view of the user’s profile across the platform.

Eventual Consistency:

Eventual consistency is a consistency model in distributed systems where data replicas are allowed to be temporarily inconsistent. Updates are propagated asynchronously, and there is no guarantee that all replicas will have the latest data at any given moment. Over time, as updates continue to propagate, the system converges to a consistent state.

Only updated in write replica

updated in all the replica as well

Eventual consistency prioritizes availability and partition tolerance, allowing the system to continue operating even in the presence of delays, partitions, or failures. Conflicts may arise during the convergence process, which need to be resolved using mechanisms like versioning or conflict resolution techniques.

Durability

Durability is a crucial property of database systems that guarantees the persistence of changes made by committed transactions. we will explore the concept of durability, its significance in maintaining data integrity, and various techniques employed to achieve durability in database management systems (DBMS).

Durability in Database Transactions:

Durability ensures that once a transaction commits its changes, those changes are permanently stored in a durable and non-volatile storage medium. This means that even in the event of system failures, power outages, or crashes, the committed changes are not lost and can be recovered. Durability is a critical component of the ACID (Atomicity, Consistency, Isolation, Durability) properties that ensure the reliability and consistency of data.

Example of Durability:

Let’s consider an example to understand the importance of durability. Suppose a banking application processes a transaction to transfer $500 from Account A to Account B. Once the transaction is committed, it is crucial that this change is durably stored to prevent any loss of data. Even in the case of a system crash or power failure, the $500 transfer must remain intact and recoverable when the system is restored.

Durability Techniques:

DBMS employ various techniques to achieve durability and ensure that changes made by committed transactions are persistently stored. Let’s explore some commonly used durability techniques:

1. Write-Ahead Log (WAL): The Write-Ahead Log technique is a widely used approach in DBMS. Instead of immediately writing all data to disk, which can be expensive, DBMSs employ a compressed version of changes known as the write-ahead log segments. In this technique, before any data modification, the changes are first recorded in the write-ahead log. Once the changes are durably stored in the log, they can be applied to the actual data on disk. This approach provides a reliable recovery mechanism by allowing the system to replay the log and restore committed changes in the event of a failure.
2. Asynchronous Snapshot: An asynchronous snapshot technique involves taking periodic snapshots of the database state and persisting them to durable storage. These snapshots act as recovery points and enable the system to restore the database to a consistent state in case of failures. Asynchronous snapshots are typically taken at regular intervals or during defined checkpoints to minimize the potential loss of data.
3. Append-Only File (AOF): The Append-Only File technique is commonly used in NoSQL databases. It involves appending all write operations to a log file in the order they occur. This log file, or AOF, can be replayed to restore the database to a consistent state. The AOF ensures that all changes made to the database are durably stored, providing a reliable recovery mechanism.
4. Operating System (OS) Cache: The OS cache is a temporary storage area used by the operating system to buffer write requests. When a write request is received, it is often initially written to the OS cache before being flushed to disk. However, relying solely on the OS cache for durability can be risky. In the event of an OS crash or machine restart, data residing only in the cache can be lost. To address this, the fsync OS command can be used to force immediate writes to disk, ensuring durability. However, the fsync command can be expensive and impact the performance of transaction commits.

In conclusion, the ACID properties (Atomicity, Consistency, Isolation, and Durability) are crucial for maintaining the integrity, reliability, and durability of data in a database management system. They ensure that transactions are treated as indivisible units, data remains consistent according to defined rules, concurrent transactions do not interfere with each other, and changes made by committed transactions are durably persisted. By adhering to these properties, databases can provide a solid foundation for robust and reliable data management in various applications and industries.

Content Credit: This material is adapted from the course “Database Engines Crash Course” available on Udemy. For more information, visit Database Engines Crash Course.