2

In my SQL tempOrder table has millions of records and with 10 trigger to update tempOrder table with another table's update.

So I want to apply apply with(NOLOCK) on table.

I know with

SELECT * FROM temporder with(NOLOCK)

This statement I can do. But is there any way to apply with(NOLOCK) directly to the table from SQL Server 2008.

Jerry Coffin
  • 476,176
  • 80
  • 629
  • 1,111
MANISHDAN LANGA
  • 2,227
  • 6
  • 29
  • 43
  • Are you asking if you can do something to the table so that some lock types are never gotten? – rjdevereux Feb 27 '12 at 11:44
  • 6
    Why are people not happy with shooting their foot with a gun these days? They ask for a bazooka at least. – GSerg Feb 27 '12 at 11:48
  • @GSerg I agree, but it's hard to tell how experienced people are. Sometimes people know the risks, sometimes they think they've found the silver bullet to slow queries. – Andreas Ågren Feb 27 '12 at 12:30

3 Answers3

7

The direct answer to your question is NO -- there is no option to to tell SQL to never lock tableX. With that said, your question opens up a whole series of things that should be brought up.

Isolation Level

First, the most direct way you can accomplish what you want is to use with (nolock) option or SET TRANSACTION ISLOATION LEVEL READ UNCOMMITTED (aka chaos). These options are good for the query or the duration of the connection respectively. If I chose this route I would combine it with a long running SQL Profiler trace to identify any queries taking locks on TableX.

Lock Escalation

Second, SQL Server does have a table wide LOCK_ESCALATION threshold (executed as ALTER TABLE SET LOCK_ESCALATION x where X is the number of locks or AUTO). This controls when SQL attempts to consolidate many fine grained locks into fewer coarse grained locks. Said another way, it is a numeric threshold for converting how many locks are taken out on a single database object (think index).

Overriding SQL's lock escaltion generally isn't a good idea. As the documentation states:

In most cases, the Database Engine delivers the best performance when operating with its default settings for locking and lock escalation.

As counter intuitive as it may seem, from the scenario you described you might have some luck with fewer broad locks instead of NOLOCK. You'll need to test this theory out with a real workload to determine if its worthwhile.

Snapshot Isolation

You might also check out the SNAPSHOT isolation level. There isn't enough information in your question to know, but I suspect it would help.

Dangers of NOLOCK

With that said, as you might have picked up from @GSerg's comment, NOLOCK can be evil. No-Lock is colloquially referred to as Chaos--and for good reason. When developers first encounter NOLOCK it seems like allowing dirty reads is the only implication. There are more...

  • dirty data is read for inconsistent results (the common impression)
  • wrong data -- meaning neither consistent with the pre-write or post-write state of your data.
  • Hard exceptions (like error 601 due to data movement) that terminate your query
  • Blank data is returned
  • previously committed rows are missed
  • Malformed bytes are returned

But don't take my word for it :

Community
  • 1
  • 1
EBarr
  • 11,826
  • 7
  • 63
  • 85
2

You cannot change the default isolation level (except for snapshot) for a table or a database, however you can change it for all read queries in one transaction:

set transaction isolation level read uncommitted

See msdn for more information.

Andreas Ågren
  • 3,879
  • 24
  • 33
2

this is not a table's configuration.

If you add (nolock) to the query (it is called a query hint) you are saying that when executing this (and only this) query, it wont create lock on the affected tables.

Of course, you can make this configuration permanent for the current connection by setting a transaction isolation level to read uncommitted for example: set transaction isolation level read uncommitted. But again, it is valid only until that connection is open.

Perhaps if you explain in more details what you are trying to achieve, we can better help you.

Diego
  • 34,802
  • 21
  • 91
  • 134