Simultaneous DML operations on same table based on different where clause

Question

Is it possible in SQl Server to run 3 jobs at the same time (say 3:00 am) which updates the same table? The updates have different where clause. What I wanted to know will the tables get deadlocked or each job will run independently of each other. Also when an update runs does it lock the whole table?

Answer 1

It is possible to update the same table at the same time if you're updating different rows, but deadlocks or at least blocking can happen at least in following cases:

1. You don't have an index for the where clause, so the updates can block each other because the whole clustered index needs to be scanned
2. You have other indexes that need to be updated because of this update, and deadlocks / blocking happen on those indexes
3. You have other transactions (update, select etc) running that have locks to pages process A is trying to update, and that process is waiting for pages already locked by process B
4. You're updating so many records that lock escalation happens into a table lock

There's probably other cases too. Blocking is of course the most common thing that can happen, but it can lead to deadlocks too.