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.