Just received very interesting question in email:
“How do I apply NOLOCK hint to my whole query. I know that I can use NOLOCK at every table level but I have many tables in my query and I want to apply the same to all the tables.
I want to do something like following script.
SELECT * FROM AdventureWorks.Sales.SalesOrderDetail sod INNER JOIN AdventureWorks.Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID ORDER BY sod.ModifiedDate OPTION (NOLOCK)
When I ran it it gives me following error:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘NOLOCK’.
Please recommend.”
I just never thought of using NOLOCK such a way, when I had to use NOLOCK in such a way, I always use isolation levels.
Here is how I use it:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM AdventureWorks.Sales.SalesOrderDetail sod INNER JOIN AdventureWorks.Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID ORDER BY sod.ModifiedDate -- Set isolation level to original isolation level SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Have you ever faced situation like this when you need to set NOLOCK isolation level at all the transaction level? If yes, how did you do this?
Here is the script to know your database isolation level: SQL SERVER – Check the Isolation Level with DBCC useroptions
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
Lets say the query takes a lot of time to execute say 1 hr..then there would be impact in other places right??..
So, What say?
Thanks,
Prawin
no it will not impact the other processes
Yes if the tables are locked by that process and they are used in other places
@Pinal Dave you can use ‘nolock’ like this
SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail as sod with(nolock)
INNER JOIN AdventureWorks.Sales.SalesOrderHeader as soh with(nolock)
ON sod.SalesOrderID = soh.SalesOrderID
ORDER BY sod.ModifiedDate
but it has two drawbacks
1-another tansaction is rolled back while your query still running you will get this exception:
Could not continue scan with NOLOCK due to data movement.
2-another tansaction is rolled back after your query had ended
then you have dirty data
i recomment to use ‘readpast’ as the database engine not consider any locked rows ordata pages when returning results.
@Prawin, no it will not impact the other processes
If you have a long running query, odds are blocking side effect will be observed with NOLOCK. While bypassing locking might sound tempting, sometimes deadlock might be a better alternative
I have similar kind of requirement but I have to query production databases to run some scripts. Is it safe to work with transaction isolation levels on production databases.
Thanks,
Sai Pavan Viswanath
great penal .very basic step
Hi Pinal,
Most of the situtations where i have used NOLOCK is at the table level on reporting databases (part of Datawarehouse), very rarely use it in OLTP. Interesting viewpoint on using NOLOCK at transaction level.
Yes, I’d be interested to know if there is any impact on a production database when setting NOLOCK isolation level at the transaction level?
If not, could you clarify the need to change it at the end of the query?
Thanks.
Thanks! you are a life savor, spent a while trying to figure out how to dynamically add to a generated nhibernate query.
I have a process which pulls the data and send SMS. My concern is, if i start inserting the data in the Table from where i read and send sms and the same time i run the application which send sms, it should not read the inserted data until its committed. But in the mean time it should fetch all the previously commited transaction. How can i achieve the save.
BEGIN TRAN A
INSERT INTO test_name VALUES (‘ABC’)
SELECT * FROM test_name (Should return me data other then ‘ABC’ as its not committed yet.