Can't disable auto-commit

[Adam Nielsen]

Hi Sergei,

I'm doing some development work in an Oracle 11g database so I want to run some queries and then later, roll them back as if they never happened (so I can try again until I get it right.)

Unfortunately my queries seem to be committed immediately, even though I am calling otl_connect::rollback().  Am I missing something?  I have called otl_connect::auto_commit_off() and I am only using otl_nocommit_stream classes, and there is no call to otl_connect::commit() in my code.  Is there anything else I need to do to disable auto-commit?

Many thanks,
Adam.

P.S. I intend to get back to you about that embedded NULL thing but I haven't had a chance to fully use it yet!

[Sergei Kuchin]

Adam,

Can you put together a self-contained code example that you think should think should work, but it doesn't. If I can see what the problem is, I can tell you more definitively whether it's a case of misuse, or a bug in OTL. Email me directly.

Cheers,
Sergei

[Tanzinul Islam]

I think I am coming across this same problem, also with an Oracle 11g R2 database. Not sure if this is a bug in OTL or a mis-configuration in Oracle (which is known to not support auto-commit at the database level).

I'm trying with C++98 code (compiling with GCC 4.1.2) similar to the following, with my expectations in comments:

#include <iostream>

#define OTL_ORA11G_R2
#include <otl/otlv4.h>

int main()
{
    otl_connect::otl_initialize(1);

    try {
        otl_connect conn("username/password@server");

        int rpc = otl_cursor::direct_exec(
            conn,
            "DELETE FROM table_name "
                "WHERE column_name_1 IN (1234, 5678)"
        );
        std::cout << "Deleted " << rpc << " rows" << std::endl;
        conn.commit();

        otl_nocommit_stream str(
            1,
            "INSERT INTO table_name "
                "(column_name_1, column_name_2, column_name_3, column_name_4) "
                "VALUES ( :1<int>, :2<int>, :3<char[7]>, :4<char[7]> )",
            conn
        );
        str << 1234 << 9876 << "Hello" << "test";    // "str" should auto-flush here,
                                                     // but not commit.
        str << 5678 << 3456 << "There" << "test2";   // "str" should auto-flush here
                                                     // again, but not commit.

        // Destructor of "str" shouldn't auto-flush, here, because it is not dirty.
        // Regardless of whether "str" auto-flushes here, it should not commit here.

        // Destructor of "conn" should also not commit here, but simply log off.

        // The database shouldn't auto-commit on my behalf, because this is Oracle.
    } catch (otl_exception & e) {
        std::cerr << "Exception: " << e.code << '\t' << e.msg << std::endl;
    }

    otl_connect::otl_terminate();
}

The above program should clean up the test rows, commit, then reinsert those rows back again, but not commit a final time. Therefore, after running this program, I should not be able to find the test rows anymore by running a query like this in SQL*Plus:

SELECT * FROM table_name WHERE column_name_1 IN (1234, 5678)

But I was able to find those records after running this program. Also, running this program multiple times confirmed that the DELETE statement was deleting 2 records each time. I was able to replicate this with the latest otlv4.h version 4.0.335.

I've read the otl_connect documentation at http://otl.sourceforge.net/otl3_connect_class.htm, and it seems like the class should not auto-commit by default. I've even tried specifying a second argument of 0 in the constructor of conn, and calling conn.auto_commit_off(); -- all yielding the same results.

For now I am working around the issue by writing my own wrapper class around otl_connect, and calling its rollback method in my destructor.

Many thanks,
Tanz

[Sergei Kuchin]

Tanz,

otl_connect doesn't auto commit on logoff(), but Oracle commits the last transaction on disconnect (it's not a bug, it's a feature, pardon the cliche). Either you need to roll back before logging off, or you can use #define OTL_ROLLS_BACK_BEFORE_LOGOFF: http://otl.sourceforge.net/otl3_compile.htm#OTL_ROLLS_BACK_BEFORE_LOGOFF.

Cheers,
Sergei

[Tanzinul Islam]

Ah thanks, #define OTL_ROLLS_BACK_BEFORE_LOGOFF is definitely helpful.

It is a much better solution compared to myself calling conn.rollback(); at the end of the try-block in my example above, because the destructor of str may still auto-flush its remaining stuff (if the buffer size were larger), followed by the destructor of conn doing the logoff() and triggering Oracle's implicit commit.

It seems my Web searches so far are yielding confusing information:

• http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci02bas.htm#LNOCI87616

  If an application disconnects from Oracle in some way other than a normal logoff, such as losing a network connection, and OCITransCommit() has not been called, all active transactions are rolled back automatically.

• http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci09adv.htm#LNOCI87752

  OCISessionRelease()
  If OCISessionGet() was called to make the connection, then OCISessionRelease() must be called to log off. Pending transactions are automatically committed.
  ...
  Note:
  Developers are advised to commit or rollback any open transaction before releasing the connection back to the pool. If it is not done, Oracle automatically commits any open transaction when the connection is released back to the pool.

• https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1519405621318

  ... (if you log off, Oracle will commit your work before it does so, yes)

• https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:314816776423

  ... the only method Oracle operates in is "client tells us when to commit". We do not have an autocommit mode. Various TOOLS and API's do (eg: I can tell sqlplus to autocommit, that just means sqlplus will issue a commit after each statement).

So it seems to me that Oracle Database doesn't do auto-commit, except at graceful logoff -- which is about the only time it actually does commit implicitly for you. On top of that, different APIs and utilities (not including OCI) can have more eager auto-commit semantics by default.