Is SqlConnection / SqlCommand thread safe?

Question

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!

Jaimeejaimes

If you do not share the same instance across threads and the static parts of the class are thread safe then you don't have a problem – Zoonosis

Don't share these instances across threads, open them, run your query, and dispose as soon as possible. Connection pooling will handle the connection optimisations. – Woodwork

Answer 1

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."

Bizerte

Agreed - several threads using the SAME SqlCommand would never work. What I was unsure of, was if it was safe to have, as you put it, "different threads using different connections issue different commands to the same database". Basically, how would I know SqlCommand doesn't have some insane internal static state that would be a problem? Even as I write this, I realize how stupid that sounds, however... So, thank you for your answer! My code should be OK :-) – Jaimeejaimes

@jalf. How about different threads issuing different commands but using one static singleton connection? I have found this to be working but suspect it might break in some cases. – Cita

Answer 2

I'm not 100% sure what you're trying to do simultaneously with SqlCommand, but whatever the internal thread-safety, you'll surely have problems purely because using the SqlCommand requires it to maintain state, e.g.

SqlCommand cmd = myConnection.CreateCommand();
cmd.CommandText = "......";
cmd.Parameters.Add(.....);
cmd.ExecuteNonQuery();

If you're trying to share the same command through multiple threads, you'd have to lock something while you were using it.

As far as SqlConnection goes, it will only allow you to have a single query open at a time, so if you're using DataReaders, you'd again have to lock something. Using multiple connections/commands is essential if you want to run multiple things the same time.

I'm also not sure what you mean when you say WCF has no shared application state - this is not necessarily true, it will depend on how you are hosting your WCF application. If it is a WCF service hosted under IIS with aspNetCompatibilityEnabled="true" set, you still have the Application object you would get in a web site. There are other options if you're not using aspNetCompatibility as well.

Rhody

I realized I was being pretty unclear, so I just updated with an example of what I'm trying to do... I don't share commands / connections between method invocations. I always new the commands / connections in each method. What I am unsure of, is if they internally have some structure that'll bite me in the ass. I did not know about aspNetCompatibilityEnabled though - thanks for that! I am very unsure about the smart way to structure this, and sometimes it really helps you can throw stuff out there to people who have been doing this stuff for longer :-) – Jaimeejaimes

Answer 3

just use connection and command from knly one thread and dont care of app level thread issue on those. sql server is good enough to handle concurrency for you with no need to lock in your code. .Net connection pool is also there to retrieve valid connections fast.

I am not saying the whole WCF layer you make should not care of threads, but its DAL has to work relying on db locks not .net locks.

Kitsch