Lock and unlock from different Postgres sessions

Question

I have a Postgres (9.5) DB for an application composed of web and background worker processes.

If we receive HTTP request POST /items/123/steps then the web app will queue a background task to perform a "workflow step" on item 123 and return. The item corresponds to a row in the Postgres items table and has a column with the id value 123.

The goal is to somehow lock item 123 for processing so that subsequent requests to POST /items/123/steps will be rejected until the background task unlocks item 123 after completing its work.

Similarly requests to GET /items/123 must return an attribute indicating that item 123 is locked so that the UI can display the item as currently processing, again until the background task unlocks it.

Because there may be a delay until the background task is picked up from its queue, it is not enough for the background task to lock item 123 when it starts executing. I want item 123 to be marked as locked from the initial POST web request until the background task completes, so that the item shows as locked for any web requests that come in before the background task is picked up.

If possible I'd like to use Postgres to accomplish this. However the options I've found don't seem to work:

• Row locks are released when the transaction ends, which occurs when my web handler returns. The background task performs its work in different transaction. So I can't do a SELECT FOR UPDATE on item 123.
• A session-level advisory lock gets closer except that if I obtain it from the web process I can't unlock it from the worker process, since the web and worker processes each establish their own Postgres session.

I can work around this by implementing an ad-hoc locking mechanism: add a locked column to items and set it to TRUE in the web endpoint and then back to FALSE in the background task. If a more standard way to do it exists though I'd rather do that.

Additional info: it's a Python project and I'm using SQLAlchemy to access Postgres from both the (Flask) web app and (Celery) background tasks. If there's another proven way to accomplish the goal with these tools that works for me.

Answer 1

Your “workaround” of using a locked column is actually the correct solution.

As you correctly observe, neither regular locks nor advisory work well in your dituation.