CODEWITHSUNDEEP
oracle-databaseplsqloracle11gtransactions
J.Doe
J.Doe

Reputation: 105

Begin transaction in Oracle - rollback in autocommit

I'm moving from SQL Server to Oracle. One thing I've found is that in Oracle autocommit is disabled by default. My question is how could we rollback a transaction when autocommit is turned on? In SQL Server, there is Begin tran statement, what about Oracle?

Upvotes: 2

Views: 15769

Answers (2)

APC
APC

Reputation: 146349

There is no equivalent of begin tran in Oracle.

Oracle implicitly starts a transaction the first time we run some DML. The transaction extends until we issue a COMMIT or a ROLLBACK. Note that in Oracle DDL commands issue implicit commits, before and after execution; so not only can we not rollback a CREATE TABLE statement we cannot rollback any DML statements we executed before that CREATE TABLE statement.

Autocommit is an anti-pattern in my book: a transaction is a Unit of Work, which in all but the most trivial of applications is a series of statements. Committing after each statement leads to inconsistency, irrecoverability and (potentially) data corruption.

However, to answer the question: Autocommit commits at the statement level. So there is no way to rollback a single successfully completed transaction. Consequently if you want to have several DML statements into a single transaction with the possibility of rolling back an incomplete unit of work you need to group the statements in a PL/SQL block.

Here we have a PL/SQL block which insert some records in single statements. 

begin
    insert into t23 (id) values (1);
    insert into t23 (id) values (2);
    insert into t23 (id) values (3);
    insert into t23 (id) values (4);
    insert into t23 (id) values (1);
exception
    when dup_val_on_index then
        rollback;
        raise;
end;
/

If we were to run this block in a client with AUTOCOMMIT on it would insert zero records. Autocommit operates at the execution level, which in this case is the block: either the whole succeeds and is committed, or it fails and is not. Consequently the failure of the fifth insert to pass the primary key validation triggers the rollback of the previous four inserts.

Note than the four inserts would be rolled back even without the explicit rollback in the exception handler. The block failed, the transaction is not committed.

so what does this option is all about?: tools -preferences - database - advance - autocommit checkbox

That is a menu path from your client IDE (Oracle SQL Developer?). AUTOCOMMIT is a client option, which issues a commit after every statement executed by the client.

Upvotes: 7

evgeniya makarova
evgeniya makarova

Reputation: 11

Oracle itself does not have autocommit, please adjust your tool and use begin exception end; : https://asktom.oracle.com/pls/apex/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:314816776423

Please read as well - as not everyone knows that DDL commands commit implicitly -all what was done before ANy DDL statement will be commited: https://docs.oracle.com/cd/A57673_01/DOC/api/doc/PC_22/ch08.htm#toc103

Upvotes: 1

Related Questions