Deadlock in ODP.NET class when using the connection pool

From: Edwin van Schaick <edwin.van.schaick_at_gmail.com>
Date: 27 Dec 2004 03:14:41 -0800
Message-ID: <c6b0fc3d.0412270314.6adf14d@posting.google.com>

In a multithreading application calls to the ExecuteNonQuery() method may hang when called via a connection, which was retrieved from the connection pool. The problem seems to appear when a connection is reused from the connection pool, which was used earlier to call a stored procedure.

I am able to reproduce the problem in a windows application, which creates a number of threads. Each thread calls the following code in a loop. After a while all but one thread are blocked on the ExecuteNonQuery() call. The problem does not occur, when connection pooling is disabled.

' Open the connection.

Dim oraConn As OracleConnection = New OracleConnection oraConn.ConnectionString = "User Id=MyUser;Password=MyPassword;Data Source=MySID;"
oraConn.Open()

' Create the command.

Dim oraCmd As OracleCommand
oraCmd = oraConn.CreateCommand()
oraCmd.CommandText = "MyProcedure"
oraCmd.CommandType = CommandType.StoredProcedure

Dim p As OracleParameter
p = New OracleParameter

p.ParameterName = "p_input"
p.OracleDbType = OracleDbType.XmlType
p.Direction = ParameterDirection.Input
p.Value = "<DATA>value</DATA>"

oraCmd.Parameters.Add(p)

p = New OracleParameter

p.ParameterName = "p_output"
p.OracleDbType = OracleDbType.XmlType
p.Direction = ParameterDirection.Output

oraCmd.Parameters.Add(p)

' Call the stored procedure.

oraCmd.ExecuteNonQuery()

' Cleanup.

oraCmd.Dispose()
oraConn.Close()

Used versions:
ODP.NET: version 9.2.040.
Oracle Database & Client: version 9.2.0.2.

Does anyone have an idea?

Thanks in advance,
Edwin van Schaick. Received on Mon Dec 27 2004 - 05:14:41 CST