ACID properties in DBMS

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These properties are crucial in the context of database management systems (DBMS) to ensure the reliability and integrity of transactions. Let’s explore each of these properties in more detail:

Atomicity:

• Definition: Atomicity ensures that a transaction is treated as a single, indivisible unit of work. Either all the changes made by the transaction are committed to the database, or none of them are.
• Scenario: Funds Transfer in a Bank
• Transaction Steps:

1. Deduct funds from Account A.
2. Credit the same amount to Account B.

• Atomicity Ensures:
• If step 1 succeeds but step 2 fails (e.g., due to a system crash), atomicity ensures that the entire transaction is rolled back.
• If step 1 fails, no changes are made to either account.

Example: Consider a funds transfer between two bank accounts. If the debit from one account occurs successfully but the credit to the other account fails, atomicity ensures that the entire transaction is rolled back, and both accounts return to their original state.

Consistency:

• Definition: Consistency ensures that a transaction brings the database from one valid state to another. The database must adhere to integrity constraints defined on the data.
• Scenario: Unique ID Constraint in a Customer Database
• Constraint:
• Every customer must have a unique customer ID.
• Transaction Steps:

1. Add a new customer with a specified ID.
2. Update an existing customer with the same ID.

• Consistency Ensures:
• If the second step violates the unique ID constraint, the entire transaction is rolled back.
• The database remains in a consistent state with respect to the defined constraints.

Example: If a database has a constraint that all customers must have a unique ID, any transaction violating this constraint would be considered inconsistent.

Isolation:

• Definition: Isolation ensures that the execution of one transaction is isolated from the effects of other transactions. Even though multiple transactions may be executed concurrently, the final outcome should be as if they were executed in some sequential order.

Scenario: Concurrent Transactions on Bank Accounts

• Transactions:

1. Transaction A: Transfer funds from Account X to Account Y.
2. Transaction B: Withdraw funds from Account Y.

• Isolation Ensures:
• Even if A and B are executed concurrently, the final state of the accounts is consistent and reflects the correct balance.
• Isolation prevents interference between the two transactions, ensuring that their effects are as if they were executed in some sequential order.

Example: Two transactions are updating the same bank account concurrently. Isolation ensures that the final state of the account reflects the changes made by both transactions in a way that is consistent and doesn’t create conflicts.

Durability:

• Definition: Durability ensures that once a transaction is committed, its effects persist in the database, even in the face of system failures (e.g., power loss, crashes). The changes made by a committed transaction should be permanent.
• Scenario: Order Processing in an E-commerce System
• Transaction Steps:

1. The customer places an order.
2. System updates inventory.
3. The system processes the payment.

• Durability Ensures:
• Once the payment is processed and the transaction is committed, the changes (order placement, inventory update, payment processing) persist even if the system crashes afterward.
• Upon system recovery, the database reflects the state at the point of the last committed transaction.

Example: If a user is notified that a transaction is successful, the system should guarantee that the changes made by that transaction are durable, and a subsequent system failure will not result in data loss.