ODP.NET

Hi,

i use "Oracle.ManagedDataAccess.Core" Version="23.9.1" on an oracle ADB 23ai (OCI).

i try to implement the SessionlessTransaction-feautrue. when working with temporary tables the resumed transaction does not retrieve the expected result. In my business app, we have multiple connections which are opened and closed on each DB-call. connections are pooled and reused.

when i reduce the poolsize to 1 in my example, the expected result is retrieved.

does SessionlessTransaction not work in combination with connection-pooling?

here is the code:

  private String ConnString()
  {

      var builder = new OracleConnectionStringBuilder
      {
          DataSource = "",
          UserID = "",
          Password = "",
          WalletLocation = "",
          Pooling = true,
          MinPoolSize = 3,
          MaxPoolSize = 10,
          IncrPoolSize = 2,
          DecrPoolSize = 2,
      };

      return builder.ConnectionString;
  }
  
private void Test()
{
    var connStr = ConnString();
    // --- Session 1: Transaktion starten und ins Pool zurückgeben ---
    using (var conn1 = new OracleConnection(connStr))
    {
        conn1.Open();

        // Sessionless Transaction-Optionen (optional)
        var opts = new OracleSessionlessTransactionOptions
        {
            StartBehavior = OracleSessionlessTransactionStartBehavior.StartImmediate,
            ResumeBehavior = OracleSessionlessTransactionStartBehavior.StartImmediate,
        };
        conn1.SessionlessTransactionOptions = opts;

        // Transaktion starten
        txnId = conn1.BeginSessionlessTransaction();

        using (var cmd1 = conn1.CreateCommand())
        {
            cmd1.CommandText = "INSERT INTO test_table (id, name) VALUES (1, 'First Part')";
            cmd1.ExecuteNonQuery();
        }

        // Transaktion pausieren und Verbindung schließen (geht ins Pool zurück)
        conn1.SuspendSessionlessTransaction();
        Debug.WriteLine($"Transaktion gestartet, ID: {BitConverter.ToString(txnId)}");
    }

    // --- Hier könnten Minuten vergehen, Pool gibt Verbindung anderen Clients ---
    Debug.WriteLine("Warte oder mache andere Arbeit...");

    // --- Session 2: Transaktion fortsetzen ---
    using (var conn2 = new OracleConnection(connStr))
    {
        conn2.Open();

        // Transaktion wieder aufnehmen
        conn2.ResumeSessionlessTransaction(txnId);

        using (var cmd2 = conn2.CreateCommand())
        {
            cmd2.CommandText = "INSERT INTO test_table (id, name) VALUES (2, 'Second Part')";
            cmd2.ExecuteNonQuery();
        }


        // Transaktion pausieren und Verbindung schließen (geht ins Pool zurück)
        conn2.SuspendSessionlessTransaction();
        Debug.WriteLine($"Transaktion pausiert, ID: {BitConverter.ToString(txnId)}");
    }

    // --- Session 3: Auslesen fortsetzen ---
    using (var conn3 = new OracleConnection(connStr))
    {
        conn3.Open();

        // Transaktion wieder aufnehmen
        conn3.ResumeSessionlessTransaction(txnId);

        DataTable dt = new DataTable();

        using (var cmd2 = conn3.CreateCommand())
        {
            cmd2.CommandText = "SELECT * FROM test_table";
            var da = new OracleDataAdapter(cmd2);

            da.Fill(dt);
        }

        Debug.WriteLine($"Rows {dt.Rows.Count}");

        // Transaktion finalisieren
        conn3.Commit();
        Debug.WriteLine("Transaktion erfolgreich abgeschlossen.");

    }

}