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.
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)- C15% (3)
- D75% (15)
- E5% (1)
- G5% (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.
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.
Oracle does not place a null lock on the table for DML operations; it acquires a Row Exclusive (RX) table-level lock instead.
A ROW SHARE (RS) table lock is acquired by SELECT ... FOR UPDATE statements, not by a standard UPDATE DML operation.
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.
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.
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.
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
Community Discussion
No community discussion yet for this question.