2 Replies Latest reply: Feb 26, 2009 12:45 PM by gdarling - oracle RSS

    ORA-00600 after aborting a TransactionScope object

    22572
      Hi,

      I'm going to evaluate Oracle Services for Microsoft Transaction Server for use in our ASP.NET 2.0 Web Application and I have the following environment:
      - Oracle Database 9.2.0.1.0 on Windows 2003
      - Oracle Data Provider 10.2.0.2.20 on Windows XP client

      I created a simple console application which creates a TransactionScope object, update an existing row in a table and don't call the Complete method to rollback changes.
      First time I execute the application works fine while subsequents throw one of the following messages:
      "ORA-02049: timeout: distributed transaction waiting for lock"
      "ORA-00060: deadlock detected while waiting for resource"

      It seems that transaction takes some time to be rolled back because if I wait to that transaction to time out and then execute the application, it works fine.

      Oracle connection string has the "enlist" parameter set as true.

      Please help.

      Thanks.

      Here's code I execute:

      using(TransactionScope scope = new TransactionScope())
      {
           using(OracleConnection connection = new OracleConnection())
           {
                connection.ConnectionString = OracleConnectionString;
                connection.Open();

                // Create a command to execute the sql statement.
                OracleCommand command = connection.CreateCommand();
                command.Connection = connection;
                command.CommandText = @"update temp set num = num + 1 where id = 1";

                // Execute the SQL statement to insert one row in DB.
                command.ExecuteNonQuery();

                // Close the connection and dispose the command object.
                connection.Close();
           }

           //scope.Complete();
      }
        • 1. Re: ORA-00600 after aborting a TransactionScope object
          687929
          Im with the same problem but using ODP 11g. Did you find a solution ?
          • 2. Re: ORA-00600 after aborting a TransactionScope object
            gdarling - oracle
            Are you sure it's the same problem? Or do you just get a similar error message? Is it consistently reproducible with the above testcase?

            I just tested 11g (11107p5 for the record) and the above code works fine for me. Here's exaclty what I tested:
            using System;
            using System.Data;
            using Oracle.DataAccess.Client;
            using System.Transactions;
            
            class Program
            {
                static void Main(string[] args)
                {
                    for (int i = 0; i < 30; i++)
                    {
                        using (TransactionScope scope = new TransactionScope())
                        {
                            using (OracleConnection connection = new OracleConnection("data source=orcl;user id=scott;password=tiger"))
                            {
                                connection.Open();
                                OracleCommand command = connection.CreateCommand();
                                command.Connection = connection;
                                command.CommandText = @"update onecol set col1 = col1 + 1";
                                command.ExecuteNonQuery();
                                connection.Close();
                            }
                            //scope.Complete();
                        }
                    }
                }
            }
            Anyway, things to check:

            1) Are you using System.Transactions because you're actually using distributed transactions? If not, and you're just using TransactionScope for ease of coding, you can set ODP's "PromotableTransaction" setting to "local" to avoid the overhead of distributed transactions in the first place which should avoid this.

            2) If you do need distrubuted transactions, check the oramts registry setting (HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\home_name) ORAMTS_ABORT_MODE and make sure it's not set to ORAMTS_ABORT_MODE_NEW_CONN_ONLY. The default setting as of 11g is ORAMTS_ABORT_MODE_ORIG_CONN_OK, even if it's not set, and that should be the setting you want for this.