52

I'm looking for a complete example of using select for update in SQLAlchemy, but haven't found one googling. I need to lock a single row and update a column, the following code doesn't work (blocks forever):

s = table.select(table.c.user=="test",for_update=True)
# Do update or not depending on the row
u = table.update().where(table.c.user=="test")         
u.execute(email="foo")

Do I need a commit? How do I do that? As far as I know you need to: begin transaction select ... for update update commit

Improve this question
1

3 Answers 3

Reset to default
57

If you are using the ORM, try the with_for_update function:

foo = session.query(Foo).filter(Foo.id==1234).with_for_update().one()
# this row is now locked

foo.name = 'bar'
session.add(foo)

session.commit()
# this row is now unlocked
Improve this answer
2
  • 1
    @MatthewMoisen could you tell me why use add ? if I don't use it, will it be OK?
    – fishshrimp鱼虾爆栈
    Commented Dec 24, 2017 at 13:11
  • 2
    @小文件 You do not need to use add. The row becomes locked after executing with_for_update().one(), and unlocked either on session.commit() or session.rollback().
    – Matthew Moisen
    Commented Oct 27, 2019 at 0:04
19

Late answer, but maybe someone will find it useful.

First, you don't need to commit (at least not in-between queries, which I'm assuming you are asking about). Your second query hangs indefinitely, because you are effectively creating two concurrent connections to the database. First one is obtaining lock on selected records, then second one tries to modify locked records. So it can't work properly. (By the way in the example given you are not calling first query at all, so I'm assuming in your real tests you did something like s.execute() somewhere). So to the point—working implementation should look more like:

s = conn.execute(table.select(table.c.user=="test", for_update=True))
u = conn.execute(table.update().where(table.c.user=="test"), {"email": "foo"})
conn.commit()

Of course in such simple case there's no reason to do any locking but I guess it is example only and you were planning to add some additional logic between those two calls.

Improve this answer
2

Yes, you do need to commit, which you can execute on the Engine or create a Transaction explicitely. Also the modifiers are specified in the values(...) method, and not execute:

>>> conn.execute(users.update().
...              where(table.c.user=="test").
...              values(email="foo")
...              ) 
>>> my_engine.commit()
Improve this answer
4
  • 10
    This answer misses the main point of the question given, which is SELECT ... FOR UPDATE usage example. Code from it can be reduced to the proposed form, but then it doesn't use requested construct anymore. Of course if @Mark didn't plan to add any additional logic between obtaining lock and updating records, such reduction is perfectly fine to do.
    – RobertT
    Commented Aug 9, 2013 at 10:57
  • @RobertT: i see the answer clearly missed to answer the main point of the question. Shall I remove the answer (given it is not a good one) or keep it (and keep getting some downvotes)? ;)
    – van
    Commented Mar 8, 2022 at 7:11
  • Personally I would problably remove it, but of course it's up to you ;). In fact though your proposed syntax with .values() is of course valid, passing values to update as keywords for .execute() also worked and still works in current SQLAlchemy version (just checked). So the only real value of this answer is pointing to use session and call .commit() which for most current SQLAlchemy users is obvious as syntax used in question is as far as I know considered obsolete for a very long time. And that's probably source of downvotes, as most people don't remember 0.9 or earlier days :)
    – RobertT
    Commented Mar 9, 2022 at 2:12
  • Thanks, @RobertT. I will keep it. It is nice to be reminded that we all make mistakes even if those are not life changing...
    – van
    Commented Mar 10, 2022 at 13:40