nerdexam
Oracle

1Z0-052 · Question #232

User Scott has updated the salary of one of the employees in the EMPLOYEES table and has not committed the transaction. What are the two types of locks that this scenario would lead to? (Choose two.)

The correct answer is D. ROW EXCLUSIVE lock for the row being updated F. a shareable table lock for the table containing the row. An uncommitted UPDATE causes Oracle to acquire a row-level exclusive (TX) lock on the modified row and a Row Exclusive (RX) table-level DML lock on the containing table, which is considered a shareable table lock.

Managing Data and Concurrency

Question

User Scott has updated the salary of one of the employees in the EMPLOYEES table and has not committed the transaction. What are the two types of locks that this scenario would lead to? (Choose two.)

Options

  • Anull lock on the row being updated
  • Bnull lock on the table containing the row
  • CROW SHARE lock for the row being updated
  • DROW EXCLUSIVE lock for the row being updated
  • Eshared row-exclusive lock for the row being updated
  • Fa shareable table lock for the table containing the row
  • Gexclusive table-level lock for the table containing the row

How the community answered

(20 responses)
  • C
    15% (3)
  • D
    75% (15)
  • E
    5% (1)
  • G
    5% (1)

Why each option

An uncommitted UPDATE causes Oracle to acquire a row-level exclusive (TX) lock on the modified row and a Row Exclusive (RX) table-level DML lock on the containing table, which is considered a shareable table lock.

Anull lock on the row being updated

Null locks are placeholder locks used in distributed lock management for lock conversion purposes and are not placed on individual rows during a standard single-instance DML update.

Bnull lock on the table containing the row

Oracle does not place a null lock on the table for DML operations; it acquires a Row Exclusive (RX) table-level lock instead.

CROW SHARE lock for the row being updated

A ROW SHARE (RS) table lock is acquired by SELECT ... FOR UPDATE statements, not by a standard UPDATE DML operation.

DROW EXCLUSIVE lock for the row being updatedCorrect

Oracle places a TX (transaction) row-level exclusive lock on every row that is being modified by the UPDATE statement. This lock prevents any other transaction from modifying or locking the same row exclusively until the current transaction either commits or rolls back.

Eshared row-exclusive lock for the row being updated

A share row-exclusive (SRX) table lock is not acquired during a normal uncommitted UPDATE; it applies to specific programmatic locking scenarios such as LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE.

Fa shareable table lock for the table containing the rowCorrect

Oracle also automatically acquires a table-level DML lock - specifically a Row Exclusive (RX) lock - on the table containing the modified row; this is the 'shareable' table lock because multiple concurrent DML sessions can all hold RX locks on the same table simultaneously, while it still prevents DDL operations that require a full exclusive table lock.

Gexclusive table-level lock for the table containing the row

An exclusive table-level lock is acquired only by DDL statements such as DROP TABLE or TRUNCATE TABLE, not by ordinary DML operations like UPDATE.

Concept tested: Oracle DML row-level and table-level locking behavior

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html

Topics

#ROW EXCLUSIVE lock#table lock#DML locks#lock types

Community Discussion

No community discussion yet for this question.

Full 1Z0-052 Practice