CODEWITHSUNDEEP
c#sql-serversql-server-2008lockingdeadlock
Selvakumar M.M
Selvakumar M.M

Reputation: 1

Simultaneous lock and use of SQL Server 2008 table in a c# project?

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?

Upvotes: 0

Views: 180

Answers (1)

marc_s
marc_s

Reputation: 754963

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?

Upvotes: 1

Related Questions