2

How do you look up a default global table locking hint?

-- Questions

  1. Are there any DMV/DMF (Dynamic Management View/Function) that return such information?
  2. And also, is there a way to change the default lock hint?

Currently I am adding nolock hint almost everywhere to prevent locks.
I'd like to avoid doing so by changing the default lock hint to nolock so that existing stored procedures do not need to change.

dance2die
  • 35,807
  • 39
  • 131
  • 194
  • This sounds very strange - is there some error in the db design that you're trying to work around? – egrunin Oct 06 '10 at 14:00
  • I am just trying to see if lack of `nolock` hint within stored procedures is cause of all query timeouts – dance2die Oct 06 '10 at 14:02

2 Answers2

4

There is no global setting. The default is always READ COMMITTED

It can be changed at

NOLOCK everywhere is utterly misguided And here too:

Edit: After comment about query timeout...

A query with NOLOCK can still consume massive CPU and IO resources. Locking isn't that big an issue. If it is, then another query is taking too long, probably consuming massive CPU and IO resources...

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • +1 I was not aware that NOLOCK could be such a bad thing sometimes. Let me check different ways to validate where the lock might be occurring. Thanks. GBN. – dance2die Oct 06 '10 at 14:37
3

I am not aware of any such global setting. IMHO even should that exist there can be little justification for using it.

You can however set the isolation levels to control whether individual transactions are able to read changes to data made by other transactions. This is done via 

SET TRANSACTION ISOLATION LEVEL
Zephyr was a Friend of Mine
  • 7,661
  • 4
  • 37
  • 39