fbpx

טרנזקציות אוטונומית הן יחידות טרנזקציה עצמאיות אשר פועלות ללא תלות בטרנזקציה הראשית ממנה הן מורצות.
שימוש בטרנזקציות אלו נפוץ במקרים בהם נרצה לתעד באמצעות תת טרנזאקציה פעולות
DML
המתרחשות בטרנסאקציה הראשית, כאשר בין שתי היחידות (האב והבן) אין יחסי תלות.
נשמע מסובך ? 🙂 ממש לא,  ראשית נסביר טכנית כיצד הטרנזקציות האוטונומיות פועלות ונתחיל בדוגמא פשוטה –  נייצר טבלה בעלת עשרה ערכים

RAM >> CREATE TABLE transaction_test
  2  (id number(2),
  3   value varchar2(25)) ;

Table created.

RAM >>  BEGIN
  2  FOR i IN 1..10 LOOP
  3  INSERT INTO transaction_test VALUES (i , 'No Value') ;
  4  END LOOP ;
  5  COMMIT ;
  6  END ;
  7  /

PL/SQL procedure successfully completed.

RAM >> SELECT * FROM transaction_test ;

        ID VALUE
---------- -------------------------
         1 No Value
         2 No Value
         3 No Value
         4 No Value
         5 No Value
         6 No Value
         7 No Value
         8 No Value
         9 No Value
        10 No Value

כעת נבצע שתי פעולות עדכון על הטבלה

RAM >> UPDATE transaction_test
    SET value = 'Transaction A' WHERE id BETWEEN 1 AND 4 ;
4 rows updated.

RAM >> DECLARE
  2   PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4  UPDATE transaction_test
     SET value = 'Transaction B' where id BETWEEN 5 AND 10 ;
  5  COMMIT ;
  6  END ;
  7  /

PL/SQL procedure successfully completed.

RAM >> SELECT * FROM transaction_test ;

        ID VALUE
---------- -------------------------
         1 Transaction A
         2 Transaction A
         3 Transaction A
         4 Transaction A
         5 Transaction B
         6 Transaction B
         7 Transaction B
         8 Transaction B
         9 Transaction B
        10 Transaction B

10 rows selected.

כחלק מבלוק ה PLSQL סיימנו את פעולת העדכון השניה בפקודת ה Commit, באופן רגיל פקודה זו היתה מסיימת את הטרנזקציה עבור פעולת העדכון הראשונה והשניה.
אך מכיוון שהגדרנו את הפעולות המתרחשות בבלוק השני כטרנזקציה עצמאית (
PRAGMA AUTONOMOUS_TRANSACTION)  שימו לב מה יקרה כאשר נבצע Rollback

RAM >> ROLLBACK ;
Rollback complete.

RAM >> SELECT * FROM transaction_test ;
        ID VALUE
---------- -------------------------
         1 No Value
         2 No Value
         3 No Value
         4 No Value
         5 Transaction B
         6 Transaction B
         7 Transaction B
         8 Transaction B
         9 Transaction B
        10 Transaction B

10 rows selected.

רק הערכים של פעולת העדכון הראשונה בוטלו בעוד הערכים של פעולת העדכון השניה (הטרנזאקציה האוטונומית) נותרו בעינם. זו המשמעות של טרנזקציה אוטונומית.

תיעוד פעולות על פרוצדורה

כעת נעבור לדוגמאות מעט יותר מורכבות – בדוגמא הבאה נייצר פרוצדורה בסיסית אשר מבצעת עדכון על טבלה ובמקביל מכניסה תיעוד לתוך טבלת לוג, אנו רוצים למנוע מצב ש Rollback בסיום פעילותה יבטל גם את הערכים שנכנסו לתוך טבלת התיעוד.  

CREATE TABLE emps AS SELECT * FROM hr.employees ;

CREATE TABLE log (id number, change_date date, comments varchar2(100)) ;

CREATE SEQUENCE log_seq ;

CREATE OR REPLACE PROCEDURE update_sal
    (emp_id emps.employee_id%type, emp_sal emps.salary%type)
AS
    e_error exception ;
    PROCEDURE insert_log
    (emp_id emps.employee_id%type, emp_sal emps.salary%type, status number)
    AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    IF status = 1 THEN
    INSERT INTO log VALUES (log_seq.nextval , sysdate,  'new salary - ' || emp_sal
                                                       || ' for employee ' || emp_id
                                                       || ' completed successfully ');
    COMMIT ;
    ELSE
    INSERT INTO log VALUES (log_seq.nextval , sysdate,  'new salary - ' || emp_sal
                                                       || ' for employee ' || emp_id
                                                         || ' Failed ');
    COMMIT ;
    END IF;
    END ;
BEGIN
    UPDATE emps
    SET salary = emp_sal
    WHERE employee_id = emp_id ;
    IF sql%rowcount = 0 THEN raise e_error  ; END IF ;
    insert_log (emp_id , emp_sal , 1) ;
