C # multithreading - is there a need for locking when writing to the database?

Question

I have a multithreaded C # 2.0 application where each thread writes some results to a SQL Server 2000 database table. There is only a direct INSERT command and no other logic.

My question is: do I need to put a lock around methods that write the results to the database? There is a lock at the moment, but I suspect this is slowing down the application quite a bit.

Thanks!

Answer 1

No, you do not need a lock, but you need to create a new SqlConnection for each insert. The documentation states that members of an SqlConnection instance are not thread safe.

Edit 2:. Otherwise, threads are unsafe if you cannot use one SqlConnection for multiple threads. But if each thread has its own SqlConnection , inserts are fine. The database itself is an ACID property, and therefore concurrent connections trying to insert data are safe and well defined.

Edit: But you have to be careful when deleting strange code if you cannot understand why it was written this way in the first place. This can easily have some kind of side effect, which is non-trivial if you look at the database code. But, on the other hand, if the code was written by someone who inserted some kind of random code found through Google, then it would be best to rewrite the code anyway.

Answer 2

As stated earlier, "SqlConnection is not thread safe."

Just try to open the connection once during your application, and MS SQL Server has an internal lock to prevent data loss, so you do not need to worry.

Answer 3

Since we are talking about MS SQL Server, I suppose they have internal locks to prevent data corruption. Most modern DBMSs will do this, otherwise they will have big problems.