On Tue, Oct 30, 2018 at 12:59 PM T Johnson <[email protected]> wrote: > > Hi, I am writing some unit tests, and I am getting blocked waiting for a > metadata lock (in MySQL) due to an open connection. > > The code is roughly: > > metadata.drop_all(engine) > metadata.create_all(engine) > conn = engine.connect() > sql = sa.text('select * from table') > rows = conn.execute(sql).fetchall() > print(conn.closed) # False > print(conn.in_transaction()) # False > # conn.close() # This seems to be required! > metadata.drop_all(engine) # is blocked > metadata.create_all(engine) > > > A single table is sufficient. So basically: drop, create, select, drop, > create. The hang happens at the second "drop". > > This seems to be related to autocommit > (https://docs.sqlalchemy.org/en/latest/core/connections.html#understanding-autocommit) > and if I'm understanding it correctly, the issue is that there must still be > a transaction open. Had I done DML instead of a SELECT, SQLAlchemy would have > autocommitted for me and there would have been no block. Had I executed with > `execution_options(autocommit=True)`, then there would have been no block. > Had I used a transaction via `with conn.begin()`, then similarly, there would > have been no block. Had I also executed: `conn.execute(sa.text('COMMIT'))` > then the transaction would have been closed and there would have been no > block.
with the exception of the last one that is potentially going to interfere with the driver, those are all correct. If you are trying to keep the same connection checked out from the connection pool between tests, then it seems that connection is a "global" so you would use that for your create_all()/ drop_all() as well. otherwise, if you are using the "engine", that's a connection pool checkout and I don't see why you'd need to keep the connection you used during your tests checked out. > > Assuming the above is correct, why is it that `conn.in_transaction()` does > not indicate that a transaction is in progress? > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
