0

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?

user3453057
  • 55
  • 1
  • 8
  • add WITH NOLOCK for all tables – mohan111 Jun 11 '15 at 06:55
  • 4
    @mohan111 - atrocious advice without knowing any context. – Damien_The_Unbeliever Jun 11 '15 at 06:55
  • when transactions done on same table to avoid locks i just suggested him add NOLOCKS it will not resolve but need to follow Process @Damien_The_Unbeliever – mohan111 Jun 11 '15 at 07:01
  • 2
    Your question is too broad to give an accurate answer. Without knowing what your queries are, what the table structure is, the data / rows involved during updating, index etc, Its hard to give you complete and accurate answer for your scenario. This will help you understand the [concept](https://technet.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx) – ughai Jun 11 '15 at 07:07
  • The columns getting updated have no index but the columns in where clause have index @ughai – user3453057 Jun 11 '15 at 07:44
  • @mohan111 NOLOCK is applicable only to select ?? – user3453057 Jun 11 '15 at 12:25
  • http://www.mssqltips.com/sqlservertip/3172/avoid-using-nolock-on-sql-server-update-and-delete-statements/ – mohan111 Jun 11 '15 at 12:34

1 Answers1

0

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.

James Z
  • 12,209
  • 10
  • 24
  • 44
  • Thanks...But doesn't an update acquire table lock on the table it is updating? If so then how can other update happen on the same locked table at the same time? Should I use rowlock instead of default table lock to update the same table at the same time to avoid blocking? – user3453057 Jun 12 '15 at 13:50
  • @user3453057 No, SQL Server tries to do as little locking as possible. Searching articles with lock escalation could explain it in detail, but might be quite technical, for example https://technet.microsoft.com/en-us/library/ms184286%28v=sql.105%29.aspx – James Z Jun 12 '15 at 13:53