What this topic covers
26 subtopics- Serializability5
- ACID4
- Isolation levels3
- Read anomalies3
- COMMIT2
- Deadlock2
- Locking2
- Recoverability2
- Recovery2
- ROLLBACK2
- Schedules2
- Timestamp ordering2
Show all 26 subtopics
- Autocommit1
- Basics1
- Concurrency anomalies1
- Consistency1
- Deadlock prevention1
- Distributed transactions1
- Durability1
- Implicit commit1
- Locking reads1
- MVCC1
- Optimistic locking1
- Safe updates1
- SAVEPOINT1
- Transaction states1
- Beginner 15
- Intermediate 15
- Advanced 15
About this topic
A transaction groups several operations into one unit that either completes fully or not at all. The ACID properties – Atomicity, Consistency, Isolation, Durability – describe what a database promises about transactions, and concurrency control decides how simultaneous transactions may interleave safely. Interviews ask about ROLLBACK behaviour, isolation levels and read anomalies; GATE asks about schedules, serializability, locking and recovery.
Free preview
Sample questions
Try each one first, then open the answer for the explanation and why the other options are wrong.
A money transfer debits one account and then the server crashes before crediting the other. Which ACID property guarantees the debit is undone?
Show answer and explanation
Explanation
Atomicity means all of a transaction's changes happen or none do. On recovery, the incomplete transfer is rolled back using the log.
Why the other options are wrong
- BIsolation is about concurrent transactions not interfering.
- CDurability is about committed data surviving crashes.
- DConsistency is about rules (constraints) holding before and after.
No transaction was started. What is Ravi's balance after the ROLLBACK?
| id | holder | balance |
|---|---|---|
| 1 | Asha | 1000 |
| 2 | Ravi | 500 |
UPDATE accounts SET balance = 0 WHERE id = 2;
ROLLBACK;
SELECT balance FROM accounts WHERE id = 2;| balance |
|---|
| 0 |
| balance |
|---|
| NULL |
| balance |
|---|
| (no rows) |
| balance |
|---|
| 500 |
Show answer and explanation
Explanation
With autocommit on (the default in MySQL and MariaDB), each statement outside an explicit transaction commits immediately. The ROLLBACK has nothing to undo. Always START TRANSACTION before changes you may want to roll back.
Why the other options are wrong
- BThe balance was set to 0, not NULL.
- CThe row still exists.
- DThe UPDATE was already committed.
How many accounts exist after this script?
| id | holder | balance |
|---|---|---|
| 1 | Asha | 1000 |
| 2 | Ravi | 500 |
START TRANSACTION;
INSERT INTO accounts VALUES (3, 'Meena', 300);
CREATE TABLE audit_log (id INT);
ROLLBACK;
SELECT COUNT(*) AS n FROM accounts;| n |
|---|
| 3 |
| n |
|---|
| 1 |
| n |
|---|
| 0 |
| n |
|---|
| 2 |
Show answer and explanation
Explanation
In MySQL and MariaDB, DDL statements such as CREATE TABLE cause an implicit COMMIT. The INSERT was committed before the ROLLBACK ran, so Meena stays. (PostgreSQL supports transactional DDL and would roll both back.)
Why the other options are wrong
- BNothing deleted accounts.
- CThe original two accounts were never touched.
- DThe CREATE TABLE committed the INSERT.