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!
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)] and the WITH(NOLOCK) table hint is not compatible with the SCH-M lock as you can find on MSDN [Table Hints (Transact-SQL)]. 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 :