Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can I force a user's queries to always run with the hint NOLOCK? e.g. they type

select * from customer

But what is executed on the server is

select * from customer with (nolock)

This question is not:
About the various pros and cons of NOLOCK, respectfully. I know what they are, this is not the place to discuss them.

share|improve this question
1  
It would be helpful to know what you are ultimately trying to accomplish. Are you trying to prevent readers from blocking writers? –  Adam Haines Jul 31 '13 at 15:59
1  
@Adam this is exactly why I suggested snapshot. :-) Even though Paul does not seem open to discussing whether or not he should be using NOLOCK. –  Aaron Bertrand Jul 31 '13 at 16:03
7  
Do we really need a big bold header that says "THIS IS THE QUESTION:? Also we remove things like "Thanks for reading" for a reason. This is all unnecessary noise that distracts from your actual question. When a moderator edits your post, you should be prepared to justify rolling that edit back. –  Aaron Bertrand Jul 31 '13 at 16:55
10  
Please refrain from rolling the question back to the original version again. If we get into an edit war I promise the moderator team will win since we have the "nuclear option" (we can lock the post). –  JNK Jul 31 '13 at 18:15
3  
Also "disallowed" is a pretty strong word. Where did anyone say that? I was merely trying to explain why we actively remove them (especially in the process of removing other unnecessary noise). Also the help center specifically states (and explains why): Do not use signature, taglines, or greetings. You came here for free help, you're getting it, please chill out and stop complaining about minutiae. –  Aaron Bertrand Aug 1 '13 at 11:47
show 13 more comments

4 Answers 4

up vote 4 down vote accepted

Seems the nearest you can get without using SPs is using SET READ_COMMITTED_SNAPSHOT, which will affect all users of the database

EDIT: READ_COMMITTED_SNAPSHOW setting controls if the isolation READ_COMMITTED is implemented like their counterparts in PostgreSQL and FirebirdSQL (statement level snapshot of records). See more on Books Online.

EDIT #2: The OP commented in another post:

There is no control application in the scenario in mind. It's direct SSMS against SQL2008R2 Standard Edition

So, you have to make sure that all that installs are set to Read Uncommitted in Tools/Options/Query Execution/SQL Server/Advanced. I'm not an network SysAdmin myself, so I don't know if a Policy can solve it.

share|improve this answer
    
Thankyou, I'm unaware of what that does so I will have to research it. However I'm interested in answers to the true question not necessarily to enact exactly that, but for the methodology that would be involved also. –  Paul Aug 7 '13 at 13:02
2  
The only other methodology is in Aaron's answer about using SP's and/or views. –  Fabricio Araujo Aug 7 '13 at 17:41
add comment

As others have said: don't use NOLOCK willy-nilly. There are certain times where it's appropriate, and 'every query from a specific user' probably isn't one of them. Use one of the better, safer options to deal with concurrency, or your favorite LART if you've got a DBA/developer routinely wedging up the production system with awful queries.

Yes, you could adjust security so they can only select from views that select from base tables with NOLOCK hints, and I could imagine a few situations where that might be called for (operational support staff needing to occasionally examine production data in real-time, perhaps with some sensitive information/columns omitted as well).

But consider this similar discussion:

"How can I get my car up to the third floor?"

"Why would you want to do that?"

If the answer is "Because my business is on the third floor, and I need to display the car for a promo/giveaway", then you start looking at ways to get it up there.

But if the answer is "Because I would like to go home to my third floor apartment," then the proper recommendation is "So park your car in the lot, get out, and take the elevator up to the third floor."

Don't turn assumptions and biases into technical requirements, and don't latch on to your first attempt at a solution. Step back and reevaluate what you want to accomplish.

share|improve this answer
    
I know what NOLOCK is and isn't, it's covered elsewhere. This is not an Answer to this Question. –  Paul Aug 8 '13 at 8:20
3  
@Paul Sure it is, read the section about creating views. But you've still not explained why you think NOLOCK use on this scale is necessary, or the best option. –  db2 Aug 8 '13 at 14:29
2  
@Paul I think you are just going to have to put up with the anti-NOLOCK stuff and assume it is for the benefit of others who stumble on this question rather than for you who understand the issues. –  Jack Douglas Aug 9 '13 at 9:46
    
@db2 the view stuff was already covered by someone else, followed by some NOLOCK opinion I wasn't interested in, and stated thus to save you time, hence not an answer to the question. That doesn't mean you didn't say things that aren't sensible and true, it just doesn't relate to the question –  Paul Aug 12 '13 at 13:13
    
