You are right, MySQL itself is doing something odd. However I feel like SQLAlchemy must have done some funny locking that is causing the database to act strangely, and I'd like to find out how and how to avoid that.
Here's the output from the query with echo=True: SELECT object.id AS object_id, object.name AS object_name FROM object FOR UPDATE Any ideas what could be happening? On Sunday, February 16, 2014 8:19:54 PM UTC-5, Michael Bayer wrote: > > > On Feb 16, 2014, at 7:50 PM, Mike Lessings > <twotimes...@gmail.com<javascript:>> > wrote: > > I'm trying to lock rows when I select because I intend to update them > shortly thereafter. I don't want another session in another process to > simultaneously be reading/updating. In code: > > # ... all necessary setup and Session creation > session = Session() > > with session.no_autoflush: > > session.begin_nested() > objects_protected_from_concurrent_access = > session.query(Object).with_for_update().all() > # ... edit some of the objects here > objects_protected_from_concurrent_access[0].field_one += 1 > session.commit() > > But for some reason on the `with_for_update()`, the connection hangs until > a timeout error (1205 Operational Error) occurs. There's no other processes > talking to the database. Checking MySQL's > > SHOW PROCESSLIST; > > Reveals nothing unexpected (no processes except Sequel Pro before, then1 > process from the box I'm running SQLAlchemy on, and 1 making the SHOW > PROCESSLIST query from Sequel Pro afterwards). > > How can I best do what I'm trying to do? And is what I'm seeing a bug or a > feature? > > > its nothing on SQLAlchemy’s side; turn on echo=True on your engine and > watch the SQL going by. You’ll see the “SELECT.. FOR UPDATE” happening, > and that’s all SQLA is doing, sending a string to the connection. Try to > break out the SQL strings into a test script against your DBAPI directly. > > > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
