Sqlalchemy explicit locking of Postgresql table

Question

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.

Answer 1

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.begin_nested()
db.session.execute('LOCK TABLE database_version IN ACCESS EXCLUSIVE MODE;')

Then, i insert the new row:

new_version = DatabaseVersion(version=version + 1)
db.session.add(new_version)
db.session.commit()

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

db.session.commit()