Here's my entire script:
IF OBJECT_ID('tempdb..#Products_temp') IS NOT NULL
BEGIN
DROP TABLE #Products_temp
END
GO
SELECT p.product_id, p.price, p.cost, i.qty
INTO #Products_temp
FROM Warehouse_Products p WITH (NOLOCK)
LEFT OUTER JOIN Warehouse_Inventory i WITH (NOLOCK)
ON p.product_id = i.product_id
GO
DELETE FROM cache.Products
GO
INSERT INTO cache.Products SELECT * FROM #Products_tempFor some reason the SELECT .. INTO part was stuck in a RUNNABLE state for 3 hours when typically the entire script takes 6-7 minutes to finish. This was blocking all my other queries, which is exactly why I wrote this script in the first place. I wanted to prevent blocking any other table/script by using a temporary table, what am I doing wrong here? Any advice?
1条答案
按热度按时间xxhby3vn1#
NOLOCK is never applyed on SELECT queries involved into INSERTs, UPDATEs, DELETEs, MERGEs and SELECT ... INTOs.
Also NOLOCK won't be applyed when ENCRYPT / DECRYPT functions are used...
Imagine just what is NOLOCK... NOLOCK is not the fact that your query will not block other users, but will ignore locks that other users have sets... This conducts to read sometime twice or more the same row, or to avoid readings some rows... It is just unacceptable to guarantee data integrity when the database queries are writting data into table/indexes !