PLSQL Language Referenc-PL/SQL靜態SQL-事務處理和控制-覆蓋預設的鎖

LuiseDalian發表於2014-03-17
SQL

覆蓋預設的鎖

預設，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部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1122929/，如需轉載，請註明出處，否則將追究法律責任。

相關文章