ACID properties in DBMS Theory - Step-by-Step Execution

Concept Flow - ACID properties

Start Transaction

↓

Atomicity: All or Nothing

↓

Consistency: Valid State

↓

Isolation: Separate Transactions

↓

Durability: Permanent Changes

↓

End Transaction

This flow shows the four ACID properties that a database transaction must follow from start to end.

Execution Sample

DBMS Theory

BEGIN TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

This code transfers 100 units from account 1 to account 2 as one transaction.

Analysis Table

Step	Action	Atomicity	Consistency	Isolation	Durability	Result
1	Begin transaction	Not violated	Database state unchanged	Transaction isolated	Not applied yet	Transaction started
2	Subtract 100 from account 1	Partial update done	Balance still valid	No other transaction sees this yet	Not applied yet	Account 1 balance reduced by 100
3	Add 100 to account 2	Partial update done	Balance still valid	No other transaction sees this yet	Not applied yet	Account 2 balance increased by 100
4	Commit transaction	All changes applied together	Database remains valid	Changes become visible	Changes saved permanently	Transaction committed successfully
5	If failure before commit	Rollback all changes	Database returns to previous valid state	No partial changes visible	No changes saved	Transaction aborted and rolled back

💡 Transaction ends after commit or rollback, ensuring ACID properties are maintained.

State Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
account1_balance	1000	900	900	900	900
account2_balance	500	500	600	600	600
transaction_state	none	in progress	in progress	committed	committed

Key Insights - 3 Insights

Why must all changes be applied together or not at all?

Because of Atomicity, which ensures partial changes do not leave the database in an inconsistent state. See execution_table steps 2-5.

How does Isolation protect data when multiple transactions run at the same time?

Isolation keeps each transaction's changes hidden from others until committed, preventing conflicts. See execution_table steps 2-4.

What happens if the system crashes after commit?

Durability guarantees that committed changes are saved permanently and will survive crashes. See execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the account1_balance after step 3?

A 800

B 1000

C 900

D 1100

💡 Hint

Check variable_tracker column 'After Step 3' for account1_balance.

At which step does the transaction become permanent and visible to others?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

See execution_table row for 'Commit transaction' and Durability column.

If a failure occurs before commit, what happens to the changes?

A Changes remain partially applied

B Changes are rolled back completely

C Changes become permanent anyway

D Only some changes are saved

💡 Hint

Refer to execution_table step 5 about rollback on failure.

Concept Snapshot

ACID properties ensure reliable database transactions:
- Atomicity: all or nothing changes
- Consistency: valid data state
- Isolation: separate transactions
- Durability: permanent after commit
Transactions start with BEGIN and end with COMMIT or ROLLBACK.

Full Transcript

ACID properties are four key rules that database transactions follow to keep data safe and correct. Atomicity means a transaction's changes all happen together or not at all, so no partial updates. Consistency means the database stays in a valid state before and after the transaction. Isolation means transactions do not interfere with each other while running. Durability means once a transaction commits, its changes are saved permanently, even if the system crashes. For example, transferring money between accounts updates both balances in one transaction. If anything goes wrong before commit, all changes are undone to keep data correct.

Practice

1. Which of the following ACID properties ensures that a transaction is completed fully or not at all?

easy

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Solution

1. Step 1: Understand Atomicity in transactions

   Atomicity means a transaction must be all or nothing; it cannot be partially done.

2. Step 2: Compare with other ACID properties

   Consistency ensures data correctness, Isolation manages concurrent transactions, Durability guarantees data permanence after commit.

3. Final Answer:

   Atomicity -> Option A

4. Quick Check:

   All or nothing = Atomicity [OK]

Hint: All or nothing means Atomicity [OK]

Common Mistakes:

• Confusing Atomicity with Consistency
• Mixing Isolation with Durability
• Thinking Durability means transaction completeness

2. Which ACID property ensures that once a transaction is committed, its changes are permanent even if the system crashes?

easy

A. Consistency

B. Isolation

C. Atomicity

D. Durability

Solution

1. Step 1: Define Durability in ACID

   Durability guarantees that committed data is saved permanently and survives system failures.

2. Step 2: Differentiate from other properties

   Isolation controls transaction interference, Atomicity ensures full completion, Consistency maintains valid data states.

3. Final Answer:

   Durability -> Option D

4. Quick Check:

   Permanent changes after commit = Durability [OK]

Hint: Permanent data after commit means Durability [OK]

Common Mistakes:

• Confusing Durability with Isolation
• Thinking Atomicity means permanence
• Mixing Consistency with Durability

3. Consider a database transaction that updates two tables. If the transaction is interrupted after updating the first table but before the second, which ACID property prevents partial updates?

medium

A. Atomicity

B. Isolation

C. Durability

D. Consistency

Solution

1. Step 1: Analyze partial update scenario

   Partial updates mean some changes are saved while others are not, which is not allowed.

2. Step 2: Identify ACID property preventing partial updates

   Atomicity ensures the transaction is fully completed or fully rolled back, preventing partial changes.

3. Final Answer:

   Atomicity -> Option A

4. Quick Check:

   All or nothing update = Atomicity [OK]

Hint: Partial updates prevented by Atomicity [OK]

Common Mistakes:

• Choosing Durability for partial update
• Confusing Isolation with Atomicity
• Assuming Consistency handles partial updates

4. A developer notices that two transactions running at the same time cause incorrect data results. Which ACID property should be checked to fix this concurrency issue?

medium

A. Atomicity

B. Durability

C. Isolation

D. Consistency

Solution

1. Step 1: Understand concurrency problem

   Concurrent transactions interfering cause data errors due to lack of proper separation.

2. Step 2: Identify ACID property managing concurrency

   Isolation ensures transactions do not affect each other's intermediate states, preventing conflicts.

3. Final Answer:

   Isolation -> Option C

4. Quick Check:

   Concurrency control = Isolation [OK]

Hint: Concurrency issues fixed by Isolation [OK]

Common Mistakes:

• Mixing Isolation with Atomicity
• Thinking Durability controls concurrency
• Confusing Consistency with Isolation

5. A banking system must ensure that money transfer transactions are safe and reliable. Which combination of ACID properties best guarantees that money is neither lost nor duplicated during transfers?

hard

A. Consistency and Isolation

B. Atomicity and Isolation

C. Atomicity and Durability

D. Durability and Consistency

Solution

1. Step 1: Identify requirements for safe money transfer

   Money must not be lost (all steps complete) and no interference between transactions.

2. Step 2: Match requirements to ACID properties

   Atomicity ensures full completion or rollback, Isolation prevents concurrent transaction conflicts.

3. Step 3: Evaluate other options

   Durability ensures permanence but does not prevent duplication; Consistency ensures valid data but not transaction completeness or isolation.

4. Final Answer:

   Atomicity and Isolation -> Option B

5. Quick Check:

   Complete and separate transactions = Atomicity + Isolation [OK]

Hint: Safe transfers need Atomicity + Isolation [OK]

Common Mistakes:

• Choosing Durability alone
• Ignoring Isolation for concurrency
• Confusing Consistency with transaction completeness