Andrew Andrew - 6 months ago 53
Python Question

Sqlalchemy explicit locking of Postgresql table

I'm trying to explicitly lock a postgres table using this sqlalchemy command:

db.engine.execute('BEGIN; LOCK TABLE database_version IN ACCESS EXCLUSIVE MODE;')

After this executes, if I go into the database client and run:

select * from pg_catalog.pg_locks;

There aren't any ACCESS EXCLUSIVE locks present.

If instead, I run the first command, but from inside the db client, it works as expected.

Is there a reason trying to get a table lock from sqlalchemy isn't working correctly?

Ideally, I want only one process to be able to query from and insert into the database_version table at a time.


So it turns out that I needed to start a nested transaction from the session object instead of trying to BEGIN one using straight SQL.

db.session.execute('LOCK TABLE database_version IN ACCESS EXCLUSIVE MODE;')

Then, i insert the new row:

new_version = DatabaseVersion(version=version + 1)

and then finally commit again to close out the nested transaction: