question

amd.repetto avatar image
amd.repetto 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!
query hintsnolock
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Then what hint should I put on my select query to speed it up?
0 Likes 0 ·
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
0 Likes 0 ·
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.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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)
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Yeah, stop putting lock hints all over your queries, especially NOLOCK. It's dangerous.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.