Is it possible to block a loop?
Last night, the test group opened a strange tick. The application has a grid in which the user can check incidents that need to be postponed; the tester selected two incidents (ids: 1 and 5), postponed both, but one did not receive an updated status
I know that the [Incident] table needs to be updated, and one new record is inserted into the [IncidentDetail] table. I asked both and loved 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’s complaint is valid because both values are [Incident]. [IncidentStatus] should be zero at this point. After digging, I took the exact request for the application 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 pending transactions. As far as I know, even if the first iteration of the loop blocked the second, an exception should be raised to the point where the update is happening and the entire transaction should be canceled.
It looks like the update might fail while the insert is working. All columns received standard types (Int, Varchar (100), DateTime, etc.), I also tested problems with implicit release.
This problem occurred only once and cannot be reproduced, even with the use of a backup, but I am afraid that this can happen again at the factory if I cannot find why it happened.