OracleConnection Thread Safety

[Guest]

Hi. I posted a connection pooling question on Friday evening; I received some good answers, but I still have a final question

I would like to know if the OracleConnection class is safe for multi-threading applications. This is what MS has to say about OracleConnection wrt Thread Safety
"Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.
I don't really understand what they say...static members are thread safe, but new instance members are not thread safe?!? That makes no sense; I would actually believe the reverse
Can someone please explain it to me

Thank
Carl

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

Not sure. For the best safety, I would aim for the ODP, which you can
download from Oracle's TechNet (http://otn.oracle.com).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************

Hi. I posted a connection pooling question on Friday evening; I received

some good answers, but I still have a final question.

I would like to know if the OracleConnection class is safe for

multi-threading applications. This is what MS has to say about
OracleConnection wrt Thread Safety:

"Any public static (Shared in Visual Basic) members of this type are

thread safe. Any instance members are not guaranteed to be thread safe."

I don't really understand what they say...static members are thread safe,

but new instance members are not thread safe?!? That makes no sense; I would
actually believe the reverse.

 

[David Browne]

Hi. I posted a connection pooling question on Friday evening; I received

some good answers, but I still have a final question.

I would like to know if the OracleConnection class is safe for

multi-threading applications. This is what MS has to say about
OracleConnection wrt Thread Safety:

"Any public static (Shared in Visual Basic) members of this type are

thread safe. Any instance members are not guaranteed to be thread safe."

I don't really understand what they say...static members are thread safe,

but new instance members are not thread safe?!? That makes no sense; I would
actually believe the reverse.

Can someone please explain it to me?

Objects have state. An OracleConnection might have states like "closed",
"open", "sending data to server", "waiting on server", "fetching results"
etc. If one thread runs a query using an OracleConnection it might go from
"open" to "sending" to "waiting" to "receiving" and back to "open" over the
course of a couple of seconds. While that's happening, you must guarantee
that another thread does not try to run a query. In practice this means
that one thread should open, use and then close each OracleConnection
object.

David

 

[Miha Markic [MVP C#]]

Hi Carl,

No, it is not.
Make sure you create a new oracleconnection instance for each thread.
Why would you believe the reverse?
Normally, static member do not use shared variables while instance memebers
do.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi. I posted a connection pooling question on Friday evening; I received

some good answers, but I still have a final question.

I would like to know if the OracleConnection class is safe for

multi-threading applications. This is what MS has to say about
OracleConnection wrt Thread Safety:

"Any public static (Shared in Visual Basic) members of this type are

thread safe. Any instance members are not guaranteed to be thread safe."

I don't really understand what they say...static members are thread safe,

but new instance members are not thread safe?!? That makes no sense; I would
actually believe the reverse.

 

[Guest]

Thanks everyone, but I'm still not 100% clear on the MS definition..
I might start to sound annoying, but I'll have to explain it in detail and I'm afraid I can't

"Any instance members are not guaranteed to be thread safe."
Much likely I'll be asked the following question: "so, you make an instance of this class by calling new OracleConnection (this is how you get an instance member, right?!), now you run the code on it in a multi-threading application... and how is it supposed to work correctly when MS says that it is not thread safe???

I appreciate your patience and thanks again
Car


PS This is my code

Public Shared Function GetUserPwd(ByVal userID As String)
As Strin
Dim pwd As Strin
Dim oraConnection As New OracleConnection(connection string
Dim oraCommand As New OracleComman
With oraComman
.CommandText = "SELECT password FROM t_member WHERE userid='"
& userID & "'
.Connection = oraConnectio
End Wit
oraConnection.Open(
Tr
pwd = CType(oraCommand.ExecuteScalar, String
Catch ex As Exceptio
Thro
Finall
oraConnection.Close(
End Tr
Return pw
End Functio

 

[Miha Markic [MVP C#]]

Hi Carl,

Thanks everyone, but I'm still not 100% clear on the MS definition...
I might start to sound annoying, but I'll have to explain it in detail and I'm afraid I can't.

"Any instance members are not guaranteed to be thread safe."
Much likely I'll be asked the following question: "so, you make an

instance of this class by calling new OracleConnection (this is how you get
an instance member, right?!),

Yes.

now you run the code on it in a multi-threading application... and how is
it supposed to work correctly when MS says that it is not thread safe???"

It won't.
Either make synchronization by yourself (note that you can't have two
operations in the same time on the same connection instance) or create an
instance per thread.

 

[David Browne]

Thanks everyone, but I'm still not 100% clear on the MS definition...
I might start to sound annoying, but I'll have to explain it in detail and I'm afraid I can't.

"Any instance members are not guaranteed to be thread safe."
Much likely I'll be asked the following question: "so, you make an

instance of this class by calling new OracleConnection (this is how you get
an instance member, right?!), now you run the code on it in a
multi-threading application... and how is it supposed to work correctly when
MS says that it is not thread safe???"

PS This is my code:

Public Shared Function GetUserPwd(ByVal userID As String) _
As String
Dim pwd As String
Dim oraConnection As New OracleConnection(connection string)
Dim oraCommand As New OracleCommand
With oraCommand
.CommandText = "SELECT password FROM t_member WHERE userid='" _
& userID & "'"
.Connection = oraConnection
End With
oraConnection.Open()
Try
pwd = CType(oraCommand.ExecuteScalar, String)
Catch ex As Exception
Throw
Finally
oraConnection.Close()
End Try
Return pwd
End Function

A method is said to be "thread safe" if it can be accessed by multiple
threads in any order. In particular a method which only accesses its input
parameters and local variables is generally thread safe, even if it has
arguments or local variables of types which are not thread safe. In
particular your GetUserPwd _is_ thread safe since it creates an
OracleConnection as a local variable, uses it and closes it. If another
thread enters GetUserPwd, that thread will have a _different_
OracleConnection.

Since OracleConnection is not thread safe, you must guarantee that each
thread gets its own OracleConnection. Declaring the OracleConnection as a
local variable in a shared(static) function does that.

David

 

[Guest]

Thanks.

 

[Guest]

Right... and my code does that, right?
Thanks.Carl.