nerdexam
Oracle

1Z0-062 · Question #252

The HR user updates the salary of one of the employees in the non-partitioned EMPLOYEES table, but does not commit the transaction. Which two types of lock exist in this situation?

The correct answer is D. row level lock on the row being updated E. shared lock on the EMPLOYEES table. When a user updates a row without committing, a row-level lock is acquired on the specific row, and a shared lock is placed on the table to prevent conflicting DDL operations.

Managing Data and Concurrency

Question

The HR user updates the salary of one of the employees in the non-partitioned EMPLOYEES table, but does not commit the transaction. Which two types of lock exist in this situation?

Options

  • Aexclusive lock on the EMPLOYEES table
  • Bnull lock on the row being updated
  • Cnull lock on the EMPLOYEES table
  • Drow level lock on the row being updated
  • Eshared lock on the EMPLOYEES table

How the community answered

(23 responses)
  • A
    4% (1)
  • B
    13% (3)
  • C
    4% (1)
  • D
    78% (18)

Why each option

When a user updates a row without committing, a row-level lock is acquired on the specific row, and a shared lock is placed on the table to prevent conflicting DDL operations.

Aexclusive lock on the EMPLOYEES table

An exclusive lock on the entire table would prevent other users from performing any DML or DDL, which is not the default behavior for a single row update.

Bnull lock on the row being updated

A null lock does not prevent other transactions from accessing or modifying the row; an update operation requires a stronger lock type to ensure data integrity.

Cnull lock on the EMPLOYEES table

A null lock on the table does not prevent other transactions from performing DML or DDL, which is insufficient to protect data consistency during an update operation.

Drow level lock on the row being updatedCorrect

A row-level lock (TX lock) is acquired on the specific row being updated to prevent other transactions from modifying the same row until the current transaction commits or rolls back.

Eshared lock on the EMPLOYEES tableCorrect

A shared lock (TM lock) is placed on the EMPLOYEES table to prevent DDL operations (like dropping the table) from occurring while DML operations are in progress, ensuring data consistency.

Concept tested: Oracle transaction locking mechanisms (row and table locks)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/oracle-transaction-management.html#GUID-8EF92AE8-8FDF-43E0-983C-C04383187F63

Topics

#row locks#table locks#transaction locks#concurrency

Community Discussion

No community discussion yet for this question.

Full 1Z0-062 Practice