Oracle SQL Oracle SQL Transactions and Concurrency Control 2

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?

Accepted Answer

Exclusive lock (X)

Answer

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?

Accepted Answer

A situation where two or more transactions are each waiting for the other to release locks, creating a circular wait

Answer

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?

Accepted Answer

Locks the selected rows so other transactions cannot modify them until released

Answer

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?

Accepted Answer

Readers see a consistent snapshot of data as of their query start time without blocking writers

Answer

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?

Accepted Answer

FOR UPDATE SKIP LOCKED

Answer

FOR UPDATE SKIP LOCKED causes Oracle to skip any rows currently locked by another transaction, returning only the unlocked rows.

Oracle SQL Practice Test

Oracle SQL Practice Test

Oracle SQL Oracle SQL Transactions and Concurrency Control 2