0

I had question above question. We are using with nolock through out the application. In some cases I need to work select faster, what ever the effect.

So select with(TABLOCKX) will be faster or with(nolock)?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Amol Patil
  • 985
  • 2
  • 11
  • 43
  • 1
    don't use " nolock through out the application." - in high insert scenarios you might get inaccurate values. It's possibly OK on reporting queries if you do not care about the results being 100% accurate or know that activity won't be causing page splits. – Mitch Wheat Nov 18 '16 at 08:55
  • If your app locks the database everywhere, then it may have to wait in order to acquire the resource, so in this case I would expect slower performance. But if you need a database lock from a logical point of view, then of course you should use it. – Tim Biegeleisen Nov 18 '16 at 08:56
  • thanks for comments, our data is not much sensitive, so there is no need to locking, there will be no effect of dirty transactions. I just want to know in select, with lock will be give result faster or with(nolock). – Amol Patil Nov 18 '16 at 08:57
  • 1
    If no one else is using the critical section, then the incoming thread waiting on the database probably won't spend noticeable extra time acquiring the lock, or releasing it. But then again, once it has the lock, anyone else who also waits on that lock would be delayed. Your question is a bit vague. – Tim Biegeleisen Nov 18 '16 at 08:59
  • 2
    Read Aaron Bertrand's [Bad habits : Putting NOLOCK everywhere](https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/) – Zohar Peled Nov 18 '16 at 11:05
  • 1
    I would suggest as others have to only use nolocks when absolutely necessary. Back in the day it was popular when the SQL SERVER engine wasn't as good as it is now. I use nolocks only for non critical report queries. – Neo Nov 18 '16 at 14:16

2 Answers2

1

To answer your question, the with (nolock) table hint will be faster.

NOLOCK typically (depending on your DB engine) means give me your data, and I don't care what state it is in, and don't bother holding it still while you read from it. It is all at once faster, less resource-intensive, and very very dangerous.

As explained very well here NoLock

SteveCav
  • 6,649
  • 1
  • 50
  • 52
Neo
  • 3,309
  • 7
  • 35
  • 44
0

Nolock means you can read some locked rows (with shared locks). But you still have to wait on other locks.

Tablockx means you block whole table with exclusive lock for other queries - other session cannot make locks, you cannot be blocked after you block whole table. Tablockx is mostly used for rapid inserts.

Avoid using nolock everywhere. Try to avoid exclusive locks for longer times or try minimalize your blocking and then you don't need nolocks.

Deadsheep39
  • 561
  • 3
  • 16