[RESOLVED] Updating SQL database from multiple threads

[chris128]

Is it safe to update an SQL database from multiple threads at the same time? I have a method which runs on up to 25 thread pool threads at the same time and it attempts to write to a text file and update an SQL database - I assumed that writing to the file would not work properly if all threads tried to write to it at the same time, so I have used SyncLock for that part of the method.
Just wondering if I need to do the same thing for the part of the method that updates the SQL database? or is the framework (or SQL server) smart enough to either execute multiple update statements on the same table at the same time without a problem or queue them?

Thanks
Chris

[chris128]

Just a quick update - I have been doing some research on this and it seems that people say its ok to update from different threads as long as each thread has its own SqlConnection. I was not doing this, I was just using one class level SqlConnection object and each thread opened and closed it as needed. I have now changed this so that each thread creates and opens its own SqlConnection object and then at the end of the method it closes/disposes this. Should that be ok?

[techgnome]

It's no different if several people update the database at the same time. From the code side, there isn't any issues. On the database side you'll need to take into consideration table locks, page locks, deadlocking issues, as well as potential concurrency issues if they try to modify the same record.

-tg

[chris128]

Ah ok, thanks. None of the threads that run at the same time will be attempting to modify the same records (there is a record in the table for each PC and there will be a thread running for each PC, so the threads only modify the record that relates to that PC)

However, they could potentially be inserting records at the same time - I dont think that would cause a problem though would it?

[techgnome]

Again, it's no different than multiple users hitting the database at the same time. Just keep the transactions as short as you possibly can.

-tg

[chris128]

They are very short, at most there will be one insert and one update to this table from each thread and thats it.

So in conclusion: as long as each thread has its own SqlConnection instance then its all good

Thanks TG
Chris