- LPU MCA Semester 1
- CAP570
- Unit 4
CAP570 · Unit 4: Database joins
Transaction control: COMMIT, ROLLBACK and SAVEPOINT
10 practice questions · in the ETE syllabus
Key points
- COMMIT saves changes and ROLLBACK undoes them
- SAVEPOINT lets you roll back part of a transaction
- ROLLBACK TO keeps the work before the savepoint
- Oracle commits implicitly before and after DDL
A transaction is a group of SQL statements that must succeed or fail together. Transaction control statements decide its outcome.
| Statement | Effect |
|---|---|
COMMIT | Makes all changes of the transaction permanent |
ROLLBACK | Undoes all changes since the last commit |
SAVEPOINT name | Marks a point inside the transaction |
ROLLBACK TO name | Undoes only the work done after that savepoint |
RELEASE name | Removes the marker but keeps the work |
Example: a transfer debits account 1 and credits account 2. If both updates are committed, the balances change together; if the transaction is rolled back, neither changes. This is atomicity, the A in ACID. Rolling back to a savepoint keeps everything before it, so with savepoints a and b, ROLLBACK TO b removes only the work after b. A commit is final: a later rollback undoes only the transaction that follows it.
In Oracle, a DDL statement such as CREATE TABLE causes an implicit commit, so earlier work in the transaction is saved before the DDL runs.
Exam trap: ROLLBACK TO savepoint does not end the transaction. Changes made before the savepoint are still pending until you commit.
Try two questions
In Oracle, what happens when a DDL statement such as CREATE TABLE is executed in the middle of a transaction?
- The DDL statement is delayed until the next explicit ROLLBACK is issued
- The current transaction is rolled back before the DDL statement runs
- The DDL statement is executed only inside a savepoint that can be undone
- The current transaction is committed automatically before the DDL statement runs
Show the answer
D. Oracle performs an implicit COMMIT around DDL statements.
Why not A: DDL is not delayed; it causes an implicit commit.
Why not B: An implicit commit saves the work; it does not undo it.
Why not C: DDL statements cannot be undone with a savepoint.
Which property of a transaction is guaranteed by ROLLBACK?
- Isolation: two users can never read the same row
- Atomicity: either all the changes happen or none of them do
- Durability: committed data is stored on exactly two disks
- Consistency: all columns of a table hold the same value
Show the answer
B. Atomicity means a transaction is all or nothing, and rollback provides the nothing.
Why not A: Isolation concerns how concurrent transactions affect each other, not undoing work.
Why not C: Durability concerns surviving failures after commit.
Why not D: Consistency means the database moves between valid states.