Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10155

Autonomous transactions

    XMLWordPrintable

    Details

      Description

      Add support for autonomous blocks in stored routines.

      This would allow a stored routine marked with autonomous_transaction to commit
      and rollback it's own work, independent of the work of the caller.

      Oracle defines this as "PRAGMA AUTONOMOUS_TRANSACTION":
      https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm

      This pragma can be applied to:

      • Top-level (not nested) anonymous PL/SQL blocks
      • Local, standalone, and packaged functions and procedures
      • Methods of a SQL object type
      • Database triggers

      For the moment a workaround is to use the Aria storage engine for tables that should be persistent against ROLLBACK. Aria tables are never rollbacked
      (except if the server crashes in the middle of a query).

      The suggested implementation is to run the autonomous routine with it's own THD (== user), like it would be a new independent connection.

      An example of an Oracle stored function using AUTONOMOUS_TRANSACTION:

      DROP TABLE employees;
      DROP FUNCTION raise_salary;
      CREATE TABLE employees (   
        employee_id INT NOT NULL PRIMARY KEY,
        salary INT NOT NULL
      );
      INSERT INTO employees VALUES (1, 100);
      CREATE FUNCTION raise_salary
                      (emp_id NUMBER,
                       sal_raise NUMBER) RETURN NUMBER
      IS
        PRAGMA AUTONOMOUS_TRANSACTION;
        new_sal NUMBER(8,2);
      BEGIN
        UPDATE employees
          SET salary= salary + sal_raise
          WHERE employee_id = emp_id;   
        COMMIT;
        SELECT salary INTO new_sal FROM employees WHERE employee_id = emp_id;
        RETURN new_sal;
      END raise_salary;
      /
      SELECT raise_salary(1, 10) FROM DUAL;
      

      RAISE_SALARY(1,10)
      ------------------
      	       110
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              monty Michael Widenius
              Votes:
              7 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration