My question is why some kind of SQL (running on SQL Server 2005) behaves as it is. In particular, I made changes to reduce the lock conflict during the update, and it seems to work in cases where I did not think it would be.
Source:
We had an update statement that applied to a table with more than 3,000,000 records:
UPDATE USER WITH (ROWLOCK)
SET Foo = 'N', Bar = getDate()
WHERE ISNULL(email, '') = ''
AND Foo = 'Y'
As you probably guessed, this caused the USER table to be delayed for a while. Even with the ROWLOCK prompt, other jobs that perform queries and updates against USER are blocked until this is completed. This is not acceptable for this particular application, so I thought I would apply the trick I was reading about if the update instruction updates only 100 records at a time. This would give other requests the opportunity to periodically appear at the table.
Improved code:
DECLARE @LOOPAGAIN AS BIT;
SET @LOOPAGAIN = 1;
WHILE @LOOPAGAIN = 1
BEGIN
UPDATE TOP (100) USER WITH (ROWLOCK)
SET Foo = 'N', Bar = getDate()
WHERE ISNULL(email, '') = ''
AND Foo = 'Y'
IF @@ROWCOUNT > 0
SET @LOOPAGAIN = 1
ELSE
SET @LOOPAGAIN = 0
END
It did the trick. Our update did its job, and other requests were received at the table. All this is happiness and light.
Secret:
, , . 100 , . , , !
, , (, 30 ), , . "TOP (100)", , , !
. - :