FOR UPDATE 子句会锁定游标结果集选择的行,这样防止数据不被其他会话改动。 其他会话对这些行数据无法加锁,直到事务处理提交或回退为止。
如果另一个会话已对活动集中的行加了锁,那么 SELECT FOR UPDATE 操作一直等待到其它的会话释放这些锁后才继续自己的操作,对于这种情况,当加上 NOWAIT 子句时,如果这些行被另一个会话锁定,则 OPEN 立即退出并报错:。
如下例所示:
+------------+--------+
| FIRST_NAME | SALARY |
+------------+--------+
| Shelley | 12008 |
| William | 8300 |
+------------+--------+
2 rows in set (0.00 sec)
-> CURSOR c_emp IS SELECT first_name, salary
-> FROM employees WHERE department_id=V_deptno FOR UPDATE NOWAIT;
-> BEGIN
-> FOR emp_record IN c_emp LOOP
-> IF emp_record.salary < 9000 THEN
-> UPDATE employees SET salary=9000 WHERE CURRENT OF c_emp;
-> END IF;
-> END LOOP;
-> END;
Query OK, 0 rows affected (0.03 sec)
obclient> SELECT first_name, salary FROM employees WHERE department_id=110;
+------------+--------+
| FIRST_NAME | SALARY |
+------------+--------+
| Shelley | 12008 |
| William | 9000 |
+------------+--------+