1Z0-052 · Question #100
User SCOTT executes the following command on the EMP table but has not issued COMMIT, ROLLBACK, or any data definition language (DDL) command: SQL> SELECT ename FROM emp 2 WHERE job='CLERK' FOR…
The correct answer is A. LOCK TABLE emp IN SHARE MODE; B. LOCK TABLE emp IN EXCLUSIVE MODE; E. SELECT ename FROM emp WHERE job='CLERK' FOR UPDATE OF empno. A SELECT FOR UPDATE acquires row-level exclusive locks; any lock request that conflicts with those row locks - share table locks, exclusive table locks, or another FOR UPDATE on the same rows - will wait.
Question
User SCOTT executes the following command on the EMP table but has not issued COMMIT, ROLLBACK, or any data definition language (DDL) command:
SQL> SELECT ename FROM emp 2 WHERE job='CLERK' FOR UPDATE OF empno; SCOTT has opened another session to work with the database instance. Which three operations would wait when issued in SCOTT's second session? (Choose three.)
Options
- ALOCK TABLE emp IN SHARE MODE;
- BLOCK TABLE emp IN EXCLUSIVE MODE;
- CUPDATE emp SET sal=sal*1.2 WHERE job='MANAGER'
- DINSERT INTO emp(empno,ename) VALUES (1289,'Harry');
- ESELECT ename FROM emp WHERE job='CLERK' FOR UPDATE OF empno;
How the community answered
(39 responses)- A67% (26)
- C10% (4)
- D23% (9)
Why each option
A SELECT FOR UPDATE acquires row-level exclusive locks; any lock request that conflicts with those row locks - share table locks, exclusive table locks, or another FOR UPDATE on the same rows - will wait.
LOCK TABLE IN SHARE MODE requires a share table lock (mode 4), which is incompatible with the row-level exclusive locks (mode 3) already held by SCOTT's FOR UPDATE, so the second session waits.
LOCK TABLE IN EXCLUSIVE MODE requires a full exclusive table lock (mode 6), which conflicts with any existing row-level lock regardless of which rows are affected, causing the second session to wait.
The UPDATE targets rows where job='MANAGER', which are entirely different rows from those locked by the FOR UPDATE on job='CLERK' rows; because there is no row-level conflict, the UPDATE proceeds without waiting.
An INSERT adds a new row that does not yet exist and therefore cannot conflict with existing row-level locks held by the FOR UPDATE; the INSERT executes immediately without waiting.
The second FOR UPDATE targets the exact same rows (job='CLERK') already locked by the first session's FOR UPDATE, so Oracle cannot grant the row-level exclusive locks and the second session waits for the first transaction to end.
Concept tested: SELECT FOR UPDATE row locking and lock compatibility
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6
Topics
Community Discussion
No community discussion yet for this question.