1

In our application as per recommendation from DBA, we are adding no lock hint to each and every select query used.

So, it requires that each select query needed to be modified to set the table hint and it will be time to do manually.

As we want to use the hint across all tables in database, Is it possible to set the no lock hint ( or TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ) at database level so that each query is not needed to be modified and table hint be applied to all queries?

Satyajit
  • 2,150
  • 2
  • 15
  • 28
  • I would ask your dba to prove there is a requirement that every select statement should require a no lock hint with real examples of where he/she expects there are issues, and I'd CC in your manager to the conversation to ensure a change like this has their sign off. SQL Server is pretty good at handling things itself without the use of hints. It only in rare cases that you might want to consider using them and I would be highly suspicious if it was applied wholesale to every select query. – Tanner Nov 21 '14 at 11:22
  • I am interested in knowing the reason behind the recommendation. – NP3 Nov 21 '14 at 11:26
  • @Tanner, Thanks. We are aware of the fact that it will help only in some cases and just want to know if such feature exists or work around possible :) – Satyajit Nov 21 '14 at 11:27
  • @NP3, you can say the application use case is like a single user case where a user can add/modify the data and he only reads the data. There are other users as well who uses the same tables but data are mostly isolated among user. So, in this special case user may get benefit from it – Satyajit Nov 21 '14 at 12:10
  • If it is really the case that different users will barely read the same data, no hints should be necessary at all, and even snapshot isolation may be overkill. SQL Server is perfectly capable of locking data at the row level as appropriate, so that queries that do not touch on each other's data do not block each other at all. I really hope your DBA knows these things too... – Jeroen Mostert Nov 21 '14 at 12:12
  • Well, I know I should positively help OP on this website but in this case I would just like to raise a red flag. There doesn't seem to be a good reason for this important decision or may be it is not coming out. I think Jeroen Mostert's comment above and the answer sums it up very nicely. – NP3 Nov 21 '14 at 12:25
  • I think DBA's concerned is about 'reader writer' problem when transactions are used. if table level lock happens then select statement by a user may be blocked by update statement by another user on the same table. – Satyajit Nov 21 '14 at 12:46

1 Answers1

5

The short answer is "no". The default isolation level in SQL Server is READ COMMITTED, and there is no way to change this to UNCOMMITTED, either globally or per-database. And that's a very good thing too.

WITH (NOLOCK) is a recipe for trouble when it comes to getting accurate results from your database, and in bad cases it can even cause timeouts from queries that run forever due to data getting moved (which NOLOCK cannot protect against). See Is the NOLOCK (Sql Server hint) bad practice? for some more discussion, and some good tips on alternatives.

In particular, many applications that are reader-heavy and want to proceed without blocking can benefit from snapshot isolation. Unlike UNCOMMITTED, you can make snapshot isolation the default with the READ_COMMITTED_SNAPSHOT option. Be sure to read up on the pros and cons of snapshot isolation before you do this -- or better yet, ask your DBA to do this, as any DBA who recommends a global use of WITH (NOLOCK) has some reading up to do. Query hints should be used only as a last resort.

Community
  • 1
  • 1
Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85
  • Thanks, you have explained your answer very well. I will explore more about snapshot isolation and use it in the the sample databases in local system then decide if it helps this case – Satyajit Nov 21 '14 at 12:01