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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 5 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.

The READ_COMMITTED_SNAPSHOT database option controls if the isolation level READ_COMMITTED is implemented like their counterparts in PostgreSQL and FirebirdSQL (statement level snapshot of records) or using locking. Read more about that in the documentation:

Locking and Row Versioning

You commented in another post:

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

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

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

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
    
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

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

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.