ACID Properties in DBMS: Transactions and Examples | Part 6

A transaction is a group of database changes that must succeed together or not happen at all. COMMIT completes a successful transaction. ROLLBACK cancels its uncommitted changes. Durability depends on the database configuration and storage guarantees, not the command name alone.

This is Part 6 of the DBMS exam notes. These are general BTech CSE notes, not one university syllabus. Earlier topics are on the Exam Numericals Hub, and the next topic after this one is Part 7: Concurrency and Isolation.

1. Why we need transactions

Think of a bank transfer of 200 rupees from account 1 to account 2. It needs two updates: subtract from account 1, add to account 2. If the system crashes after the first update, 200 rupees disappear. A transaction stops this. Both updates are treated as one unit of work.

2. The three commands

• BEGIN (or START TRANSACTION) starts the unit of work.
• COMMIT finishes the transaction and retains its changes. Whether success means durable storage depends on the configured durability guarantees.
• ROLLBACK cancels all changes since BEGIN. The database goes back to how it was.

3. ACID properties

Letter	Property	Meaning in one line	Transfer example
A	Atomicity	All steps happen, or none do	Both updates apply, or neither
C	Consistency	Rules stay true before and after	Balance never goes below 0; total money is unchanged
I	Isolation	Parallel transactions do not disturb each other	Another user does not see half a transfer
D	Durability	Committed data survives a crash	Committed effects survive crashes within the configured durability and storage assumptions

Exam tip: Recovery mechanisms support atomicity; concurrency control, using locks and/or versions, supports isolation. Both are covered in the next parts.

4. Worked example: the transfer

Table acct(id, bal) with a rule CHECK (bal >= 0). Start: account 1 has 500, account 2 has 300.

BEGIN;
UPDATE acct SET bal = bal - 200 WHERE id = 1;
UPDATE acct SET bal = bal + 200 WHERE id = 2;
COMMIT;

Result: account 1 = 300, account 2 = 500. Total is still 800. I ran this on SQLite to check the numbers.

5. Savepoints: undo only part of the work

A savepoint is a bookmark inside a transaction. You can roll back to it without losing earlier work.

BEGIN;
UPDATE acct SET bal = bal - 100 WHERE id = 2;
SAVEPOINT s1;
UPDATE acct SET bal = bal - 50 WHERE id = 2;
ROLLBACK TO s1;
COMMIT;

Result: the first 100 stays, the 50 is undone. Account 2 goes from 500 to 400. Account 1 stays at 300.

6. When a rule breaks: failure example

Now try to take 900 from account 1, which has only 300. The CHECK rule fails, so the database refuses the update. You then run ROLLBACK, and nothing changes: account 1 = 300, account 2 = 400. This is consistency and atomicity working together. The same check was run in SQLite and gave the error "CHECK constraint failed".

7. Transaction states

A transaction moves through these states: Active (running), Partially committed (last statement done), Committed (saved), Failed (error found) and Aborted (rolled back). Only Committed and Aborted are final. A common exam question asks you to draw this diagram: Active goes to Partially committed, then to Committed; Active or Partially committed can go to Failed, then Aborted.

8. Common mistakes

• Expecting ROLLBACK to undo a completed COMMIT. It cannot roll back that finished transaction; a later compensating transaction is a new change, not the original rollback.
• Mixing up atomicity and durability. Atomicity is about all-or-nothing; durability is about surviving a crash after commit.
• Forgetting that many tools use autocommit, so each single statement is already its own transaction unless you write BEGIN.
• Confusing consistency with isolation. Consistency is about rules; isolation is about parallel users.

9. Practice questions

Q1. Account 1 has 700 and account 2 has 100. A transaction moves 250 from 1 to 2 and commits. Find both balances and the total.

Answer: account 1 = 450, account 2 = 350, total = 800.

Q2. The system crashes after the first UPDATE of a transfer, before COMMIT. What should the recovery do, and which ACID property is that?

Answer: Do not retain the incomplete transfer. A recovery method may undo persisted uncommitted effects, or discard uncommitted versions/pages depending on its design. The required property is atomicity.

Q3. A transaction does BEGIN, subtracts 40 from a balance, sets SAVEPOINT a, subtracts 30, sets SAVEPOINT b, subtracts 20, then ROLLBACK TO a and COMMIT. Starting from 200, what is the final balance?

Answer: 160. Only the first subtraction (40) stays, because rolling back to a undoes the 30 and 20.

10. Complete scratch-database setup

Use a fresh SQLite database. Run this setup once, then the transfer and savepoint examples above in order. Account IDs are unique, and negative or NULL balances are rejected. This small model is for learning, not a production banking system.

CREATE TABLE acct (id INTEGER PRIMARY KEY, bal INTEGER NOT NULL CHECK (bal >= 0));
INSERT INTO acct VALUES (1,500),(2,300);

11. Transfer trace: check both rows and the total

