USING WHILE LOOP and setting ISOLATION LEVEL TO SERIALIZABLE

[sanjdhiman]

Hi I am running a simple loop statement that deletes data from a table

however it needs to be ran in a loop.

I need to let it run in a high level of isolation level SERIALIZABLE

WHILE @t <= @Max
BEGIN

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
DELETE FROM TABLE a
DELETE FROM TABLE y

IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
SELECT @Counter = @Counter + 1
END

That is pretty much the code , yet if I take the DELETE STATEMENTS out of the loop it works fine, but once in the loop it hangs and their is no I/O movement when doing a sp_who2

Can anyone help

thanks?

 

[gmmastros]

You have this condition for the loop:

WHILE [!]@t[/!] <= @Max

But, you are not incrementing @t anywhere. You do have this...

SELECT @Counter = @Counter + 1

Perhaps you should change it to...

SELECT [!]@t[/!] = [!]@t[/!] + 1


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sanjdhiman]

sorry yes it should be @t I was para-phrasing the code.

So still got the same problem, hanging!!!

 

[SQLSister]

also your delete statment has no where clasue so you would delte the whole table inthe first round of the loop, so no need for the loop.

Questions about posting. See faq183-874