0

I am getting up to speed on using transactions and "with (nolock)".

It appears to me that "with (nolock)" is used only within transactions. Is that correct?

The reason I ask is that within SQL at my company, they use it as a standard on queries, but transactions are not actually used.

So is there any point to using this? I have asked about it, and was told that it prevents the tables from being locked which prevents other queries from executing. It seems to me that this would be true if transactions were being used, but they are not.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Greg Gum
  • 33,478
  • 39
  • 162
  • 233
  • 4
    If there is no explicit transaction, the statement will always be an implicit transaction. However you should worried about the consequences of using nolock, it should most definitely not be used "as standard". Hopefully you have read [this](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) among other good articles. – Dale K Sep 01 '21 at 05:41
  • 2
    Without an explict transaction, each statement executes in an implicit autocommit transaction, with locking necessary to honor the transaction isolation level (which can be overridden with hints). A better standard to avoid long-term blocking is snapshot isolation or turning on the READ_COMMITTED_SNAPSHOT database option. – Dan Guzman Sep 01 '21 at 09:56
  • `NOLOCK` does not prevent schema locks, and only offers a moderate speed improvement. **Don't use it unless you really know what you are doing,** it can cause completely incorrect results, such as missing or double-counted rows. – Charlieface Sep 01 '21 at 10:00

0 Answers0