NoLock Hint on a Read-only database

Question

NoLock Hint on a Read-only database

chandan_jha18

SSChampion

Points: 11330
More actions
August 26, 2011 at 5:50 am

#390283

Hi,
I have a database which is used just for reporting i.e only select queries are used here. I am noticing that developers are creating procedures using no-lock hint. My guess is that it is used in cases where dirty reads are acceptable and you do not want to block write operations in your normal OLTP database.
But in a read only database, a normal select query will cause a shared lock but other select queries can still run. so I do not see any use of this hint.
Please give me your opinion.
Thanks
chandan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

In a read-only database, no locks will be taken ever. SQL doesn't need to, it knows nothing can ever change data (it's defined read-only)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

chandan_jha18 SSChampion Points: 11330 More actions · Answer 2

GilaMonster (8/26/2011)
In a read-only database, no locks will be taken ever. SQL doesn't need to, it knows nothing can ever change data (it's defined read-only)

sorry. By read only, I mean to say that it is a replicated subscriber built for reporting where we give access only as read only. Only the DBAs or the replication agent can change the data if needed.

so do you think nolock hints have any role to play here? I don't see any performance benefits as there won't be any user inserts or updates.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Probably no benefits, but the same risks of incorrect data. The replication is changing the DB, so you can get all the missed-rows, duplicate-rows that you would in a normal DB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

In that case, you have the risk that tables will be updated between when your SELECT statement starts and when it finishes, possibly causing dirty reads. Using NOLOCK may increase concurrency and thereby increase performance, but it will also produce wrong data - maybe not today or tomorrow, but eventually. Use with extreme caution. I would insist that the developers comment their code so that use of the hint is not blindly copied by someone who looks at the code and thinks it's the right thing to do.

John