Can a loop lock itself?

Question

Can a loop lock itself?

Last night the test team opened a strange ticked. The app have a grid where the user can check incidents to be postponed the tester selected two incidents (ids: 1 and 5), postponed both but one don't got the status updated

I know the [Incident] table must be updated and one new record inserted at the [IncidentDetail] table I queried both and fond this:

Id IncidentKindCode TransportEntryId CreateDate             IncidentStatus CloseDate
-- ---------------- ---------------- ---------------------- -------------- ---------
1  11301            52               2014-08-26 19:23:21.47 1              NULL
5  11301            56               2014-08-26 20:06:17.94 0              NULL

Id IncidentId  InsertDate             DetailKind Reason    IncidentUser PostponeDate
-- ----------- ---------------------- ---------- --------- ------------ -----------------------
9  1           2014-08-26 20:28:37.37 1          TEST TEST 8            2014-08-27 00:00:00.000
10 5           2014-08-26 20:28:37.37 1          TEST TEST 8            2014-08-27 00:00:00.000

The tester complain is valid since both [Incident].[IncidentStatus] values must be zero at this point. After digging in it I captured the exact query the app sent to the server (thanks to the profiler).

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@IDINCIDENT varchar(max) ,@REASON varchar(max) ,@USERCODE varchar(max) ,@POSTPONEDATE varchar(max) ',N'
DECLARE @ARRAY VARCHAR(8000), @DELIMITADOR VARCHAR(100), @SELECTEDID VARCHAR(8000);

SELECT @ARRAY = @IDINCIDENT
SELECT @DELIMITADOR = '';''

IF LEN(@ARRAY) > 0 SET @ARRAY = @ARRAY + @DELIMITADOR

WHILE LEN(@ARRAY) > 0
BEGIN

    SELECT @SELECTEDID = LTRIM(SUBSTRING(@ARRAY, 1,
    CHARINDEX(@DELIMITADOR, @ARRAY) - 1))

    BEGIN
        UPDATE [dbo].Incident SET INCIDENTSTATUS = 1 WHERE ID = @SELECTEDID
        INSERT [dbo].IncidentDetail (INCIDENTID, DETAILKIND, REASON, INCIDENTUSER, POSTPONEDATE)
                       VALUES (@SELECTEDID, 1, @REASON, @USERCODE, @POSTPONEDATE);
    END

    SELECT @ARRAY = SUBSTRING(@ARRAY,
    CHARINDEX(@DELIMITADOR, @ARRAY) + 1, LEN(@ARRAY))
END
',@IDINCIDENT='1;5',@REASON='querty',@USERCODE='1',@POSTPONEDATE='2014-08-28 00:00:00'
select @p1

No triggers, no other update no pending transactions. As far I know, even if the first loop iteration blocked the second one to the the point the update fails, a exception must be raised and all the transaction be rolled back.

Seems ilogical the update can fail while the insert works. All columns got standart types (Int, Varchar(100), DateTime, etc) also I tested for implicit casts issues.

This issue happened only once and cannot be reproduced, even using a backup but I'm afraid it can happen again at production if I cannot find why it happened.

Answer 1

I am not understood what you actually want to do. I am giving you a way based on what i understood. You can check whether that entry gets updated or not, if updated then you insert in IncidentDetail.

UPDATE [dbo].Incident SET INCIDENTSTATUS = 1 WHERE ID = @SELECTEDID

If Exists(  Select  1
            From    [dbo].Incident As I
            Where   I.ID = @SELECTEDID
                    And I.INCIDENTSTATUS = 1
         )
Begin
    INSERT [dbo].IncidentDetail (INCIDENTID, DETAILKIND, REASON, INCIDENTUSER, POSTPONEDATE)
                       VALUES (@SELECTEDID, 1, @REASON, @USERCODE, @POSTPONEDATE);
End