DML Statement After a Transactionscope Block Fails With ORA-00054 (Doc ID 465731.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Services for Microsoft Transaction Server - Version: 9.2.0.1 and later   [Release: 9.2 and later ]
Oracle Data Provider for .NET - Version: 9.2.0.7.0 to 10.2.0.3.3]
Information in this document applies to any platform.

Symptoms

***Checked for relevance on 04-Nov-2010***

ORA-00054: RESOURCE BUSY AND ACQUIRE WITH NOWAIT SPECIFIED" Using .NET 2.0

Also occurs using Microsoft System.Data.OracleClient

A statement runs within a TransactionScope code block.
The TransactionScope is not completed.

e.g. the underlying distributed transaction is rolled back.

   When trying to run a TRUNCATE command against the same table (also occurs on DELETE)
an exception occurs "ORA-00054: resource busy and acquire with NOWAIT specified"

   This is despite waiting for the transaction to complete and testing for the TransactionCompleted event.

 Sample code as reference.

1. Create a new windows console project

2. Add references for Oracle.DataAccess and System.Transactions

3. in SQL*Plus :

   CREATE TABLE TEST (ID NUMBER);

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
//using System.Data.OracleClient;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Transactions;
using System.Diagnostics;

namespace ConsoleApplication1
{
    class Program
    {
        static Boolean blnWaiting = true;
        static void Main(string[] args)
        {
            try
            {
                string _connectionString = "Password=tiger;User ID=scott;Data Source=";
                OracleConnection cn = new OracleConnection(_connectionString);
                using ( //CommittableTransaction ctx = new CommittableTransaction()
                    TransactionScope transactionScope = new TransactionScope()
                    )
                {
                  Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler(Current_TransactionCompleted);

                    cn.Open();
                    OracleParameter outId = new OracleParameter("out_id", OracleDbType.Int32);
                    outId.Direction = ParameterDirection.Output;
                    OracleCommand cm = new OracleCommand("INSERT INTO TEST  VALUES (1) RETURNING id INTO :OUT_id", cn);
                    cm.Parameters.Add(outId);
                    cm.ExecuteNonQuery();
                    Console.WriteLine("The id: [" + (OracleDecimal)outId.Value + "]");
                }

                while (blnWaiting)
                {
                    Console.WriteLine("Transaction not completed");
                    System.Threading.Thread.Sleep(1);
                }
                cn.Close();
                cn.Open();

                OracleCommand cmd = new OracleCommand("TRUNCATE TABLE TEST", cn);

                cmd.ExecuteNonQuery();
                cn.Close();
                Console.WriteLine("Press any key to continue ...");
//                Console.ReadLine();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine("Press any key to continue ...");
                //Console.ReadLine();
            }

        }

        static void Current_TransactionCompleted(object sender, TransactionEventArgs e)
        {
            blnWaiting = false;
            Console.WriteLine("Transaction completed");
        }
    }

}

 

The sample output given still returns the ORA-00054.
"
The id: [25]
Transaction not completed
Transaction not completed
Transaction completed
ORA-00054: resource busy and acquire with NOWAIT specified
"

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms