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

Autonomous transactions

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

          is duplicated by

          Task - A task that needs to be done. MDEV-9917 autonomous transaction

          • Major - Major loss of function.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-35973 Oracle Compatibility Project 1 for 2025

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress

          Activity

            Ascending order - Click to sort in descending order
            monty Michael Widenius created issue -
            monty Michael Widenius made changes -
            Field Original Value New Value
            Epic Link MDEV-10137 [ 56868 ]
            serg Sergei Golubchik made changes -
            Summary Autonomous blocks Autonomous transactions
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Description Add support for autonomous blocks in stored procedures.

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

            Here is a reference how Oracle defines this:
            https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm

            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 procedure with it's own THD (== user), like it would be a new independent connection.             		Add support for autonomous blocks in stored procedures.

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

            Here is a reference how Oracle defines this:
            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 procedure with it's own THD (== user), like it would be a new independent connection.
            bar Alexander Barkov made changes -
            Description Add support for autonomous blocks in stored procedures.

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

            Here is a reference how Oracle defines this:
            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 procedure with it's own THD (== user), like it would be a new independent connection.
            		Add support for autonomous blocks in stored procedures.

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

            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 procedure with it's own THD (== user), like it would be a new independent connection.

            An example of an Oracle stored procedure using AUTONOMOUS_TRANSACTION:
            {code:sql}
            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;
            {code}
            {noformat}
            RAISE_SALARY(1,10)
            ------------------
            110
            {noformat}

            bar Alexander Barkov made changes -
            Description Add support for autonomous blocks in stored procedures.

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

            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 procedure with it's own THD (== user), like it would be a new independent connection.

            An example of an Oracle stored procedure using AUTONOMOUS_TRANSACTION:
            {code:sql}
            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;
            {code}
            {noformat}
            RAISE_SALARY(1,10)
            ------------------
            110
            {noformat}

            		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:
            {code:sql}
            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;
            {code}
            {noformat}
            RAISE_SALARY(1,10)
            ------------------
            110
            {noformat}
            alvinr Alvin Richards (Inactive) made changes -
            Labels procedures Compatibility procedures
            alvinr Alvin Richards (Inactive) made changes -
            Epic Link MDEV-10137 [ 56868 ] MDEV-10872 [ 58182 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.2 [ 14601 ]
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility procedures Compatibility NRE-307517 procedures
            alvinr Alvin Richards (Inactive) made changes -
            NRE Projects NRE-307517
            alvinr Alvin Richards (Inactive) made changes -
            Labels Compatibility NRE-307517 procedures Compatibility procedures
            monty Michael Widenius made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Support case ID not-26307
            ralf.gebhardt Ralf Gebhardt made changes -
            NRE Projects AC-2610/DEFERRED
            ward3r Yuriy Kuleshov added a comment -

            When you plan to implement this feature? This will be a great thing in oracle compatibility and app design techniques.

            ward3r Yuriy Kuleshov added a comment - When you plan to implement this feature? This will be a great thing in oracle compatibility and app design techniques.
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 75840 ] MariaDB v4 [ 130488 ]
            bar Alexander Barkov made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            bar Alexander Barkov made changes -
            Issue Type Task [ 3 ] New Feature [ 2 ]
            bar Alexander Barkov made changes -
            Labels Compatibility procedures Compatibility Oracle procedures
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            monty Michael Widenius made changes -
            Assignee Michael Widenius [ monty ]
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Minor [ 4 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 12.2 [ 30146 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Minor [ 4 ] Major [ 3 ]
            monty Michael Widenius made changes -
            Sprint Server 12.1 dev sprint [ 793 ]
            serg Sergei Golubchik made changes -
            Rank Ranked lower
            serg Sergei Golubchik made changes -
            Fix Version/s ROADMAP [ 30157 ]
            Fix Version/s 12.2 [ 30146 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]

            People

              monty Michael Widenius
              monty Michael Widenius
              Votes:
              8 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.