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.