1

I use SQL Server and have simple table:

Person (ID bigint, Name nvarchar(255))

It contains 1000 rows (for example). I'm using this SQL query:

Update Person
Set Name = 'Jason' 
Where Id = 100

I want to understand how SQL Server will do read/write locking. Will it block one row (which I'm updating) or will it block all Person table?

Will the situation change if I will use query?

Update Person
Set Name = 'Jason' 
Where Name = 'Piter'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Evgeny Semyonov
  • 131
  • 1
  • 4
  • Take a look at [this question](http://stackoverflow.com/questions/3114826/is-it-possible-to-force-row-level-locking-in-sql-server).. – Peter Schneider May 09 '15 at 17:15

1 Answers1

2

I suggest you to read this SQL Server Transaction Locking and Row Versioning Guide.

In general, your query will take update lock on the row which is updated, unless server decides that it should use lock escalation and locks page or table/index.

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197