1Z0-062 · Question #251
The HR user executes the following query on the EMPLOYEES table but does not issue COMMIT, ROLLBACK, or any data definition language (DDL) command after that: HR then opens a second session. Which…
The correct answer is A. LOCK TABLE employees IN EXCLUSIVE MODE; C. SELECT job FROM employees WHERE job='CLERK' FOR UPDATE OF empno. This question assesses understanding of Oracle's locking mechanisms and transaction isolation, specifically which operations in a second session will wait when the first session has an open transaction with implicitly acquired exclusive row-level locks.
Question
The HR user executes the following query on the EMPLOYEES table but does not issue COMMIT, ROLLBACK, or any data definition language (DDL) command after that:
HR then opens a second session. Which two operations wait when executed in HR's second session?
Options
- ALOCK TABLE employees IN EXCLUSIVE MODE;
- BINSERT INTO employees(empno,ename) VALUES (1289, `Dick');
- CSELECT job FROM employees WHERE job='CLERK' FOR UPDATE OF empno;
- DSELECT empno,ename FROM employees WHERE job='CLERK';
- EINSERT INTO employees(empno,ename,job) VALUES (2001,'Harry','CLERK);
How the community answered
(41 responses)- A78% (32)
- B2% (1)
- D12% (5)
- E7% (3)
Why each option
This question assesses understanding of Oracle's locking mechanisms and transaction isolation, specifically which operations in a second session will wait when the first session has an open transaction with implicitly acquired exclusive row-level locks.
The first session holds row-level exclusive locks on the EMPLOYEES table, which implicitly acquires a Row Share (RS) table lock. An 'EXCLUSIVE MODE' table lock requested by the second session is incompatible with an existing RS table lock, causing it to wait.
An INSERT operation creates new rows and typically acquires an exclusive lock on the newly inserted row and a table-level shared lock (SS - SubShare), which is compatible with the row share (RS) lock held by the first session, so it generally would not wait.
If the first session holds exclusive row-level locks on rows where 'job='CLERK' (e.g., from a 'SELECT ... FOR UPDATE' or 'UPDATE' statement), the second session's attempt to acquire 'FOR UPDATE' locks on the *same* rows will conflict and therefore wait.
A simple SELECT statement performs a consistent read, accessing data as it existed at the start of the query and does not acquire any locks that would conflict with the first session's row-level exclusive locks.
Similar to choice B, an INSERT operation creates new rows and its locks are compatible with the existing row-level exclusive locks and the associated shared table lock from the first session, so it would not wait.
Concept tested: Oracle transaction isolation and locking
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/sql-transactions.html#GUID-735A122E-A52D-4E38-B590-3F48C4586C61
Topics
Community Discussion
No community discussion yet for this question.