[SOLVED] Inserted records getting committed in DB without committing it manually

Inserted records getting committed in DB without committing it manually

I have created a database transaction and I am inserting records in Table1 of H2 DB. But no commits done yet. In between this process, after executing half of the records, I execute one create statement(created Table2).

Table2 is created and along with it, previous INSERT statements are also getting committed in DB.

After this, I'm inserting more records in Table1, if there is a failure in insertion, I still see records in Table1 which were inserted before create statement for Table2.

Due to this, I see some records in DB even after transaction failure. I was expecting ZERO records in DB.

Why is this happening?

Solution

Because create table is a DDL statement and no DML statement. And DDL statement usually commit any open transaction.

If you want to avoid this you should create all objects you need during the import before you import the first record.

EDIT 2019-03-22

Although this topic is a bit old I like to mention one thing which could help. You could create a procedure which uses PRAGMA AUTONOMOUS_TRANSACTION which executes an sql statement via execute immediate

PROCEDURE exec_sql_autonomous(p_sql VARCHAR2)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   EXECUTE IMMEDIATE p_sql;

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

This way you may be able to create a table while the data inserting transaction is in progress without committing it due to the table creation.