Practice Test GeeksOracle SQL Practice Test
← All Oracle SQL Flashcard Decks

Oracle SQL Transactions and Concurrency Control Flashcards

7 cards from real Oracle SQL practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.

Read the first 7 Oracle SQL Transactions and Concurrency Control flashcards as text

  1. When a DML operation (INSERT, UPDATE, DELETE) is executed on a row in Oracle, what type of lock is acquired on that row?

    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.

  2. What is a deadlock in Oracle SQL?

    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.

  3. What does the SELECT ... FOR UPDATE statement do in Oracle?

    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.

  4. Oracle uses Multi-Version Concurrency Control (MVCC). What is the key benefit of this approach?

    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.

  5. Which SELECT ... FOR UPDATE clause in Oracle causes locked rows to be skipped instead of waiting?

    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.

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

    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.

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

    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.