Does Oracle roll back the transaction on an error?

Question

This feels like a dumb question, but I see the following in the Oracle concepts guide on transaction management:

A transaction ends when any of the following occurs:

A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.

A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.

A user disconnects from Oracle. The current transaction is committed.

A user process terminates abnormally. The current transaction is rolled back.

Am I to interpret the last point to mean that if I issue a query that has an error, the transaction will get rolled back?

Answer 1

this is an interesting question !

When Oracle encounters an error, it will rollback the current statement, not the transaction. A statement is any top-level instruction, it can be a SQL statement (INSERT, UPDATE...) or a PL/SQL block.

This means that when a statement (for example a pl/sql procedure called from java) returns an error, Oracle will put the transaction in the same logical state as before the call. This is immensely helpful, you don't have to worry about half-executed procedures (**).

This thread on AskTom covers the same topic:

[the statement] either ENTIRELY happens or it ENTIRELY DOES NOT happen and the way that works is the database does the logical equivalent of:

begin
   savepoint foo;
   <<your statement>>
exception
   when others then rollback to foo; 
                    RAISE;
end;

This feature, in my opinion, is why it is a lot easier to write database code (*) in pl/sql than in any other language.

(*) code that interacts with an Oracle DB of course, I suppose the native procedural languages of the other DBMS have similar features.

(**) This only concerns DML since DDL are not transactional in Oracle. Be also careful with some DBMS packages that update the data dictionary (such as DBMS_STATS), they often do DDL-like changes and issue commits. Refer to the documentation in case of doubts.

Update: this behaviour is one of the most important concept in PL/SQL, I will provide a small example to demonstrate the atomicity of the pl/sql statements:

SQL> CREATE TABLE T (a NUMBER);

Table created

SQL> CREATE OR REPLACE PROCEDURE p1 AS
  2  BEGIN
  3     -- this statement is successful
  4     INSERT INTO t VALUES (2);
  5     -- this statement will raise an error
  6     raise_application_error(-20001, 'foo');
  7  END p1;
  8  /

Procedure created

SQL> INSERT INTO t VALUES (1);

1 row inserted

SQL> EXEC p1;

begin p1; end;

ORA-20001: foo
ORA-06512: at "VNZ.P1", line 5
ORA-06512: at line 2

SQL> SELECT * FROM t;

         A
----------
         1

Oracle has rolled back the transaction to the point just before calling p1. There is no half-work done. It is as if the procedure p1 had never been called.

Answer 2

"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.

Answer 3

I agree with Justin, his insight is correct. Adding additional information: As the application developer, you should explicitly call a rollback command if errors happen. This means, you should also consider grouping statements into transactional blocks as appropriate. Transactional blocks and rollbacks are handled differently by different technologies, it's worth some research to make sure you understand it well.