EXCEPTION
    WHEN e_error THEN
    insert_log (emp_id , emp_sal , 0) ;
END ;
 /

ניתן לראות את תוצאות הרצת הפרוצדורה לפני ואחרי פעולת ה Rollback בקוד הבא :

RAM >> -- Existing value
RAM >> EXEC update_sal (100 , 5000)

PL/SQL procedure successfully completed.

RAM >> -- Non existing value
RAM >> EXEC update_sal (555 , 5000)

PL/SQL procedure successfully completed.

RAM >> -- Value Changed
RAM >> SELECT salary FROM emps WHERE employee_id = 100 ;

    SALARY
----------
      5000

RAM >> -- Log table reflects the two updates
RAM >> SELECT * FROM log ;

        ID CHANGE_DA COMMENTS
---------- --------- -----------------------------------------------------------------
         2 16-DEC-12 new salary - 5000 for employee 100 completed successfully
         3 16-DEC-12 new salary - 5000 for employee 555 Failed

RAM >> COL comments FOR a80
RAM >> ROLLBACK ;

Rollback complete.

RAM >> -- Value for employee_id = 100 was rollbacked
RAM >> SELECT salary FROM emps WHERE employee_id = 100 ;

    SALARY
----------
     24002

RAM >> -- Rollback didn't affect log table values
RAM >> SELECT * FROM log ;

        ID CHANGE_DA COMMENTS
---------- --------- -----------------------------------------------------------------
         2 16-DEC-12 new salary - 5000 for employee 100 completed successfully
         3 16-DEC-12 new salary - 5000 for employee 555 Failed

תיעוד פעולות על טבלה

בדוגמא הבאה נייצר טריגר בסיסי אשר נועד להכניס לתוך טבלת לוג תיעוד פעולות Update שונות אשר מתבצעות טבלה מסויימת, וכמו מקודם, כאשר יתבצע Rollback אנו לא רוצים שהנתונים בתוך טבלת הלוג יבוטלו.

RAM >> CREATE TABLE copy_emp AS select * FROM hr.employees ;

Table created.

RAM >>
RAM >> CREATE TABLE log_emp
  2  (action VARCHAR2(3),
  3   user_n VARCHAR2(25),
  4   change_date DATE,
  5   changed_employee_id NUMBER)
  6  /

Table created.

RAM >>
RAM >> CREATE OR REPLACE TRIGGER log_emp_trig
  2  AFTER UPDATE ON copy_emp
  3  FOR EACH ROW
  4  DECLARE
  5     PRAGMA AUTONOMOUS_TRANSACTION;
  6  BEGIN
  7      INSERT INTO log_emp
  8      VALUES ('DML', USER, sysdate , :old.employee_id);
  9      COMMIT ;
 10  END;
 11  /

Trigger created.

RAM >>
RAM >> -- Before Change
RAM >> SELECT salary FROM copy_emp WHERE employee_id IN (100, 101, 102) ;

    SALARY
----------
     24002
     17000
     17000

RAM >>
RAM >> UPDATE copy_emp SET salary = salary + 500 WHERE employee_id IN (100, 101, 102) ;

3 rows updated.

RAM >>
RAM >> -- After Change
RAM >> SELECT salary FROM copy_emp WHERE employee_id IN (100, 101, 102) ;

    SALARY
----------
     24502
     17500
     17500

RAM >>
RAM >> SELECT action , user_n ,
  2         TO_CHAR(change_date , 'dd/mm/yyyy - hh24:mi:ss') CHANGE_DATE ,
  3         changed_employee_id
  4  FROM log_emp
  5  ORDER BY CHANGE_DATE ;

ACT USER_N                    CHANGE_DATE           CHANGED_EMPLOYEE_ID
--- ------------------------- --------------------- -------------------
DML RAM                       16/12/2012 - 11:29:11                 100
DML RAM                       16/12/2012 - 11:29:11                 102
DML RAM                       16/12/2012 - 11:29:11                 101

RAM >>
RAM >> -- After Rollback
RAM >> ROLLBACK ;

Rollback complete.

RAM >>
RAM >> SELECT salary FROM copy_emp WHERE employee_id IN (100, 101, 102) ;

    SALARY
----------
     24002
     17000
     17000

RAM >>
RAM >> SELECT action , user_n ,
  2         TO_CHAR(change_date , 'dd/mm/yyyy - hh24:mi:ss') CHANGE_DATE ,
  3         changed_employee_id
  4  FROM log_emp
  5  ORDER BY CHANGE_DATE ;

ACT USER_N                    CHANGE_DATE           CHANGED_EMPLOYEE_ID
--- ------------------------- --------------------- -------------------
DML RAM                       16/12/2012 - 11:29:11                 100
DML RAM                       16/12/2012 - 11:29:11                 102
DML RAM                       16/12/2012 - 11:29:11                 101

RAM >>

נכתב ע"י רם קדם