Does Oracle Roll Back the Transaction on an Error

Does Oracle roll back the transaction on an error?

"User process" in this context is referring to the process running on the client machine that creates the connection to Oracle. In other words, if you are using Application A (SQL*Plus, TOAD, etc.) to connect to Oracle, the user process is SQL*Plus, TOAD, etc. If that user process dies while you were in the middle of a transaction, that transaction will be rolled back. This will happen as soon as PMON discovers that the client has died which may take a bit of time-- it isn't always trivial for Oracle to distinguish the failure of a user process from a user process that just isn't issuing commands at the moment.

how to rollback tran when error occur in oracle

Try this.

declare
cur_empno int;
cursor salary_increment  IS
select employee.empno from EMPLOYEE 
where emplevel in(
    select EmpNo from EMP_SKILL
    group by EmpNo
    having count(*)>1
);
begin
open salary_increment;
loop
fetch salary_increment into cur_empno;
exit when salary_increment%notfound;
update EMPLOYEE set Salary=Salary+300000 
where EmpNo=cur_empno and empLevel=2 and add_months(startdate,24)<=current_date;
update EMPLOYEE set Salary=Salary+500000 
where EmpNo=cur_empno and empLevel=3 and add_months(startdate,36)<=current_date;
end loop;
close salary_increment;

EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;

COMMIT;
END;

Commit transactions if no error,rollback if error occured in Oracle SQL* plus

You don't say what your script is. How about putting it inside a PL/SQL anonymous block?

BEGIN
    ... updates here ...

    COMMIT; 
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    RAISE; 
END;
/

So how is `ROLLBACK` in Oracle exception handler different from my `ROLLBACK`?

This is how Oracle works. Your first INSERT was done correctly (i.e. inserted "1" into a table).

Then you ran an anonymous PL/SQL block which inserts "3", then "2", and fails when trying to insert "1" due to primary key violation.

If unhandled exception occurs during execution of that PL/SQL block (which is what you say that "Oracle exception handler rollbacks to here"), Oracle rolls back to the beginning of the PL/SQL block.

When you utilize EXCEPTION handler and issue ROLLBACK, then it is YOU who decide what to do if something happens, and that is to revert all changes to previous COMMIT which was an implicitly done COMMIT after executing the CREATE TABLE statement, so following INSERT "1" is also rolled back.

What is the common roll back logic in Oracle PL/SQL

What is SAVEPOINT for?

The Oracle 10.2 docs have a good summary of the purpose of SAVEPOINT

With the ROLLBACK TO statement, savepoints undo parts of a transaction instead of the whole transaction.

and

An implicit savepoint is marked before executing an INSERT, UPDATE, or DELETE statement. If the statement fails, a rollback to the implicit savepoint is done. Normally, just the failed SQL statement is rolled back, not the whole transaction; if the statement raises an unhandled exception, the host environment (such as SQL*Plus) determines what is rolled back.

You've written

make all queries in a PL/SQL within one transaction

SAVEPOINT is useless in this situation - as is stated it's used for undong part of a transaction. If every query is in one transaction with a savepoint forced at the beginning then it can't help us at all.

The procedure

I think there's a few things you're missing:

• You can't guarantee that all queries sit within that initial transaction.

  Take a batch job with two steps each step using up significant amount of temp space. If you run both steps without committing between them then you might run out of temp space causing issues for your and other processes running.

  I think we have to assume that you might have a multi-stage and therefore multi-procedure process

• You're not raising exceptions. This means if you've got a data error you're never going to know about it. You must raise this exception to the calling code to decide how to handle it. If every layer of Oracle code raises an exception, i.e. the issue is exceptional, then the statements will rollback automatically.

As a minimum your procedure must be:

create or replace procedure transaction_safe(var in varchar2) is 
begin
  savepoint start_point;

exception when others then
   rollback to start_point;
   raise; -- the important part
end;

We're probably going to end up with a calling construct that very broadly looks like:

procedure run_process is
begin
   transaction1;
   commit;
   transaction2;
   commit;
exception when ...
   ...
end;

That procedure should really be a scheduler chain but putting it like this serves the purpose of making it easy to see.

How might this be used?

I can think of three possibilities

1. On it's own

begin
   transaction1();
   commit;
end;

There are no immediate issues, the calling code is doing the commit logic, we've added an exception to your procedure so we get informed of errors. If we read the documentation for ROLLBACK, it says:

Oracle recommends that you explicitly end transactions in application programs using either a COMMIT or ROLLBACK statement. If you do not explicitly commit the transaction and the program terminates abnormally, then Oracle Database rolls back the last uncommitted transaction.

