Ignore Lock in MYSQL Database in Sqlalchemy Query

Question

Using SQLAlchemy to query a MySQL database I am getting 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 error message comment to "consider using a session.no_autoflush block if this flush is occurring prematurely" is about the other session placing the lock, not the session I'm using for my current query? If I followed this advice would this help avoid locks on the database in general? Second, I only need to read and do not need to write modifications 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 the sql is NOWAIT, but I don't see how to do that in the sqlalchemy API.

Answer 1

Assuming you are using the mysql.connector, the default value of the autocommit Property is False, which might cause your script to hang due to other session that is waiting to finish.

SQLAlchemy is using the BEGIN statements, (alias of START TRANSACTION) which cause the session to acquire LOCK of the table/database, and your connection will wait until the lock will get approved.

To overcome this behavior (and due to the fact that you said you only need to READ data during the session) you can set autocommit=True when creating your Session:

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

Another option - after you create the session you can execute SET AUTOCOMMIT=1:

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

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

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

However I didn't test it. According to the documentations it should work.