Point	Account 1	Account 2	Total visible in this transaction
Before transfer	500	300	800
After subtracting 200	300	300	600
After adding 200	300	500	800
After COMMIT	300	500	800

The intermediate total inside the transaction is 600. The application must complete both updates or roll back. A balance CHECK constraint alone does not enforce a cross-row total or stop a buggy application from committing after only one valid UPDATE. Consistency requires correct transaction logic and suitable constraints; ACID is not a substitute for that logic.

12. Transaction-state diagram

Success path
Active
↓ last statement finished
Partially committed
↓ commit succeeds
Committed

Failure path
Active or Partially committed
↓ error/failure
Failed
↓ rollback/recovery
Aborted → stop or restart as a new transaction

This is a textbook state model, not a list of exact SQLite UI states. Partially committed means work has finished but commit has not yet completed its required guarantees. A restarted transaction is a new attempt.

13. A statement error is not automatically whole-transaction rollback

Continue after the earlier savepoint example, where balances are 300 and 400. The first subtraction below succeeds. The second violates CHECK. Under SQLite's default ABORT conflict behavior, the failing statement is rejected, but earlier valid statements in the transaction remain pending. Explicitly roll back the whole failed transfer. Other errors or conflict policies can have different behavior.

BEGIN;
UPDATE acct SET bal = bal - 100 WHERE id = 1;
UPDATE acct SET bal = bal - 900 WHERE id = 2;
-- The second UPDATE fails. Stop and run ROLLBACK, not COMMIT.
ROLLBACK;

Before the explicit ROLLBACK, this connection sees 200 and 400. After ROLLBACK, it sees 300 and 400 again. These states were checked by executing the statements individually and catching the expected constraint error. A script that stops at an error may not automatically execute the later ROLLBACK line; the application must handle it.

14. Nested savepoints: execute the practice answer

CREATE TABLE practice (bal INTEGER NOT NULL);
INSERT INTO practice VALUES (200);
BEGIN;
UPDATE practice SET bal = bal - 40;
SAVEPOINT a;
UPDATE practice SET bal = bal - 30;
SAVEPOINT b;
UPDATE practice SET bal = bal - 20;
ROLLBACK TO a;
RELEASE a;
COMMIT;
SELECT bal FROM practice;

Result: 160. ROLLBACK TO a removes the later 30 and 20 changes, but keeps the earlier 40 subtraction. It keeps the transaction active and the named savepoint available; RELEASE a removes that mark before COMMIT.

15. RELEASE is not always final commit

Run this after the nested example:

BEGIN;
SAVEPOINT inner_step;
UPDATE practice SET bal = bal + 10;
RELEASE inner_step;
ROLLBACK;
SELECT bal FROM practice;

Result: 160, not 170. Releasing an inner savepoint inside BEGIN merges its work into the enclosing transaction. The later outer ROLLBACK still undoes it. In SQLite, releasing an outermost savepoint with no enclosing transaction commits it, so always identify the enclosing scope.

16. Compare the failure guarantees

Property	Question it answers	Not a promise of
Atomicity	Do incomplete effects survive?	Correct business logic
Consistency	Are defined invariants respected?	Automatic discovery of missing rules
Isolation	What can concurrent work observe?	Every isolation level being serializable
Durability	Will committed effects survive supported failures?	Protection from every disk loss without backup

17. End-topic questions with short answers

1. Does COMMIT repair a half-written transfer? No. It retains the valid work submitted; the transaction logic must include both sides.
2. Does CHECK bal≥0 preserve the total across accounts? No. It checks each affected balance, not the cross-row sum.
3. What does ROLLBACK TO do? Rewinds changes after the named savepoint without finishing the outer transaction.
4. Does RELEASE of an inner savepoint make changes immune to outer rollback? No, as the tested example shows.
5. Can a later transaction reverse a business action after COMMIT? It may make compensating changes, but that is a new transaction, not rollback of the completed one.
6. Are all errors identical? No. Engine, error type and conflict policy determine whether one statement or the transaction is canceled.
7. Can durability replace backups? No. Media loss and disaster recovery need an appropriate backup/recovery plan.
8. What are the tested balances for Q1? 450 and 350; total 800.

Test scope: both original SQL sequences, expected constraint failure, partial-work rollback, nested savepoint and inner RELEASE examples executed on SQLite. Arithmetic answers checked by program. No actual power-loss or production bank test is claimed. Sources: SQLite transactions and SQLite savepoints.

18. Five-minute revision

• Transaction = one logical unit of work.
• BEGIN, COMMIT, ROLLBACK, SAVEPOINT.
• ACID = Atomicity, Consistency, Isolation, Durability.
• States: Active, Partially committed, Committed, Failed, Aborted.

For a practice problem on schedules, try conflict serializability problems.

DBMS Exam Notes series: Previous: Part 5: Normalization | DBMS series and hub | Next: Part 7: Concurrency