覆盖默认的锁

默认，Oracle数据库会自动锁住数据结构，这允许不同的应用程序在互不破坏他人数据和互不协调的情况下写相同的数据结构。

如果需要在事务中排除访问，可以覆盖默认的锁：

LOCK TABLE，显式地锁住整个表

带有FOR UPDATE子句的SELECT，显式地锁住表的特定行。

（1）LOCK TABLE语句

该语句显式地以指定的锁模式锁定一个或多个表，这样就可以共享或拒绝对它们的访问。

锁模式决定了表上可以应用的锁。例，许多用户可以同时获得行共享锁，但只有一个用户可以获得排它锁。当一个用户获得了表上的排它锁，其它用户不可以进行insert、update、delete表中的行。

表锁不会阻止其它用户查询表，而且一个查询永远也不会获得表锁。只有当两个不同的事务试图修改相同的行的时候，这时一个事务要等待另一个事务结束。LOCK TABLE语句指定等待多长时间等待另一个事务结束。

当获得表锁的事务提交或回滚之后，表锁被释放。

（2）SELECT FOR UPDATE语句和FOR UPDATE光标

带有FOR UPDATE子句的SELECT语句，会选择结果集的行并锁定它们。SELECT FOR UPDATE允许你将更新基于行中已经存在的值，因为它可以确保在你更新它们之前没有其它用户可以改变这些值。也可以使用SELECT FOR UPDATE来锁住你不想更新的行。

在使用混合列压缩（Hybrid Columnar Compression，HCC）的表中DML语句锁住的是压缩单元，而不是行。HCC是Oracle某个Oracle存储系统的功能。

当SELECT FOR UPDATE与一个显式光标相关联，则该光标被称为FOR UPDATE光标。只有FOR UPDATE光标可以出现在UPDATE或DELETE语句的CURRENT OF 子句中。CURRENT OF 子句是PL/SQL对UPDATE或DELETE语句的where子句的扩展，限制语句到光标的当前行。

-- FOR UPDATE光标出现在UPDATE语句的CURRENT OF 子句中

DECLARE

my_emp_id NUMBER(6);

my_job_id VARCHAR2(10);

my_sal NUMBER(8,2);

-- FOR UPDATE光标

CURSOR c1 IS

SELECT employee_id, job_id, salary

FROM employees FOR UPDATE;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO my_emp_id, my_job_id, my_sal;

IF my_job_id = 'SA_REP' THEN

UPDATE employees

SET salary = salary * 1.02

WHERE CURRENT OF c1; --出现CURRENT OF子句

END IF;

EXIT WHEN c1%NOTFOUND;

END LOOP;

END;

当SELECT FOR UPDATE查询多个表的时候，它只锁定列出现在FOR UPDATE子句中的行。

DECLARE

CURSOR c1 IS

SELECT last_name, department_name

FROM employees, departments

WHERE employees.department_id = departments.department_id

AND job_id = 'SA_MAN'

FOR UPDATE OF salary; --只锁定了salary

BEGIN

NULL;

END;

--结论：该查询查询了2个表employees和departments,在FOR UPDATE 子句中只出现在salary列

-- salary列是employees表的列,而不是departments表的列,因此只会锁定employees表中的行;

-- 如果FOR UPDATE子句包含department_id或manager_id，它们出现在2个表中，则会锁定

-- 2个表中的行。

当打开FOR UPDATE光标时，结果集中的行是被锁定的。当你提交或回滚了整个事务，行被解锁。当行被解锁之后，你就不能从FOR UPDATE光标获取数据了。

DROP TABLE emp;

CREATE TABLE emp AS SELECT * FROM employees;

DECLARE

CURSOR c1 IS

SELECT * FROM emp

FOR UPDATE OF salary

ORDER BY employee_id;

emp_rec emp%ROWTYPE;

BEGIN

OPEN c1;

LOOP

--第2次循环的时候失败

FETCH c1 INTO emp_rec;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE ('emp_rec.employee_id = ' ||

TO_CHAR(emp_rec.employee_id));

UPDATE emp

SET salary = salary * 1.05

WHERE employee_id = 105;

COMMIT; -- 释放锁（将该语句移动到循环之外就可以了）

END LOOP;

END;

（3）使用ROWID伪列模拟CURRENT OF 子句

查询每一行的rowid到一个urowid变量中，然后在后面的update或delete操作中使用rowid来识别当前的行。

注意：当更新使用HCC技术压缩表中的行时，则行的rowid会改变。

注意：因为如果没有FOR UPDATE语句锁定已经获取的行，其它用户可能无意识地覆盖你的修改。

注意：为了读一致性而需要的空间直到光标被关闭时才会被释放，这会降低大的更新的处理速度。

DROP TABLE emp;

CREATE TABLE emp AS SELECT * FROM employees;

DECLARE

CURSOR c1 IS

SELECT last_name, job_id, rowid

FROM emp; -- 没有FOR UPDATE子句

my_lastname employees.last_name%TYPE;

my_jobid employees.job_id%TYPE;

my_rowid UROWID;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO my_lastname, my_jobid, my_rowid;

EXIT WHEN c1%NOTFOUND;

UPDATE emp

SET salary = salary * 1.02

WHERE rowid = my_rowid; -- 模拟CURRENT OF c1

COMMIT;

END LOOP;

CLOSE c1;

END;

ITPub博客

PLSQL Language Referenc-PL/SQL静态SQL-事务处理和控制-覆盖默认的锁

覆盖默认的锁

最新文章