CODEWITHSUNDEEP
sql-servert-sqlcoldfusion
RHPT
RHPT

Reputation: 2650

NOLOCK and Coldfusion

A co-worker informed me the other day that using NOLOCK in a cfquery is pointless if you do not include the query inside a cftransaction. He claims that SQL Server will only recognize NOLOCK inside a transaction and thus not having the query within a cftransaction makes the NOLOCK redundant.

Can someone confirm is this is true or not, because in all the CF shops in which I've worked , NOLOCK is used generously, and it is rarely ever inside a cftransaction. Am I supposed to think that all those queries in all those shops are not actually respecting the NOLOCK?

Upvotes: 4

Views: 838

Answers (3)

Dan Short
Dan Short

Reputation: 9616

All you need to do is look at the execution plans on your SQL Server to see if they're honoring the NOLOCK or not. I can confirm (from years of usage) that the NOLOCK is indeed honored. I wouldn't suggest it (check into READ_COMMITTED_SNAPSHOT_ISOLATION), but it's definitely honored, with or without a cftransaction.

Upvotes: 4

Cory
Cory

Reputation: 12824

Your co-worker in incorrect, NOLOCK is not redundant outside the context cftransaction.

If you are using the default setting in SQL Server, every query you execute is wrapped within a transaction whether you have explicitly typed it out or not. This is called "autocommit mode".

Upvotes: 3

tigeryan
tigeryan

Reputation: 162

Ben Nadel has a great explanation as well as a couple of reference articles that explains the use of NOLOCK much better then I can. However you do not have to use NOLOCK inside of a transaction to get the performance gain.

http://www.bennadel.com/blog/477-SQL-Server-NOLOCK-ROWLOCK-Directives-To-Improve-Performance.htm

Hope this helps! John

Upvotes: 0

Related Questions