CODEWITHSUNDEEP
pythonpostgresqlsqlalchemylocking
Andrew
Andrew

Reputation: 1998

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.

Upvotes: 15

Views: 17472

Answers (2)

Noa Iarchy
Noa Iarchy

Reputation: 57

with_lockmode("...") is now deprecated in sqlalchamy, see a reference here: https://kite.com/python/docs/sqlalchemy.orm.Query.with_lockmode

The solution now is to use:

session.query(database_version).with_for_update()

Upvotes: 4

Andrew
Andrew

Reputation: 1998

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()

Upvotes: 14

Related Questions