@JackDouglas, aye you're right. I was also interested in ways to alter a specific user's query. Be it to NOLOCK, or to something else. –  Paul Aug 12 '13 at 13:14
show 2 more comments

Similar to the Database Snapshot option or having a reporting mirror, but how about using AlwaysOn and read-only routing with SQL 2012? You can redirect a connection to the readable secondary via the ApplicationIntent=ReadOnly facet of the connection string.

Have your application determine the connection string depending on the user name then it is completely transparent to the user. The secondary is kept relatively up-to-date depending on latency without the overhead of maintaining a database snapshot.

Scenario 1 Senior user logs into application, application determines user should have read/write permission. Connection string created to primary database.

Scenario 2 User logs into application, application determines user should have read only permission. Connection string created including ApplicatonIntent=ReadOnly. Connection made to readable secondary database. If write-activity is required, a separate connection to primary would be required.

Availability Group Listeners, Client Connectivity, and Application Failover http://msdn.microsoft.com/en-us/library/hh213417.aspx#connecttosecondary

"Read-only workloads use row versioning to remove blocking contention on the secondary databases. All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention."

share|improve this answer
    
For the OP this seems unlikely - they are on 2008 R2 and who knows about edition. –  Aaron Bertrand Aug 1 '13 at 11:48
    
There is no control application in the scenario in mind. It's direct SSMS against SQL2008R2 Standard Edition. –  Paul Aug 7 '13 at 13:04
add comment

The real answer is:

Stop using NOLOCK

OTHER INFORMATION THAT MIGHT BE USEFUL:

Sure, deny direct access to the tables/views, and enforce data access through stored procedures (or views - thanks Martin) - which you can code to use NOLOCK.

If you need it to be per user, and you can enforce data access through procedures, you can use the idea suggested here by SQLMenace:

CREATE PROCEDURE dbo.blah
AS
BEGIN
  SET NOCOUNT ON;

  IF SUSER_SNAME() = N'something'
  BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  END

  SELECT columns FROM dbo.Customer;
END
GO

Or you could also do this:

CREATE PROCEDURE dbo.blah
AS
BEGIN
  SET NOCOUNT ON;

  IF SUSER_SNAME() = N'something'
  BEGIN
    SELECT columns FROM dbo.Customer WITH (NOLOCK);

    RETURN;
  END

  SELECT columns FROM dbo.Customer;
END
GO

If that is not possible, I thought of suggesting a plan guide, but I haven't used them to enforce NOLOCK. Also this document in Books Online suggests that this is reserved for INDEX, FORCESCAN, and FORCESEEK hints. Using a plan guide to try and add NOLOCK if it isn't already present in the query text will lead to an error. Though, the code shown in Example K seems to contradict that, so maybe it is worth a shot after all. Of course, you'll need to set up a plan guide for every query, which may be prohibitive (or impossible), and they're not exactly for the weak of heart.

And I know you don't want to hear the cons of NOLOCK, but it would be much easier (and better all around!) to force everyone to use snapshot.

To address Kenneth's suggestion of using a logon trigger: in my testing this SET option can be specified in a logon trigger, but like setting it in a stored procedure, the setting reverts to the server default when the trigger goes out of scope. So no, there doesn't seem to be a way to set a session's isolation level from a logon trigger.

share|improve this answer
3  
Or enforce access through views which contain NOLOCK –  Martin Smith Jul 31 '13 at 15:36
2  
@Martin thanks, yes you can use views too, though my preference is stored procedures. :-) –  Aaron Bertrand Jul 31 '13 at 15:39
1  
You can't create a view/procedure with (NOLOCK) and tell only user a to use that specific view/procedure (or better yet, enforce that via permissions)? –  Aaron Bertrand Jul 31 '13 at 15:44
2  
Anyway, no, I can't think of any way to force hints to be used just because the query is executed by a specific user (if we had INSTEAD OF SELECT triggers, that might be one way, but we don't). There are lots of things you can enforce for a user that way via Resource Governor, but a locking hint is not among them. –  Aaron Bertrand Jul 31 '13 at 15:47
2  
@Paul Bear in mind that the bold "Don't use NOLOCK" is not addressed to you as you have made clear that you know its cons. There can be thousands or millions readers that could read this in the future and that's probably why Aaron added that big bold notice. –  ypercube Aug 1 '13 at 8:37
show 9 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.