Hi All,
I've got a multithreaded C# 2.0 app where each thread writes some results into a SQL server 2000 database table. There is only a straight INSERT command and no other logic.
My question is - do I need to put a lock around the methods that writes the results to the database? There is a lock at the moment but I suspect that it's slowing down the app by quite a lot.
Thanks!
Since we're talking about MS SQL Server, I suppose they have internal lockings to prevent data corruption. Most modern DBMS would do that, otherwise they will have big trouble.
No you don't need any lock, but you need to create a new SqlConnection for each insert. The documentation says that instance members of SqlConnection are not threadsafe.
Edit2: By beeing NOT threadsafe in the case means that you can not have a single SqlConnection shared between several threads. But if each thread have its own SqlConnection the inserts are OK. The database itself adheres to the ACID properties and thus concurrent connections trying to insert data is safe and well defined.
Edit: But you should be careful when removing strange code if you can not figure out why it was written that way in the first place. It could easily have some kind of side effect that is non trivial to figure out by looking at just the database code. But on the other hand, if the code was written by someone who pasted some random code found through Google, then your best bet would be to rewrite the code anyway.
Are you changing some common state in the methods that write to the database? If the answer is NO, then you don't need to worry about the lock; you can remove it.
But is it really slowing your application? How many threads you are creating in your application?
As Suggested earlier "SqlConnection are not threadsafe".
Just try to Open the connection once during your application and MS SQL Server have there internal locking to prevent data lose so you need not to worry.