Randomly getting ORA-08177 with only one active session

I'm running a program that creates a table and then inserts some data.

This is the only program that accesses the database.

I'm getting ORA-08177 randomly.

Actual code is somewhat complex, but I've written a simple program that reproduces this behavior.

using System;
using System.Data;
using Oracle.DataAccess.Client;

namespace orabug
{
  class Program
  {
    private const string ConnectionString = ""; // Valid connection string here

    // Recreate开发者_如何学Pythons the table
    private static void Recreate()
    {
      using (var connection = new OracleConnection(ConnectionString)) {
        connection.Open();
        using (var command = connection.CreateCommand()) {
          command.CommandText = @"
            declare
              table_count binary_integer;
            begin
              select count(*) into table_count from sys.user_tables where table_name = 'TESTTABLE';
              if table_count > 0 then
                execute immediate 'drop table TestTable purge';
              end if;
              execute immediate 'create table TestTable(id nvarchar2(32) primary key)';
            end;";
          command.ExecuteNonQuery();
        }
        connection.Close();
      }
    }

    // Opens session sessionCount times, inserts insertCount rows in each session.
    private static void Insert(int sessionCount, int insertCount)
    {
      for (int sessionNumber = 0; sessionNumber < sessionCount; sessionNumber++)
        using (var connection = new OracleConnection(ConnectionString)) {
          connection.Open();
          using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable)) {
            for (int insertNumber = 0; insertNumber < insertCount; insertNumber++)
              using (var command = connection.CreateCommand()) {
                command.BindByName = true;
                command.CommandText = "insert into TestTable (id) values(:id)";
                var id = Guid.NewGuid().ToString("N");
                var parameter = new OracleParameter("id", OracleDbType.NVarchar2) {Value = id};
                command.Parameters.Add(parameter);
                command.Transaction = transaction;
                command.ExecuteNonQuery();
              }
            transaction.Commit();
          }
          connection.Close();
        }
    }

    static void Main(string[] args)
    {
      int iteration = 0;
      while (true) {
        Console.WriteLine("Running iteration: {0}", iteration);
        try {
          Recreate();
          Insert(10, 100);
          Console.WriteLine("No error");
        }
        catch (Exception exception) {
          Console.WriteLine(exception.Message);
        }
        iteration++;
      }
    }
  }
}

This code runs infinite cycle.

On each iteration it performs the following actions 10 times:

• Open session

• Insert 100 rows with random data

• Close session

• Displays a message saying that no error occured

If error occurs, the exception is caught and its message is printed and then the next iteration is executed.

Here is sample output. As you can see ORA-08177 is interleaving with successful interations randomly.

Running iteration: 1
No error
Running iteration: 2
ORA-08177: can't serialize access for this transaction
Running iteration: 3
ORA-08177: can't serialize access for this transaction
Running iteration: 4
ORA-08177: can't serialize access for this transaction
Running iteration: 5
ORA-08177: can't serialize access for this transaction
Running iteration: 6
ORA-08177: can't serialize access for this transaction
Running iteration: 7
No error
Running iteration: 8
No error
Running iteration: 9
ORA-08177: can't serialize access for this transaction
Running iteration: 10
ORA-08177: can't serialize access for this transaction
Running iteration: 11
ORA-08177: can't serialize access for this transaction
Running iteration: 12
ORA-08177: can't serialize access for this transaction
Running iteration: 13
ORA-08177: can't serialize access for this transaction
Running iteration: 14
ORA-08177: can't serialize access for this transaction
Running iteration: 15
ORA-08177: can't serialize access for this transaction
Running iteration: 16
ORA-08177: can't serialize access for this transaction
Running iteration: 17
No error
Running iteration: 18
No error
Running iteration: 19
ORA-08177: can't serialize access for this transaction
Running iteration: 20
No error

I'm running Oracle 11.1.0.6.0 and using ODP.NET 2.111.6.20.

Changing isolation level to ReadCommited fixes the problem, but I really want to run this at Serializable level.

Looks like I'm not alone with this problem, but answer was not given, so I'm asking again.

What am I doing wrong and how could I fix this?

edit by APC

To prevent anybody else barking up the wrong tree, the posted code sample is just a generator of ORA-8177 errors. Apparently the actual code is different; specifically, the dropping and recreating of tables is a red herring.

Total rewrite (having barked up the wrong tree the first time around).

The SERIALIZABLE isolation level grabs a slot in the Interested Transactions List. If Oracle cannot get a slot then it hurls ORA-8177. The number of available ITL slots is controlled by INITRANS and MAXTRANS. According to the documentation:

To use serializable mode, INITRANS must be set to at least 3.

This must be set for both the table and its indexes. So, what are your INITRANS settings? Certainly your sample code uses the default value (1 for tables, 2 for indexes).

In comments user Gary posted a link to thread that explains this strange behavior. Shortly, sometimes during index restructurization undo data becomes unavailable. Any transaction that runs at serializable isolation level and requests the data that is somehow related with this index will get ORA-08177. This is a half-bug half-feature of Oracle. ROWDEPENDENCIES reduces the chance of getting this error. For my application I've simply switched to ReadCommited level for large data uploads. It seems that there is no other way to escape this problem completely.

Thanks, Gary, I've upvoted your answer to other question.