Oracle SQL Oracle SQL Transactions and Concurrency Control 2 — Questions and Answers
Question 1: When a DML operation (INSERT, UPDATE, DELETE) is executed on a row in Oracle, what type of lock is acquired on that row?
- Shared lock (S)
- Exclusive lock (X) (Correct answer)
- Row share lock (RS)
- Share row exclusive lock (SRX)
Correct answer: Exclusive lock (X)
DML operations acquire an exclusive (X) row-level lock on modified rows, preventing other transactions from modifying the same rows until the lock is released.
Question 2: What is a deadlock in Oracle SQL?
- A single transaction holding more locks than the system limit
- A situation where two or more transactions are each waiting for the other to release locks, creating a circular wait (Correct answer)
- A transaction that has been waiting for a lock for more than 30 seconds
- A lock that cannot be released due to a database corruption error
Correct answer: A situation where two or more transactions are each waiting for the other to release locks, creating a circular wait
A deadlock occurs when two or more transactions each hold locks that the other needs, creating a circular wait that Oracle detects and resolves by rolling back one transaction.
Question 3: What does the SELECT ... FOR UPDATE statement do in Oracle?
- Immediately updates all rows matching the WHERE clause
- Creates a read-only snapshot of the selected rows
- Locks the selected rows so other transactions cannot modify them until released (Correct answer)
- Runs an update operation asynchronously in the background
Correct answer: Locks the selected rows so other transactions cannot modify them until released
SELECT FOR UPDATE locks the selected rows in exclusive mode, preventing other transactions from modifying them until the current transaction commits or rolls back.
Question 4: Oracle uses Multi-Version Concurrency Control (MVCC). What is the key benefit of this approach?
- Multiple users can update the same row simultaneously without errors
- Readers see a consistent snapshot of data as of their query start time without blocking writers (Correct answer)
- All reads are automatically serialized to prevent any inconsistency
- Oracle maintains separate physical copies of each table per user session
Correct answer: Readers see a consistent snapshot of data as of their query start time without blocking writers
MVCC allows Oracle to provide read-consistent snapshots using UNDO data, so readers see data as it existed at query start time without being blocked by writers.
Question 5: Which SELECT ... FOR UPDATE clause in Oracle causes locked rows to be skipped instead of waiting?
- FOR UPDATE NOWAIT
- FOR UPDATE SKIP LOCKED (Correct answer)
- FOR UPDATE NO BLOCK
- FOR UPDATE IGNORE LOCKED
Correct answer: FOR UPDATE SKIP LOCKED
FOR UPDATE SKIP LOCKED causes Oracle to skip any rows currently locked by another transaction, returning only the unlocked rows.
Question 6: What type of lock does a plain SELECT statement acquire in Oracle by default?
- Exclusive lock to ensure consistent reads
- Row share lock on all queried rows
- No lock — Oracle uses MVCC with UNDO data for read consistency (Correct answer)
- Shared lock that allows concurrent reads but blocks writes
Correct answer: No lock — Oracle uses MVCC with UNDO data for read consistency
Regular SELECT statements in Oracle do not acquire any locks; Oracle uses MVCC with UNDO data to provide read consistency without blocking other operations.
Question 7: What happens when a transaction issues SELECT FOR UPDATE NOWAIT on a row that is already exclusively locked by another transaction?
- Oracle waits indefinitely until the lock is released
- Oracle immediately raises ORA-00054 (resource busy and acquire with NOWAIT specified) (Correct answer)
- Oracle automatically commits the blocking transaction to free the lock
- Oracle creates a deadlock and terminates the current session
Correct answer: Oracle immediately raises ORA-00054 (resource busy and acquire with NOWAIT specified)
SELECT FOR UPDATE NOWAIT immediately raises ORA-00054 if the requested rows are already locked, rather than waiting for the lock to be released.
Oracle SQL Oracle SQL Transactions and Concurrency Control 2
When a DML operation (INSERT, UPDATE, DELETE) is executed on a row in Oracle, what type of lock is acquired on that row?