3

In my .Net application I'm experiencing issue with Oracle CommandTimeout. When I querying to the locked table using text command without bound parameters CommandTimeout fires fine as expected. But If I introduce and bind any parameter to the command current thread hangs and timeout exception will never fire (until the lock on the table is released).

I tried the latest ODAC 12.2c Release 1 (12.2.0.1.0) (Oracle.ManagedDataAccess and Oracle.DataAccess) downloaded from http://www.oracle.com/ as well as Oracle.ManagedDataAccess from nuget. It was tested on 11g Express Edition and Oracke 12c databases without success.

Here is my c# code:

I'm locking table with query:

LOCK TABLE document  
IN EXCLUSIVE MODE NOWAIT; 

C# code:

using System;  
using System.Data;  
using Oracle.ManagedDataAccess.Client;  

namespace ConsoleApplication  
{  
    class Program  
    {  
        static void Main(string[] args)  
        {  
            using (var connection = new OracleConnection("oracle connection string"))  
            using (var command = new OracleCommand("update Document set pagecount = 0 where rownum = :rn", connection))  
            {  
                connection.Open();  
                command.CommandType = CommandType.Text;  
                command.CommandTimeout = 5;  
                command.Parameters.Add(new OracleParameter("rn", 1));  

                try  
                {  
                    command.ExecuteNonQuery();  
                }  
                catch (Exception ex)  
                {  
                    Console.WriteLine("Exception: " + ex.Message);  
                }  
            }  
        }  
    }  
}

Works fine if change this

using (var command = new OracleCommand("update Document set pagecount = 0 where rownum = :rn", connection))

to

using (var command = new OracleCommand("update Document set pagecount = 0 where rownum = 1", connection))  

but I need to use binding of parameters.

Configuration file:

<?xml version="1.0" encoding="utf-8"?>  
<configuration>  
  <configSections>  
    <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>  
    </configSections>  
  <startup>  
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2"/>  
  </startup>  
  <system.data>  
    <DbProviderFactories>  
      <remove invariant="Oracle.ManagedDataAccess.Client"/>  
      <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver"  
        type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>  
    </DbProviderFactories>  
  </system.data>  
  <oracle.manageddataaccess.client>  
    <version number="*">  
      <settings>  
        <setting name="Disable_Oob" value="on" />  
      </settings>  
    </version>  
  </oracle.manageddataaccess.client>  
</configuration>

Also I tried Disable_Oob with "on" and "off" options but it had no effect.

Another example using powershell:

Add-Type -Path "Oracle.DataAccess.dll"  

$connectionString = "connection string here"  

#Timeout works fine (command without parameters)  
try {  
    $dbConn = New-Object Oracle.DataAccess.Client.OracleConnection  
    $dbConn.ConnectionString = $connectionString  
    $dbConn.Open()  

    $tran = $dbConn.BeginTransaction()  

    $cmd = New-Object Oracle.DataAccess.Client.OracleCommand  
    $cmd.Connection = $dbConn  
    $cmd.Transaction = $tran  
    $cmd.CommandTimeout = 5  
    $cmd.CommandText = "update Document set pagecount = 0 where rownum = 1"  

    $cmd.ExecuteNonQuery()  
}  
finally {  
    if ($tran) {$tran.Rollback()}  
    $dbConn.Close()  
    Write-Host "Timeout works fine for command without parameters."  
}  

#Timeout fired only after table lock is released (command with parameters)  
Write-Host "Attempting to execute command with parameter"  

try {  
    $dbConn = New-Object Oracle.DataAccess.Client.OracleConnection  
    $dbConn.ConnectionString = $connectionString  
    $dbConn.Open()  

    $tran = $dbConn.BeginTransaction()  

    $cmd = New-Object Oracle.DataAccess.Client.OracleCommand  
    $cmd.Connection = $dbConn  
    $cmd.Transaction = $tran  
    $cmd.CommandTimeout = 5  
    $cmd.CommandText = "update Document set pagecount = 0 where rownum = :rn"  
    $cmd.Parameters.Add(":rn", 1);  

    Write-Host "Hangs here if table is locked with exclusive lock."  
    $cmd.ExecuteNonQuery()  
}  
finally {  
    if ($tran) {$tran.Rollback()}  
    $dbConn.Close()  
    Write-Host "Never comes here if lock is not released"  
}  

I will appreciate any suggestions, thoughts, help. Thank you.

Yauhen K
  • 31
  • 2

0 Answers0