asked
With (nolock) locked by with (tablock)?
Hi, Let's say I have a table X (a huge one), I frequently run a truncate on it and after that I insert data using Insert into with (tablock). In parallel I usually run another query, a select statement with (nolock), but seems to be that when both process run at the same time, the select with (nolock) waits until the other one is finished. Is that ok? I mean is there some sort of hint conflict? or should I start looking at a different place to find the real problem? The problem should not be the truncate because only takes a few seconds. Also if I remove the with (tablock) in the insert the select waits, too. Any ideas here? Thanks in advance!
You don't. Rewrite the query, or review your indexing strategy - work with the optimiser to get the best execution plan for your query, instead of overriding it
I agree with what @Kev Riley says about this. There are, in certain circumstances, rare circumstances, hints you can use to speed a query. But there's no magic hint and that's what people think NOLOCK is. It's not. It's dangerous.
If you run the TRUNCATE TABLE and the following INSERT in the same transaction, then the TRUNCATE TABLE is the issues. TRUCATE TABLE places SCH-M Lock on the table as you can reed on MSDN [TRUNCATE TABLE (Transact-SQL)][1] and the WITH(NOLOCK) table hint is not compatible with the SCH-M lock as you can find on MSDN [Table Hints (Transact-SQL)][2]. It states: > 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 [1]:
https://msdn.microsoft.com/en-us/library/ms177570.aspx [2]:
https://msdn.microsoft.com/en-us/library/ms187373.aspx
Yah, TABLOCK will put an exclusive lock, but eg. to achieve bulk insert in case of INSERT...SELECT into a heap, it is necessary to use it to achieve BULK insert, otherwise the insert will be fully logged and it will slow down the insert. But Of course the DB has to be in bulk or simple recovery mode.
Thank you Pavel, now I see the problem. Also reading about the locks you mentioned I realized that the tablock on an insert produces the same results as tablockX - i.e. An exclusive lock is taken on the table (I read it on msdn,
https://msdn.microsoft.com/es-uy/library/ms174335.aspx)
Yeah, stop putting lock hints all over your queries, especially NOLOCK. It's dangerous.
Yep, I know :) It is a legacy process that I'm trying to understand to improve it later. But I think that nothing is dangerous when you know why you use it (and the given situation justifies it of course)
17 People are following this question.
