Strange anomaly on a query result

Transact-SQL
1

Hi guys.
I have a strange situation that cannot understand.

Let's start with the table.

CREATE TABLE [dbo].[RxEvent](
[IdEvent] [bigint] IDENTITY(1,1) NOT NULL,
[IdTerm] [bigint] NOT NULL,
[Args] varchar NOT NULL,
[Working] [bit] NOT NULL,
[Error] [int] NOT NULL,
CONSTRAINT [PK_RxEvent] PRIMARY KEY CLUSTERED
(
[IdEvent] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Ok.
In this table there are one or more windows applications that insert records like these.
INSERT INTO RXEVENT VALUES
(3, '5,3,4,0,0,2017,7,5,18,11,58,0', 0,-1),
(4, '30,2,4,0,0,2017,7,5,20,31,55,0', 0,-1),
(5, '5,3,4,0,0,2017,7,29,22,29,29,0', 0,-1)
and go on.
Every appliaction insert always Working = 0 and Error = -1.

Then there is second application that execute the analysis of these records.

the working flow is:
select the first record,
analyzes the record,
update record.

The last application erase the records already analized.

To get the first record and set it in working I use this query:

IF OBJECT_ID('TEMPDB..#IDEVENTTABLE') IS NOT NULL DROP TABLE #IDEVENTTABLE
CREATE TABLE #IDEVENTTABLE (IDEVENT BIGINT NOT NULL)
INSERT INTO #IDEVENTTABLE
SELECT TOP 1 IDEVENT FROM RXEVENT WHERE WORKING = 0 AND ERROR < 0
UPDATE RXEVENT
SET WORKING = 1
FROM RXEVENT
INNER JOIN #IDEVENTTABLE ON RXEVENT.IDEVENT = #IDEVENTTABLE.IDEVENT
SELECT RXEVENT.IDEVENT, RXEVENT.IDTERM, ARGS, IDTERMTYPE FROM RXEVENT
INNER JOIN TERM ON RXEVENT.IDTERM = TERM.IDTERM
INNER JOIN #IDEVENTTABLE ON RXEVENT.IDEVENT = #IDEVENTTABLE.IDEVENT
WHERE WORKING = 1
DROP TABLE #IDEVENTTABLE

Now the record is analized.
after analysis I use this query to update the record as already analyzed:
DECLARE @IDEVENT INT, @EXECUTED INT
SET @IDEVENT = 11111
SET @EXECUTED= 1
IF (@EXECUTED > 0)
BEGIN
UPDATE RXEVENT
SET WORKING = 0, ERROR
= 0
WHERE IDEVENT = @IDEVENT
END

where obviously
@IDEVENT is the idevent like result of the first query.

If I execute this work flow from SSMS, all is ok.
When instead this work flow is executed from application,
in random mode, the result of the first query is the same of the previus.

the questions are
1 how can that happen?
2 how do I solve it?

Best regards

GianPiero

2

When you SELECT the row first, there is time between the SELECT and the UPDATE for someone else to SELECT that same row.
You need to UPDATE the row immediately, then capture the id of the row that was UPDATEd.

For example:
IF OBJECT_ID('TEMPDB..#IDEVENTTABLE') IS NOT NULL DROP TABLE #IDEVENTTABLE
CREATE TABLE #IDEVENTTABLE (IDEVENT BIGINT NOT NULL)

UPDATE TOP (1) RXEVENT
SET WORKING = 1
OUTPUT INSERTED.IDEVENT INTO #IDEVENTTABLE ( IDEVENT )
WHERE WORKING = 0 AND ERROR < 0

/*do analysis here*/
...
1 Like
3

thanks
I have tryed and seem like it works correctly