0

It's more theoretical question but I need to do something with it.

I have web interface and SQL Server 2012 behind it. Which is given me a lot of problem on UPDATE

I have one table let's call it Contract which has 100+ columns.

When user from web interface is doing an UPDATE it's exclusively locking whole table instead of only updated row, so the other users can't do inserts or updates some times selects which sometimes is causing multiple deadlocks.

Usually update looks like 

UPDATE Contract
set 
param1=@1,
param2=@2,
param3=@3,
param4=@4,
.....
where id=@id

How to fix this lock or maybe how to tell to SQL Server lock only row on updates?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andrey
  • 1,629
  • 13
  • 37
  • 65
  • Do you have an index on `id`? – Felix Pamittan Mar 18 '15 at 08:06
  • 1
    SQL Server only locks **individual rows** when doing an `UPDATE` - unless you do an `UPDATE` over more than 5000 rows in a single transaction - in that case, a **lock escalation** would lock the entire table. Does your table have a **primary key**, and if so: which column is it? Is that primary key also the clustered index? – marc_s Mar 18 '15 at 08:07
  • @wewesthemenace< yes I have clustered and non -clustered indexes. – Andrey Mar 18 '15 at 08:10
  • 2
    This other SO question (and most definitely the expert answers!) might also be interesting to you: http://stackoverflow.com/questions/2248011/when-i-update-insert-a-single-row-should-it-lock-the-entire-table – marc_s Mar 18 '15 at 08:10
  • I was going to refer you to the same question. :) – Felix Pamittan Mar 18 '15 at 08:11
  • @marc_s, I thought the same thing in has to Update only individual rows, but its locking whole table i was checking but running test and always had exclusive lock on table not on the row. or i am running my test wrong way – Andrey Mar 18 '15 at 08:13
  • @Andrey: can you show us a query output that **shows** the full table locking? Are those really `X` locks? Or just `IX` (intent exclusive)? – marc_s Mar 18 '15 at 09:17
  • What is the value for allow_row_locks in sys.indexes for the table in question? – dean Mar 18 '15 at 10:13

0 Answers0