SQL Server stored procedure in multi threaded environments

Question

Hi,

I need to execute some Sql server stored procs in a thread safe manner. At the moment I'm using software locks (C# locks) to achieve this but wonder what kind of features provided by the Sql server itself to achieve thread safety. It seems to be there are some table and row locking features built in to Sql server.

Also from a performance perspective what is best approach? Software locks? Or Sql Server built in locks?

Thanks, Shamika

Answer 1

The threads at your client are going to have very little to do with your database behavior. SQL Server is a multi-user database, so anyone can be doing anything to the database on a different machine at the same time, and all the thread-safety in the world is not going to help with that. SQL Server will use locks to maintain the database during transactions, to ensure that ACID rules and all your database constraints are maintained.

Typically, when you execute stored procedures on a single machine on the same thread, they will be sequential and cannot conflict. If you have multiple threads or use the async features of ADO.NET, then the procedures could conceivably interfere with each other within the SQL Server - i.e. blocking or deadlocking - but SQL Server is going to handle all that. Obviously, in this scenario, any code you have around them in your app needs to be threadsafe - i.e. you can't use the same IAsyncResult handle for both requests, you need to ensure that both threads are not attempting to modify the same data as a result of the completions, etc.

You need to lay out your requirements a bit more clearly, because these are two completely different locking scenarios.