Update concurrency

[Sylvain Déve <sylvain.deve@xxxxxxxxxxxxxx>]

Hello,

On a server, I have an API which is called by one or multiple clients. The server can receive, almost at the same time, multiple update instructions for the same PostgreSQL attribute. These requests do not arrive at the exact same time, and I would just like to execute them one after another. Because of the (Flask) API, I cannot control that a previous call is over, so the new update can be called before the previous one is completed. I use psycopg2 to interface with PostgreSQL, and I would except psycopg2/PostgreSQL to be able to queue the updates and proceed them quietly one after another (maybe not even in order), but it does not seem to work that way.

Below is a simplified version of the function called on the server when an update is required. The attribute to be updated is a JSONB object, if that matters. Hence there is 'path' to determine which part of the JSONB object to update. I copy the Python code, not only the PostgreSQL code, because it might be part of the problem. So the API call looks like this:

def pg_update(data, path):
    conn = psycopg2.connect(...) # always the same database.
    cur = conn.cursor()

    # JSONB update for a single selected row and column.
    # 'column' and 'select_row' are determined with the parameters 'data' and 'path'.
    command = (
        f"""UPDATE MY_TABLE SET """
        f"""{column} = jsonb_merge({column}, %s) """
        f"""WHERE {select_row};"""
    )

    cur.execute(command, [Json(data)])
    conn.commit()

When called twice in a row, this call leads to the error (at "cur.execute"):

    psycopg2.errors.InternalError_: tuple concurrently updated

Since the server keeps running all the time, I can also define conn outside the API call "pg_update" so that all calls are processed with the same psycopg2 connection. But then, when two updates are requested for the same attribute, I get instead:

    psycopg2.ProgrammingError: execute cannot be used while an asynchronous query is underway

I forced async_ = False in psycopg2.connect, just in case. No change.

I also tried to lock like that:
    command = (
        f"""BEGIN; """
        f"""SELECT * FROM MY_TABLE WHERE {select_row} FOR UPDATE; """
        f"""UPDATE MY_TABLE SET """
        f"""{column} = jsonb_merge({column}, %s) """
        f"""WHERE {select_row};"""
        f"""END;"""
    )

To summarize, how can I allow for multiple updates of the same attributes, one after another, even when these updates are requested almost at the same time from independent, uncontrollable client requests? Maybe there is a need for a better locking mechanism? A queuing mechanism?

Thanks for your help!