You've got COMMIT and ROLLBACK stated but it's not very explicit because your transaction control is in multiple places. Were we to put it in the same place it should be in the calling code rather than your procedure, though doing so makes the two functionally pretty similar

procedure run_process is
begin
   transaction1();
   commit;
exception when others then
   rollback;
   raise;
end;

This also makes code more reusable. transaction1() stripped of transaction logic can be called from multiple places as much as is required. Because if everything blows up then everything is rolled back we're not concerned about other procedures having poor transaction control logic as they only commit in calling code.

2. Executing multiple "transactions"

Let's assume that you now raise exceptions in your procedure and we're not committing between "transactions"

procedure run_process is
begin
   transaction1();
   transaction2();
   commit;
exception when others then
   rollback;
   raise;
end;

Let's assume that transaction1() failed and rolled back. An exception is raised hitting our outer exception handler. The rollback logic within the procedure is not required.

Just in case, if you don't raise an exception in transaction1() then your data might be left in an inconsistent because transaction2() will start running.

If you're running transaction1() and transaction2() on the scheduler then they are just two discrete examples of (1).

3. Executing multiple transactions in a loop

In other words, your calling code looks like the following:

procedure run_process() is
begin
   for i in (...) loop
      begin
         transaction1();
      exception when others then
         log();
      end;
   end loop;
end;

In this situation you might not want a single failure to prevent processing of your batch (if you do this is an example of (1)). This is where you're undoing parts of a transaction rather than the whole transaction and therefore using savepoints make sense. I'd still put them in the calling code though:

procedure run_process() is
begin
   for i in (...) loop
      begin
         savepoint blah;
         transaction1();
      exception when others then
         log();
         rollback to blah;
      end;
   end loop;
end;

---

tl;dr

You shouldn't always use savepoints so they should go in the calling code to ensure code reusability.

Make a PL/SQL stored procedure roll back all changes when an exception occurs?

In Oracle you can use SAVEPOINTS. It would be this:

CREATE PROCEDURE foo (x NUMBER) IS
BEGIN
  SAVEPOINT update_bar;

  -- Do some inserts here.
  INSERT INTO bar VALUES (x);
  -- Sometimes there might be an error.
  IF x = 3 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Wooops...');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- Rollback everything which was made after `SAVEPOINT update_bar`
    ROLLBACK TO update_bar;
    RAISE;
END foo;

Rolling back transaction with Oracle OCCI

This could be a side-effect of batch error processing you should try and call setBatchErrorMode( false ) if you want an exception to be raised on error. Otherwise you can also use the batch-error mode and check the collected errors and decide if you want to commit or rollback

PL/SQL: Contradiction in Oracle document on Implicit Rollbacks

There is no contradiction, but the documentation must be read precisely e,.g.

CREATE TABLE t
  (col NUMBER(1) NOT NULL)
/

Table created.

CREATE PROCEDURE insert_t1
AS
BEGIN
  INSERT INTO t
    (col)
  SELECT 1 FROM dual
  UNION ALL
  SELECT 2 FROM dual;
  INSERT INTO t
    (col)
  SELECT 9 FROM dual
  UNION ALL
  SELECT 10 FROM dual;
END;
/

Procedure created.

SELECT col
FROM t
/

no rows selected.

INSERT INTO t
SELECT 9 FROM dual
UNION ALL
SELECT 10 FROM dual
/

INSERT INTO t
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SELECT col
FROM t
/

no rows selected.

This shows, assuming that the attempt to insert the two records is in the order specified, that a DML statement rolls back to the implicit savepoint established before executing the statement, as neither record exists in the database. If we then continue:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  CURSOR csr
  IS
    SELECT col
    FROM t
    ORDER BY col;
BEGIN
  BEGIN
    insert_t1;
  EXCEPTION
    WHEN OTHERS THEN
      FOR rec IN csr LOOP
        dbms_output.put_line('COL: ' || rec.col);
      END LOOP;
      RAISE;
  END;
END;
/

COL: 1
COL: 2
DECLARE
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 15

This shows that if you exit a stored subprogram with an unhandled exception, Oracle does not do any rollback as the records inserted by the first insert statement are still in the table. However, as above when executing the DML directly, the whole of the second insert statement has been rolled back to the implicit savepoint established before the second statement was executed.

However, if we then attempt to query the table.

SELECT col
FROM t
/

no rows selected.

This shows that if you exit an anonymous block with an unhandled exception Oracle does do rollback. This will again be to the implicit savepoint established before the anonymous block was executed.

---

Related Topics