CODEWITHSUNDEEP
pythonarchitecturesynchronizationflask-sqlalchemyblocking
GML-VS
GML-VS

Reputation: 1100

Python Flask-SQLAlchemy prevent from inserting records in parallel

I have a Python Flask-SQLAlchemy application generated from the OpenAPI v.3 YAML file running under Gunicorn and one application's endpoint URL check the existence of the records in a table for specified ID and if there are no records in it - inserts some results from the REST API call.

So I am wondering how to prevent the two parallel insertion into the DB table if someone calls the endpoint URL simultaneously when the table has no records for the specified ID.

What can I do?

  1. Lock the DB table - checking the records existence - insert records - release lock?

  2. Synchronize the Python code somehow to prevent two parallel calls?

Can the Flask endpoint function be called simultaneously by the two API external calls or Flask is a single threaded app? I read that from v.1.0 it's multi-threaded by default - I have v. 1.1.2.

Current I am working via generated DB Models and

       db = SQLAlchemy()
       db.session.query(...)
       db.session.add(entry)
       db.session.commit()

Upvotes: 1

Views: 1668

Answers (1)

Daniser
Daniser

Reputation: 162

If you are using SQLite with SQLAlchemy, you may have issues with multi-threading/concurrency. Databases like MySQL support parallel insertions (you will not have to lock the table).

Flask can work in single threaded or multi-threaded mode and serve several connections from different users. Flask's ability to serve many requests in parallel (10+) is limited, this is why you need to use Gunicorn (please correct me if I am wrong).

If you do not want to use databases like MySQL (or have another reason to prevent concurrent insertions), you can try to "serialize" the problem.

For example: you can have a shared Queue between Flask's threads (you need a thread-safe Queue), each request will insert all the relevant information into the Queue.

A background process will "consume" the requests from the Queue - one at a time. There are many possible methods to implement a "background process" as part of your flask app.

You can use Redis as your queue, here's an example that might help:

https://realpython.com/flask-by-example-implementing-a-redis-task-queue/

Edit:

After you mentioned that you using PostgreSql,

I think you should have a look at row/table locking:

https://www.postgresql.org/docs/9.4/explicit-locking.html

Allow the database to take care of the parallel insertion issues.

Upvotes: 1

Related Questions