Sqlalchemy explicit locking of Postgresql table
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()