none
Oracle transaction auto commit RRS feed

  • Question

  • Hi,

    I have a simple code of few inserts with a transaction. It works as follows

     

    1.)

    Connect.Close: Y

    Transaction Commit/Rollback: Y

     

    Works fine

     

    2.)

    Connect.Close: Y

    Transaction Commit/Rollback: N

     

    Works fine

     

    3.)

    Connect.Close: N (Abnormal termination)

    Transaction Commit/Rollback: N

     

    Automatically commits

     

    How can we prevent automatic commit of transactions, even if the program terminates abnormally,

     

    Thanks?

     

    Monday, November 17, 2008 6:29 AM

Answers

  • What kind of transaction are you doing?  Local or distrib?  System.Transactions?

    Is this consistently reproducible? Do you have a snippet you can provide?

     

    I tried the following (using Oracle's provider, 11.1.0.7) , and didnt end up with a row inserted..

     

     

    CODE

    ======

    OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl");

    con.Open();

    OracleTransaction ot = con.BeginTransaction();

    OracleCommand cmd = new OracleCommand("", con);

    cmd.CommandText = "insert into emp(empno) values (1234)";

    cmd.ExecuteNonQuery();

    throw new NotImplementedException();

    ot.Rollback();// not reached

     

    Tuesday, November 18, 2008 3:55 PM

All replies

  • What kind of transaction are you doing?  Local or distrib?  System.Transactions?

    Is this consistently reproducible? Do you have a snippet you can provide?

     

    I tried the following (using Oracle's provider, 11.1.0.7) , and didnt end up with a row inserted..

     

     

    CODE

    ======

    OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl");

    con.Open();

    OracleTransaction ot = con.BeginTransaction();

    OracleCommand cmd = new OracleCommand("", con);

    cmd.CommandText = "insert into emp(empno) values (1234)";

    cmd.ExecuteNonQuery();

    throw new NotImplementedException();

    ot.Rollback();// not reached

     

    Tuesday, November 18, 2008 3:55 PM
  • This is the code i used.

    Code Snippet

    Sub Main()

    Dim oc As New OracleConnection("Data source=ORCL; User ID=scott;Password=tiger")

    oc.Open()

    Dim t As OracleTransaction = oc.BeginTransaction()

    Dim cmd2 As New OracleCommand("INSERT INTO TAB1(A) VALUES (" & DateTime.Now.Ticks & ")", oc)

    cmd2.Transaction = t

    cmd2.ExecuteNonQuery()

    't.Commit()

    'oc.Close()

    End Sub

     

     

    In this i commented the transaction commit and also the connection close, to simulate the abnormal process temination.

    Without connection close and commit, it should not commit, but it does.

    I like to get rid of this.

    Should be possible. But how?

     

    Thanks

     

    Wednesday, November 19, 2008 6:45 AM
  • hi gdarling,

    do you have any update for this issue

     

    Wednesday, November 26, 2008 7:08 AM