Azure SQL concurrent inserts cause duplicates in incremented column

Question

We have an integration of PowerApps with Azure SQL. The inputs provided by user is stored in Azure SQL with incremental row ID. The request ID increment operation is handled in the stored procedure. But if multiple users are adding the content at same time then all requests are saved with the same Request ID. We can not force the Request ID column to be unique due to dependency.

Please suggest if we can avoid duplicates here. Here is the SP:

SELECT @ROWNUM = MAX(R.ROWNUM) + 1
         FROM REQUESTS R
         WHERE R.ABC = @XYZ

         IF @ROWNUM is NULL
         begin
             set @ROWNUM = 1
         end

         -- Inserting a single new request into request table with auto generated identify
            INSERT INTO REQUESTS
         (  
             Column1,
             …… 
             Columnn
         )

Thanks!

One approach is to execute the SELECT and INSERT in the same serializable transaction. Consider increased possibility of deadlocks. — Dan Guzman, Dec 17 '19 at 13:10
That's not an autoincremented column. This is *manually* incremented MAX+1 and prone to duplicates even if just two commands run concurrently on a *local* server. Use `IDENTITY` or a SEQUENCE instead. If you want to return the new ID use the `OUTPUT` clause in `INSERT` — Panagiotis Kanavos, Dec 17 '19 at 13:41
This is prone to duplicates even with *one* user - if you delete the latest rows, this query will generate the same IDs again. If those values were used in other tables, eg as lookup keys, they'll end up creating a fake relation between unrelated rows — Panagiotis Kanavos, Dec 17 '19 at 13:43
@PanagiotisKanavos the problem is Rownum cannot be changed to identity...the rownum is Request ID..I have something called Inspections (a parent, if you will) and each inspection can have multiple requests..Under each inspection, the requestID has to start at 1 and get incremented. Any suggestions? — karthik, Dec 18 '19 at 15:05
`the requestID has to start at 1 and get incremented.` which means it's not an ID, just a counter? Even if it's a business field, eg an invoice number, or Line item in an order, it's still a counter. You could get rid of that column and calculate the `ROW_NUMBER()` when querying, ordered by the key of the row. — Panagiotis Kanavos, Dec 18 '19 at 15:46

score 1 · Answer 1 · answered Dec 17 '19 at 13:34

In order to avoid such situations you could change your table column ROWNUM to identity

CREATE TABLE [dbo].[REQUESTS](
    [ROWNUM] [int] IDENTITY(1,1) NOT NULL,
    [Col1] [int] NOT NULL,
    [Col2] [varchar](50) NOT NULL,
    [Col3] [datetime] NOT NULL,
 CONSTRAINT [PK_REQUESTS] PRIMARY KEY CLUSTERED 
(
    [ROWNUM] ASC
)

Azure SQL concurrent inserts cause duplicates in incremented column

1 Answers1