Forum Stats

  • 3,826,928 Users
  • 2,260,726 Discussions
  • 7,897,123 Comments

Discussions

Selective commit

user-dym_on_3270226
user-dym_on_3270226 Member Posts: 3 Blue Ribbon
edited May 14, 2021 12:26PM in Database Ideas - Ideas

Well, I need an ability to commit operation in some table. Sample code:

insert into T1 ....

insert into T2...

insert into T3...

commit (T3);

rollback;


In this case ROLLBACK executes only for T1 and T2.

For what it needs?

Let you have got a long transaction where qeue many statements have to execute, and each statement has some info which you write into special table. If exception occures I want to save all info about statements. Or for the test runs.

user-dym_on_3270226User_69K8X
5 votes

Active · Last Updated

Comments

  • Rostislav Kushnirenko
    Rostislav Kushnirenko Member Posts: 5 Blue Ribbon

    Is SAVEPOINT functionality something what you are looking for? Also maybe check documentation for DBMS_TRANSACTION package - some its procedures maybe contains functionality you need.

    ChristyxoWilliam Robertson
  • user-dym_on_3270226
    user-dym_on_3270226 Member Posts: 3 Blue Ribbon

    No, SAVEPOINT doesn't fit.

    In my example:

    insert into T1 ....

    insert into T2...

    insert into T3...

    commit (T3);

    rollback;

    T1 and T2 must be rolled back, but T3 must be committed. How to this with SAVEPOINT? Statements order is important.

    Extended example:

    insert into T1 ....

    insert into T3...

    insert into T2...

    update T3...

    update T1 ...

    insert into T3...

    commit (T3);

    rollback;

    ROLLBACK must executes only for T1 and T2.

  • Thorsten Kettner
    Thorsten Kettner Member Posts: 42 Red Ribbon

    Sorry, this idea doesn't sound good to me.

    1. I insert a new product in the products table
    2. I insert an order on this product in the orders table
    3. I commit the order insert
    4. I rollback the product insert
    5. Now I have an order row that violates its foreign key constraint, as its product is no longer in the database.


    William Robertson
  • user-dym_on_3270226
    user-dym_on_3270226 Member Posts: 3 Blue Ribbon

    Well, I agree this option is a dangerous in common using. But it would be very usefull. To exclude like mistakes it may be explicite grant special privileges. Selective commit is needed for some tables only (for example, log tables) .

    It may be:

    create table log_table(event_date timestamp default systimestamp, event_name varchar2(120), ...);

    grant selective commit on log_table ... ;

    ...

    commit(log_table);

    ok

    ...

    commit(other_table);

    Error!

  • Niels Hecker
    Niels Hecker Member Posts: 28 Bronze Badge

    If you need it for logging tables why not create a procedure with PRAGMA AUTONOMOUS_TRANSACTION;?