Is SqlConnection / SqlCommand thread safe?

Fafnr picture Fafnr · Sep 6, 2011 · Viewed 14.6k times · Source

I am currently creating a WCF web service.

As part of its job, it will unfortunately need to do some fairly intensive computations, however these computations can fortunately be shared between calls to the webservice. In effect - we only need to do the computations once, and all later calls can get the benefit.

However, since WCF has no shared application state it seems logical to set WCF in single-instance mode. (Each client would require some of the computations in all likelyhood, forcing us to recompute them per-serssion which could be ok, or per-call which is untenable)

However, I am not very familiar with securing code for multiple threads. I have been reading up on it some, and as none of our WCF code writes to shared state (other than the computation-bit, which is easy to protect) I'm almost convinced I don't need to change anything.

There is a single snag, though - we use SqlConnection and SqlCommand to communicate with our backend, and I am not sure if I can count on these being thread safe?

EDIT: I should perhaps clarify that the Commands / Connections are always local to a method. We're talking a pattern in the vein of:

using sqlConn = new SqlConnection(...) {
 try {
  sqlConn.Open()
} catch () {
  throw new FaultException();
}
var cmd = new SqlCommand("Some SQL", sqlConn);
var reader = cmd.ExecuteReader();
//Read the stuff 
reader.Close();
//Return something
}

END EDIT

I looked up the SqlCommand class on MSDN: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx which says: "Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe."

Am I interpreting this correctly in thinking it means that MS does not guarantee that SqlCommand works in a multi threaded scenario?

If it does not, is there a thread-safe alternative?

Yes, I could just lock all database access methods in my webservice, but a) it's ugly and b) if it's not necessary I'd prefer I didn't have to :)

Cheers in advance!

Answer

jalf picture jalf · Sep 6, 2011

Am I interpreting this correctly in thinking it means that MS does not guarantee that SqlCommand works in a multi threaded scenario?

It works fine in a multi-threaded scenario as long as you use it correctly.

If several threads try to use the SAME SqlCommand, what do you think will happen? How could it possibly work?

but if different threads using different connections issue different commands to the same database, there is no problem.

The notes about thread safety on MSDN are really broken and badly worded, and must be written by someone who didn't know what thread safety is.

What they're trying to say with that message (which is tacked onto 99.9% of the classes and functions documented on MSDN) is that "Any static method of this type can be safely called concurrently by multiple threads. Any instance members on the same instance is not guaranteed to be safe if invoked concurrently by multiple threads, but accessing the same member on different objects is perfectly fine."