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.)
A.
LOCK TABLE emp IN SHARE MODE;
B.
LOCK TABLE emp IN EXCLUSIVE MODE;
C.
UPDATE emp SET sal=sal*1.2 WHERE job=ANAGER?UPDATE emp SET sal=sal*1.2 WHERE
job=?ANAGER?
D.
INSERT INTO emp(empno,ename) VALUES (1289,’Harry’);
E.
SELECT ename FROM emp WHERE job=’CLERK’ FOR UPDATE OF empno;
Use FOR UPDATE OF column_name column is defined to be changed, and at this point will only target specific rows in a table.
When the issue SELECT … FOR UPDATE statement, relational database management system (RDBMS) will automatically have row-level mutex all rows identified by the SELECT statement, so you can hold those records “are for you to make changes.” Other people will not be able to change these records until you execute COMMIT or ROLLBACK statement so far.
If the SELECT statement to lock a row already locked by another user, the database will wait until the line becomes available, and then returns the result of the SELECT statement.
The LOCK TABLE statement allows you to explicitly acquire a shared or exclusive table lock on the specified table.
SELECT FOR UPDATE – allows you to UPDATE and INSERT!!
I don’t understand why the response “A” is right. I thought several sessions could have a shared lock on the same object.
After an exclusive lock(X) no other lock can be applied, neither another exclusive(X) lock nor another share(S) lock.
After an exclusive lock(X) no other lock can be applied, neither another exclusive(X) lock nor another share(S) lock.
— on the same db object