I have a SQL Server table and this table will be updated by a batch job every 5 minutes using backgroundworker multithread calls. Also i am using thread lock when i am inserting the table via batch job. The same data can be accessed by the user of the application simultaneously. I have my business logic in C#. What is the best and optimized solution for this? Can i use thread lock on this situation or not?
3
- 2Just let SQL Server handle this - that's what it's made for! Just try to avoid updating more than 5'000 rows in a single batch run - otherwise, your entire table might get an exclusive lock placed on it and no reading of data would be possible anymore until that (X) lock is freed againmarc_s– marc_s2013-05-15 18:58:35 +00:00Commented May 15, 2013 at 18:58
- I am doing some explicit lock via thread for batch job sql update. Is this required or sql will handle this? private Object thisLock = new Object(); lock (thisLock) { <<sql update>> }Selvakumar M.M– Selvakumar M.M2013-05-15 19:13:34 +00:00Commented May 15, 2013 at 19:13
- 1That's just .NET locking - has nothing to do with and no impact on the database AT ALL!marc_s– marc_s2013-05-15 19:14:04 +00:00Commented May 15, 2013 at 19:14
Add a comment |
1 Answer
What's the problem you have (or that you anticipate)?
SQL Server is made and optimized for handling lots of concurrent connections and users updating, inserting, reading data. Just let it handle the work!
When your background worker thread updates the table, it will take exclusive (X) locks on those rows that it updates - but only on those rows (as long as you don't update more than 5000 rows at once).
During that time, any other row in the table can be read - no problem at all, no deadlock in sight....
The problem could occur if you update more than 5000 rows in a single transaction - then SQL Server will do a Lock escalation to avoid having to keep track of too many locks, and it will lock the entire table with an (X) lock. Until the end of that update transaction - no reads are possible anymore - but those are normal transactional locks - NOT deadlocks.
So where is your problem / your issue?
2 Comments
Selvakumar M.M
I am doing some explicit lock via thread for batch job sql update. Is this required or sql will handle this? private Object thisLock = new Object(); lock (thisLock) { <<sql update>> }
marc_s
@SelvakumarM.M: SQL Server handles all of this automatically - I would try to avoid doing any explicit locking unless you absolutely must....