How to Handle Concurrent API call for same HTTP Method-CodePudding

In my application, I have InsertData HTTP method, where i am inserting multiple data in DB (postrgres).While inserting data, I am taking the MAX count from one of the column "ColIndex" and increment the "ColIndex" by 1. Increment operation will be happen based on the data count we received.

Note: Column "ColIndex" is not a primary key.

My problem is, When two users make a call at the same time to insert a different data. Data has been inserted successfully. But problem is User1 and User2 are getting same MAX count from column "ColIndex". How to wait till User1 response and proceed with User2 request?

Environment: .NET 6 & Postrgres

How to handle this Concurrent API call scenario without affecting performance?

CodePudding user response：

You have to have some form of concurrency, which could be either pessimistic (taking a lock of some kind) or optimistic (lockless, but needs some way to resolve conflicts).

The easiest way to do this, and to not take a lock would be to put a unique constraint on the COLINDEX column. Something like

CREATE TABLE YourTableName(..., ColIndex Integer, UNIQUE (ColIndex));

In your API code, implement a retry loop. Something like

const int MaxTries = 5;
for (int i=1; ; i  )
{
    try
    {
        // attempt your DB call, which includes inserting
        // what might or might not be the correct Max(ColIndex)
    }
    catch (PostgresException e)
        when (e.Code == PostgressErrorCodes.UniqueViolation)
    {
        if (i == MaxTries) throw;
        await Task.Delay(i*100);
    }
}

How long you delay between tries is a whole other topic, so I suggest for now delaying for a random number of milliseconds.

CodePudding user response：

You could make ColIndex an auto-increment column:

CREATE SEQUENCE a_colIndex_seq;

CREATE TABLE A (
    name VARCHAR PRIMARY KEY,
    colIndex integer NOT NULL DEFAULT nextval('a_colIndex_seq')
);

ALTER SEQUENCE a_colIndex_seq OWNED BY A.colIndex;

Then:

INSERT INTO A(Name) VALUES ('Ashok');
INSERT INTO A(Name) VALUES ('Kohsa');

SELECT * FROM A

Result:

name    colindex
Ashok   1
Kohsa   2