Transactions in database

Transactions: What is a database transaction? Explain the ACID properties of transactions and how they ensure data integrity.

Interview Questions Sep 12, 2023 0 246 Add to Reading List

A database transaction is a unit of work in a database management system (DBMS) that follows the principles of the ACID properties to ensure data integrity. ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability, and it represents a set of properties that guarantee reliable database operations, even in the presence of system failures.

Here's a detailed explanation of each ACID property with examples:

Atomicity:
- Atomicity ensures that a transaction is treated as a single, indivisible unit of work. Either all the changes within a transaction are applied to the database, or none of them are applied.
- If any part of a transaction fails (e.g., due to an error or exception), the entire transaction is rolled back to its original state, ensuring that the database remains consistent.
Example: Consider a bank transfer operation, which involves deducting an amount from one account and crediting it to another account. Atomicity ensures that if, for any reason, the credit operation fails (e.g., due to insufficient funds), the debit operation is also rolled back to maintain the integrity of the accounts.
Consistency:
- Consistency guarantees that a transaction brings the database from one consistent state to another. It enforces data integrity constraints, such as primary key, foreign key, and check constraints.
- A consistent state ensures that the database remains in a valid and meaningful state, preserving business rules and relationships.
Example: In a library database, if a user checks out a book, the consistency constraint ensures that the book's availability status changes from "available" to "checked out." The database remains consistent with the library's rules and policies.
Isolation:
- Isolation ensures that concurrent transactions do not interfere with each other. It provides the illusion that each transaction is executing in isolation, even when multiple transactions are executing concurrently.
- Isolation levels (e.g., Read Uncommitted, Read Committed, Repeatable Read, Serializable) control the degree of isolation and trade-off between performance and data consistency.
Example: Consider two users simultaneously booking seats on the same flight. Isolation ensures that one user's transaction does not affect the availability of seats for the other user until the transactions are complete, preventing double bookings or inconsistencies.
Durability:
- Durability guarantees that once a transaction is committed, its changes are permanent and will survive any subsequent system failures (e.g., power outage, system crash).
- To ensure durability, DBMSs typically write transaction changes to a durable storage medium (e.g., disk) before acknowledging the commit.
Example: If a customer completes an online purchase, the Durability property ensures that their payment and order information is stored securely in the database. Even if a server crash occurs immediately after the purchase, the data will not be lost and can be retrieved when the system is restored.

To illustrate how these ACID properties work together, consider a scenario where a bank customer transfers money between two accounts:

Atomicity: The entire transaction is treated as one unit. If the debit operation (withdrawal) succeeds but the credit operation (deposit) fails for any reason (e.g., network issue, system crash), the entire transaction is rolled back. The customer's account balance remains unchanged.
Consistency: The transaction ensures that both accounts' balances are updated correctly and within the bounds of their constraints (e.g., non-negative balances). If the transaction violates these constraints, it is rolled back to maintain consistency.
Isolation: If multiple customers initiate money transfers concurrently, the Isolation property ensures that their transactions do not interfere with each other. Each transaction appears to execute in isolation, preventing conflicts.
Durability: Once the transaction is successfully completed and committed, the changes (new balances) are permanently stored in the database. Even if a system failure occurs immediately after the commit, the changes are preserved.

In summary, ACID properties provide a robust framework for maintaining data integrity in database transactions by ensuring that transactions are atomic, consistent, isolated, and durable. These properties are essential for applications that require reliable and error-resistant data operations, such as financial systems, e-commerce platforms, and more.

Let's use a simplified example of a money transfer between two bank accounts to illustrate the ACID properties in action:

Suppose we have a database with two tables: Accounts and Transactions. The Accounts table stores information about customer accounts, and the Transactions table records all financial transactions.

Table: Accounts

bash

| AccountID | AccountHolder | Balance   |
|-----------|---------------|-----------|
| 101       | Alice         | $1,000.00 |
| 102       | Bob           | $500.00   |

Table: Transactions

bash

| TransactionID | FromAccount | ToAccount | Amount   | Status    |
|---------------|-------------|-----------|----------|-----------|
| 1             | 101         | 102       | $300.00  | Completed |
| 2             | 101         | 102       | $200.00  | Completed |
| 3             | 102         | 101       | $50.00   | Completed |
| 4             | 101         | 102       | $100.00  | Pending   |

Let's analyze a money transfer transaction between Alice (AccountID 101) and Bob (AccountID 102) for $100.00:

Now, let's break down how the ACID properties work during this money transfer example:

Alice initiates a transfer of $100.00 from her account (AccountID 101) to Bob's account (AccountID 102).
The database system first checks the balance of Alice's account to ensure she has sufficient funds.
If Alice has enough funds, the debit operation (withdrawal) is executed, reducing her balance by $100.00.
Then, the credit operation (deposit) is performed, increasing Bob's balance by $100.00.
If either the debit or credit operation fails for any reason (e.g., insufficient funds or a system crash), the entire transaction is rolled back. Alice's account remains unchanged, and Bob's account balance remains the same.
If both operations succeed without violating any constraints, the transaction is committed, and the changes become permanent in the database.
The transaction is recorded in the Transactions table as "Completed," and Alice and Bob's account balances are updated accordingly.

This example demonstrates how the ACID properties ensure the reliability and integrity of financial transactions, even in complex scenarios with multiple database operations and potential system failures.