20

is there some way to tell sql server to use the (nolock) hint or every select in a stored procedure?

is pretty tiresome to add it to each an every select....

John Saunders
  • 160,644
  • 26
  • 247
  • 397
opensas
  • 60,462
  • 79
  • 252
  • 386
  • 2
    You want to be careful doing this especially in high volume transaction environments because of dirty reads. – Raj More Jun 23 '09 at 23:30
  • 2
    Before doing this - make sure you understand the full consequences of dirty reads. You could end up missing records or double-reading records in queries - which in a lot of cases isn't a good thing for your app. – Scott Ivey Jun 24 '09 at 00:51

5 Answers5

19

As others quite rightly say, a global (nolock) is done using READ UNCOMMITTED.

However, before going down that route, it's worth trying READ COMMITTED SNAPSHOT first. This means your reads won't be locked by in progress inserts / updates and means that the data isn't dirty, just out of date.

Robin Day
  • 100,552
  • 23
  • 116
  • 167
  • 1
    On 2016 `SET TRANSACTION ISOLATION LEVEL READ COMMITTED SNAPSHOT` causes error: `Incorrect syntax near 'SNAPSHOT'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.` – nurettin Jul 17 '18 at 07:29
10

You want to use the following syntax:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

I found this by looking at the NOLOCK table hint located here. The WITH(NOLOCK) table hint is equivalent to setting the isolation level to be READ UNCOMMITTED. Here's the snippet from MSDN:

NOLOCK Is equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED later in this topic.

Dan Wolchonok
  • 1,930
  • 1
  • 21
  • 21
7

I think this is what you are looking for...

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

bytebender
  • 7,371
  • 2
  • 31
  • 54
5

found this....

How to force nolock hint for sql server logins

seems like the best way to achieve this is to issue a

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

any other idea???

ps

another related question...

NOLOCK vs. Transaction Isolation Level

Community
  • 1
  • 1
opensas
  • 60,462
  • 79
  • 252
  • 386
1

However, you may end up with incorrect data. On 2005, it is preferable to use snapshot isolation: "When Snapshot Isolation Helps and When It Hurts" http://www.devx.com/dbzone/Article/32957

A-K
  • 16,804
  • 8
  • 54
  • 74