Last post Oct 28, 2016 05:33 PM by PatriceSc
Oct 28, 2016 11:37 AM|sudip_inn|LINK
i open a query windows in sql server and run the below script
UPDATE EMP2 WITH (HOLDLOCK,ROWLOCK)
SET Salary=100 WHERE ID IN (1,2)
WAITFOR DELAY '00:00:10';
PRINT 'Job complete'
when i run the above script and again the run the below script from another window then select not giving any records
from Emp2 WITH(NOLOCK)ORselect * from Emp2 WITH(READPAST)
tell me what wrong is there in my approach. thanks
Oct 28, 2016 05:33 PM|PatriceSc|LINK
It seems to work on my side. How many rows? I'm using SQL Server 2012.
I'm always testing with 1000+ rows and with a significant row size (using a varchar(n) column to use up some space). SQL Server might take some shortcuts if the table is really small.
Actually it was to do a test about your previous post. When not using any hint in the select I don't have to wait when not selecting locked ids.
Edit; yes I tried again with 3 rows rather than 1000 and it seems I do see then the same behavior. Try with 1000 rows on your side and see what happens. You can use GO 1000 to repeat the previous batch of commands 1000 times.