ITPub博客

首页 > 数据库 > Oracle > PLSQL Language Referenc-PL/SQL静态SQL-事务处理和控制-覆盖默认的锁

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

原创 Oracle 作者: luisedalian 时间:2014-03-17 08:23:14 0 删除 编辑

覆盖默认的锁

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

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

LOCK TABLE,显式地锁住整个表

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

1LOCK TABLE语句

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

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

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

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

 

2SELECT FOR UPDATE语句和FOR UPDATE光标

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

在使用混合列压缩(Hybrid Columnar CompressionHCC)的表中DML语句锁住的是压缩单元,而不是行。HCCOracle某个Oracle存储系统的功能。

 

SELECT FOR UPDATE与一个显式光标相关联,则该光标被称为FOR UPDATE光标。只有FOR UPDATE光标可以出现在UPDATEDELETE语句的CURRENT OF 子句中。CURRENT OF 子句是PL/SQLUPDATEDELETE语句的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个表employeesdepartments,FOR UPDATE 子句中只出现在salary

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

--      如果FOR UPDATE子句包含department_idmanager_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变量中,然后在后面的updatedelete操作中使用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博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1122929/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    1986
  • 访问量
    5678183