]]>By: Fardinhttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-351710
Fri, 21 Sep 2012 07:44:11 +0000http://blog.sqlauthority.com/?p=12645#comment-351710I 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.

]]>By: shawnhttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-279964
Fri, 27 Apr 2012 02:38:19 +0000http://blog.sqlauthority.com/?p=12645#comment-279964Thanks! you are a life savor, spent a while trying to figure out how to dynamically add to a generated nhibernate query.

]]>By: Anonymoushttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-130560
Fri, 22 Apr 2011 17:19:02 +0000http://blog.sqlauthority.com/?p=12645#comment-130560If 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

]]>By: Craighttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-129932
Tue, 19 Apr 2011 07:18:59 +0000http://blog.sqlauthority.com/?p=12645#comment-129932Yes, 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?

]]>By: Ramdashttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-129558
Mon, 18 Apr 2011 14:17:04 +0000http://blog.sqlauthority.com/?p=12645#comment-129558Hi 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.

]]>By: madhivananhttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-129493
Mon, 18 Apr 2011 10:37:48 +0000http://blog.sqlauthority.com/?p=12645#comment-129493Yes if the tables are locked by that process and they are used in other places

]]>By: pinaldavehttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-129335
Sun, 17 Apr 2011 11:22:27 +0000http://blog.sqlauthority.com/?p=12645#comment-129335yeah I know that – but the point was to have it for whole transaction and not at individual table level.

]]>By: Sai Pavan Viswanathhttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-129329
Sun, 17 Apr 2011 10:03:07 +0000http://blog.sqlauthority.com/?p=12645#comment-129329I 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.

]]>By: Taha Aminhttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-129325
Sun, 17 Apr 2011 09:37:05 +0000http://blog.sqlauthority.com/?p=12645#comment-129325@Prawin, no it will not impact the other processes

]]>By: Taha Aminhttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-129324
Sun, 17 Apr 2011 09:36:21 +0000http://blog.sqlauthority.com/?p=12645#comment-129324@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.

]]>By: prawinhttp://blog.sqlauthority.com/2011/04/17/sql-server-applying-nolock-hint-at-query-level-nolock-for-whole-transaction/#comment-129306
Sun, 17 Apr 2011 07:16:18 +0000http://blog.sqlauthority.com/?p=12645#comment-129306Lets 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?