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

Shared lock (S)

Answer

Row share lock (RS)

Answer

Share row exclusive lock (SRX)

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 single transaction holding more locks than the system limit

Answer

A transaction that has been waiting for a lock for more than 30 seconds

Answer

A lock that cannot be released due to a database corruption error

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

Immediately updates all rows matching the WHERE clause

Answer

Creates a read-only snapshot of the selected rows

Answer

Runs an update operation asynchronously in the background

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

Multiple users can update the same row simultaneously without errors

Answer

All reads are automatically serialized to prevent any inconsistency

Answer

Oracle maintains separate physical copies of each table per user session

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 NOWAIT

Answer

FOR UPDATE NO BLOCK

Answer

FOR UPDATE IGNORE LOCKED

Question 6

What type of lock does a plain SELECT statement acquire in Oracle by default?

Accepted Answer

No lock — Oracle uses MVCC with UNDO data for read consistency

Answer

Exclusive lock to ensure consistent reads

Answer

Row share lock on all queried rows

Answer

Shared lock that allows concurrent reads but blocks writes

Question 7

What happens when a transaction issues SELECT FOR UPDATE NOWAIT on a row that is already exclusively locked by another transaction?

Accepted Answer

Oracle immediately raises ORA-00054 (resource busy and acquire with NOWAIT specified)

Answer

Oracle waits indefinitely until the lock is released

Answer

Oracle automatically commits the blocking transaction to free the lock

Answer

Oracle creates a deadlock and terminates the current session