OCP-1Z0-052-V8.02-185题

185. 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=MANAGER;

D.INSERT INTO emp(empno,ename) VALUES (1289,'Harry');

E.SELECT ename FROM emp WHERE job='CLERK' FOR UPDATE OF empno;

Answer: ABE  

答案解析：

参考：http://blog.csdn.net/rlhua/article/details/12885143

使用FOR UPDATE OF column_name来限定要更改的列，此时只会锁定特定表中的行。

发出SELECT...FOR UPDATE语句时，关系数据库管理系统(RDBMS) 会自动获得对由SELECT语句标识的所有行的行级互斥锁，因此可暂挂这些记录“仅供你进行更改”。其他人将无法更改这些记录，直至你执行ROLLBACK或COMMIT语句为止。

如果SELECT语句要锁定另一个用户已锁定的某一行，数据库就会一直等待到该行可用为止，然后返回SELECT语句的结果。