Ignore lock in MYSQL database in Sqlalchemy query

Question

Using SQLAlchemy to query a MySQL database, I get the following error:

sqlalchemy.exc.OperationalError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (_mysql_exceptions.OperationalError) (1205, 'Lock wait timeout exceeded; try restarting transaction')

First, I assume that the comment on the error message “think about using the session.no_autoflush block if this flash happens prematurely” is that another session puts a lock, and not the session that I use for the current request? If I followed this advice, it will help to avoid locks in a database as a whole? Secondly, I only need to read and do not need to write changes to the query results, so I would like to know how to ignore the lock and just read what is currently in the database. I believe sql is NOWAIT, but I don't see how to do it in the sqlalchemy API.

Answer 1

Assuming you are using mysql.connector , the default value for the autocommit property autocommit False, which could cause your script to freeze due to another session that is waiting for completion.

SQLAlchemy uses a BEGIN statement ( START TRANSACTION alias) that forces the session to receive LOCK tables / database, and your connection will wait until the lock is approved.

To overcome this behavior (and because you said you only need to READ the data during the session), you can set autocommit = True when creating the session:

 Session = sessionmaker(bind=engine, autocommit=True) 

Another option - after creating the session, you can do SET AUTOCOMMIT=1 :

 s = Session() s.execute("SET AUTOCOMMIT=0") 

You can also try setting the autocommit property directly in the connection string:

 engine = create_engine("mysql+mysqlconnector://user:pass@localhost/dbname?autocommit=1") 

However, I have not tested it. According to the docs, it should work.