I’m encountering a problem with a Python function designed to update rows in a PostgreSQL database in a highly concurrent environment. The function aims to lock the oldest unassigned row in the realms table using FOR UPDATE SKIP LOCKED, update it, and commit the transaction. However, I’ve noticed that when running about 50 requests concurrently, the number of rows updated in the database is less than the total number of function executions. I have verified that the function is executed successfully every time without any issues.

I’m using psycopg2 to interact with the Postgres Database

def allocate_realm(self):
    try:
        with self.db_conn.cursor() as cursor:
            cursor.execute("""
                SELECT * FROM realms
                WHERE is_assigned IS NULL
                LIMIT 1
                FOR UPDATE SKIP LOCKED;
            """)
            realm = cursor.fetchone()

            if realm:
                cursor.execute("UPDATE realms SET is_assigned = 'some_value' WHERE id = %s;", (realm[0],))
                self.db_conn.commit()
            else:
                # Handle the case when no unassigned realm is found
                pass
    except Exception:
        self.db_conn.rollback()
        # Error handling

Concurrency: Around 50 concurrent requests

What could be causing the discrepancy in the number of rows updated versus the total number of function executions in this concurrent setup? Are there any improvements needed in the way I’m handling transactions or locks to ensure an accurate reflection of updates in the database?

Meanwhile, I checked even for 5 parallel requests which failed similarly.