SQL Transaction Control Language (TCL) 3 — Questions and Answers
Question 1: Which property of a transaction ensures it completes fully or not at all?
- Atomicity (Correct answer)
- Consistency
- Isolation
- Durability
Correct answer: Atomicity
Atomicity guarantees a transaction is all-or-nothing.
Question 2: In ACID, what does Durability guarantee?
- Committed changes survive system failures (Correct answer)
- Transactions never conflict
- Data is always valid
- Transactions run in order
Correct answer: Committed changes survive system failures
Durability ensures committed data persists even after crashes or power loss.
Question 3: Which TCL-related command sets the isolation level for a transaction in standard SQL?
- SET TRANSACTION (Correct answer)
- SET ISOLATION
- ALTER TRANSACTION
- CONFIGURE TRANSACTION
Correct answer: SET TRANSACTION
SET TRANSACTION ISOLATION LEVEL configures how a transaction interacts with others.
Question 4: Which isolation level allows dirty reads?
- READ UNCOMMITTED (Correct answer)
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
Correct answer: READ UNCOMMITTED
READ UNCOMMITTED permits reading uncommitted changes from other transactions.
Question 5: A COMMIT inside a transaction with multiple savepoints does what to those savepoints?
- Removes all savepoints (Correct answer)
- Keeps them active
- Converts them to checkpoints
- Locks them
Correct answer: Removes all savepoints
COMMIT ends the transaction, so all savepoints are released.
Question 6: Which event typically causes an implicit COMMIT in Oracle?
- Executing a DDL statement (Correct answer)
- Running a SELECT
- Opening a cursor
- Declaring a variable
Correct answer: Executing a DDL statement
DDL statements like CREATE or ALTER trigger an implicit commit of the current transaction.
Question 7: What happens to row locks held by a transaction after COMMIT?
- They are released (Correct answer)
- They remain until ROLLBACK
- They escalate to table locks
- They persist for the session
Correct answer: They are released
COMMIT releases locks acquired during the transaction.
SQL Transaction Control Language (TCL) 3
Which property of a transaction ensures it completes fully or not at all?