sqlalchemy FOR UPDATE NOWAIT

Question

The documentation for sqlalchemy says that you can specify "FOR UPDATE NOWAIT" in PostgreSQL 8.1 upwards using Query.with_lockmode('update_nowait'). Does anyone know how to get it to add the FOR UPDATE NOWAIT instead of just FOR UPDATE?

PostgreSQL 9.1.6

query = db.session.query(MyTable)\
    .filter_by(process_status="PENDING")\
    .order_by(MyTable.id)\
    .with_lockmode('update_nowait')\

print query

sql:

SELECT 
MyTable.id AS MyTable_id
,MyTable.created_on AS MyTable_created_on
FROM MyTable 
WHERE MyTable.process_status = :process_status_1 
ORDER BY MyTable.id 
FOR UPDATE

Please try to execute this query with engine echo=True and check stdout for actual query and post it here. — vvladymyrov, Jan 23 '13 at 03:32
The engine dialect is "postgresql". Executing the query with engine echo=True showed FOR UPDATE NOWAIT. It is good to know that the string representation of the query object isn't what actually gets executed. That will save me a lot of confusion. I hope that gets fixed someday. — tponthieux, Jan 23 '13 at 18:31

score 2 · Accepted Answer · edited May 23 '17 at 12:27

I was able to reproduce similar case with Oracle 11g and SQLAlchemy 0.8.0b2.

db = create_engine('...',echo=True)
...
q = session.query(CauseCode.__table__).with_lockmode('update_nowait')
print q
>>>SELECT ... FROM cause_code FOR UPDATE
print renderquery(q)
>>>SELECT ... FROM cause_code FOR UPDATE NOWAIT
q.all()
>>>2013-01-23 09:58:12,665 INFO sqlalchemy.engine.base.Engine SELECT ... FROM cause_code FOR UPDATE NOWAIT

Usually default query renderer used by str() produce query different from the actual query executed to DB. I suspect that sqlalchemy behave in the same way for Postgres in your case - print q produce dialect agnostic query.

PS renderquery() method implementation can be found here

score 2 · Answer 2 · answered Sep 13 '18 at 00:37

2

In newer versions of sqlalchmey, you can use

session.query().with_for_update(of=[fields to update], nowait=True)

answered Sep 13 '18 at 00:37

Charles L.

5,795
10
40
60

sqlalchemy FOR UPDATE NOWAIT

2 Answers2