Question 90507 in PostgreSQL Question posted in
The official documentation can be found here.

How to Handle Concurrent API call for same HTTP Method – Postgresql

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?

Tags:

2

Answers


  1. 0

    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).

    One 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.

    @tymtam’s way is even easier, though both mine and their answer requires you to have the permissions to modify the DB schema.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search