Are you affraid of COMMIT?

w dniu


Some time ago, while drinking a cup of coffee at work, I was browsing the source code. Suddenly - I almost gasped! The code that appeared to my eyes intrigued me.
Here is the code:
begin

   -- some code
   
   
   -- more code
   
   begin
   
    COMMIT;
   exception when others then
       rollback;
       -- log error
   end;
   
-- of course, more code

end;
COMMIT wrapped with BEGIN/END? But why? I've never even seen this. I'll ask.
- Why did you give COMMIT in BEGIN / END?
- Oh yes, just in case. - I hear the answer.

Just in case.... hmm...

COMMIT perform changes to the database that have been already made. If something went wrong, it went earlier, when performing specific operations such as INSERT, UPDATE, etc. COMMIT is just a dot the I's. (Well, of course, during COMMIT, the server can go up in flames, but let's agree, BEGIN/ END will not help here ...)
But just in case I'll check ... 

I work on Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production.
-- attempt with incorrect insert
set serveroutput on

begin

    insert into countries (country_id, country_name, region_id)
    values ('GEO', 'GEORGIA',  1);
   
   begin
   
    commit;
    dbms_output.put_line('commited');
   exception when others then
     rollback;
       -- log error
       dbms_output.put_line('in the commit''s exception');
   end;

exception when others then
    rollback;
    -- log error
    dbms_output.put_line('in the main exception');

end;
/
in the main exception
In case of the incorrect insert, we didn't even enter BEGIN/END;  of COMMIT. We dropped out in the main EXCEPTION.

-- attempt with the correct insert
set serveroutput on

begin

    insert into countries (country_id, country_name, region_id)
    values ('GE', 'GEORGIA',  1);
   
   begin
   
    commit;
    dbms_output.put_line('commited');
   exception when others then
     rollback;
       -- log error
       dbms_output.put_line('in the commit''s exception');
   end;

exception when others then
    rollback;
    -- log error
    dbms_output.put_line('in the main exception');

end;
/
commited
In case of correct insert - we entered BEGIN/END of COMMIT, the transaction was committed.
Nothing unpredictable.

But... What about distributed databases? Let's see.
I have created two databases, one has an HR schema and the other has a SAL schema. A detailed structure is not essential. In the SAL schema, I created a db link to the database with the HR diagram.
So let's go!
set serveroutput on

begin
    
    insert into countries@hr (country_id, country_name, region_id)
    values ('GE', 'GEORGIA',  1);
   
   begin
   
      commit COMMENT 'ORA-2PC-CRASH-TEST-1';    
      dbms_output.put_line('commited');
   exception when others then
     
       -- log error
       dbms_output.put_line('in the commit''s exception');
       dbms_output.put_line( sqlerrm);
   end;

exception when others then
    rollback;
    -- log errorCOMMIT
    dbms_output.put_line('in the main exception');

end;
/
PL/SQL procedure successfully completed.

in the commit's exception
ORA-02050: transaction 3.18.1427577 rolled back, some remote DBs may be in-doubt
ORA-02059: ORA-2PC-CRASH-TEST-1 in commit comment
Ha! I broke COMMIT! Great :)

So there is a bit of madness in COMMIT :)

Nevertheless putting COMMIT into his own block BEGIN/END  is an excessive, unnecessary procedure. The problem can only occur in distributed databases, and a little more attention to design may minimize the risk. I have never really seen such an error. Apparently this is not a common thing.
So not panic and do COMMIT :)

A little more detail on this subject are found on the net.

Komentarze

Prześlij komentarz