November 4, 2009 at 9:56 am
We had an issue recently where a query caused a lock on a database.
Looking at the query, its a select statement that has (no locks) stated. The query looks well formed.
The query looked like the following:
SELECT NAME,TEXT
INTO #TEMP
FROM DB with (nolock)
WHERE TEXT like '%Stuff%'
OR TEXT like '%Stuff2%'
OR TEXT like '%Stuff3%'
The query was run on a large number of records. Is it possible that this could still causes locks?
November 4, 2009 at 10:08 am
I would think the only locking would be on tempdb. But for me it is less clear about an INSERT/SELECT, I could see that causing some locking. Hm, well thats not a really clear answer is it..
The best way to find out is to test it. I would say that (NOLOCK) works best (and most clearly) with straight SELECT statements without data changes. And I could say that an update that used nolocks would probably not honor the hint..
Sorry, I can't be clearer, test it..
CEWII
November 4, 2009 at 10:10 am
Direct from BOL (Books Online, the SL Server Help System):
READUNCOMMITTED
Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, or present users with data that was never committed.
READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock. For more information about lock behavior, see Lock Compatibility (Database Engine).
READUNCOMMITTED, and NOLOCK, cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
Note:
Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of Microsoft SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.
November 4, 2009 at 10:13 am
So I would say the answer is yes it still causes locks and you can't prevent it..
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply