0

In other words, what are the steps to acquire locks ? Also, when 'WITH(Nolock)' hint is added to a query/'Read Uncommitted' Isolation level is used, does this avoid all or some of the overheads associated with acquiring locks ?

jarlh
  • 42,561
  • 8
  • 45
  • 63
anonxen
  • 794
  • 1
  • 8
  • 24
  • 1
    Possible duplicate of [Effect of NOLOCK hint in SELECT statements](http://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements) – Ivan Starostin Nov 28 '16 at 11:51
  • 3
    Take a look here https://www.brentozar.com/sql/locking-and-blocking-in-sql-server/ Lots of useful information. – Rich Benner Nov 28 '16 at 11:54
  • `NOLOCK` means *gimme dirty data*, not *avoid locks*. Overheads aren't caused by locks, they are caused by unoptimized statements or using accessing data you don't need. You can use snapshot isolation to avoid locking but that has a *higher* IO overhead. – Panagiotis Kanavos Nov 28 '16 at 12:32
  • What is the *actual* problem you are trying to solve? Do you have a statement that waits for too long? Are you trying to read and write in a long running transaction (which is 999.99% a bug)? Are you trying to use an ORM to read and update objects inside a single transaction (another serious bug)? – Panagiotis Kanavos Nov 28 '16 at 12:34
  • Other possibilities are bulk updates, typically caused by improperly denormalized data. Using loops or cursors instead of a single UPDATE or INSERT statement. Lazily loading dependent objects. – Panagiotis Kanavos Nov 28 '16 at 12:36

1 Answers1

0

This is too big for a specific answer, but in a nutshell SQL Server will employ various types of locks depending on request being made of it. A select might acquire one type of lock and an update will acquire another.

This link has a good 101 on the subject. SQL Server locking Basics

And this one too

Another good locking read

Neo
  • 3,309
  • 7
  • 35
  • 44