CODEWITHSUNDEEP
pythonpostgresqlatomic
Dinero
Dinero

Reputation: 1160

Atomic Update for postgresql table

Background

I have a simple function that makes an update to my table. Any time i execute my script foo_script.py i call this function set_status to create a time stamp. Once i am done with the process i call the function remove_status to make the entry in the table NULL.

I also have a wrapper script called db.py that lets me execute sql statements hence in my code i am using db.execute() but that is not relevant to my problem here.

The idea is that foo_script.py can not run concurrently.

Code

def set_status() -> None:
    db.execute(
        ENVIRONMENT=db.get_environment(),
        sql="UPDATE my_table SET status_process = NOW()",
    )

def remove_status() -> None:
    db.execute(
        ENVIRONMENT=db.get_environment(),
        sql="UPDATE my_table SET status_process = NULL",
    )

ISSUE

Unfortunately the script foo_script.py can be accidentally run by 2 or more. I want to avoid any race conditions. How can i ensure the queries i make are atomic. I am new to sql and i am not sure how to achieve this.

Upvotes: 1

Views: 671

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246338

That sounds like a typical application for advisory locks.

If you use them, you don't need to lock an object in the database to synchronize operations. Also, advisory locks can be independent of transactions, so you don't have to have long database transactions with all their associated unpleasantness.

To grab the lock, run

SELECT pg_advisory_lock (42);

and to release it, run

SELECT pg_advisory_unlock(42);

42 is the lock number; you are of course free to choose a different one.

Upvotes: 2

Related Questions