8

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.

RazerM
  • 5,128
  • 2
  • 25
  • 34
Michael
  • 13,244
  • 23
  • 67
  • 115
  • Please provide the SQL statements involved (on both connections), and `SHOW CREATE TABLE`, and `SHOW ENGINE=InnoDB STATUS;` (if possible, when the problem happens). From what you have provided, we can't tell why it is locking, nor many other important details. – Rick James Jul 18 '16 at 17:04
  • Also provide information on sqlalchemy's "no_autoflush"; we need to figure out what MySQL construct is under the covers. – Rick James Jul 18 '16 at 17:05
  • @RickJames no SQL constructs underly [autoflush](http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.autoflush). It is just how SQLAlchemy session handles state. If the session has pending changes not yet flushed to DB (inserts, updates, deletes), it'll flush those to the DB before the next query is run, if autoflush is in use. Can't help OP without a mcve though, something is locking something and a flush issued before some query is using something locked... – Ilja Everilä Jul 19 '16 at 13:17
  • `NOWAIT` would be done using [`Query.with_for_update`](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_for_update), but MySQL [does not support `NOWAIT`](https://dev.mysql.com/worklog/task/?id=3597). Also I don't think it is the select itself that causes your issue, but some modified instance state or issued inserts, updates or deletes that get autoflushed before the query. – Ilja Everilä Jul 19 '16 at 13:28
  • 1
    Smells like SqlAlchemy is _getting in the way_. – Rick James Jul 19 '16 at 19:14
  • 1
    To me it smells like OP is not even sure what they're doing: "First, I assume that ... is about the other session placing the lock, not the session I'm using for my current query?" What other session? As stated before: providing a [minimal, complete and verifiable example](http://stackoverflow.com/help/mcve) would help helping. – Ilja Everilä Jul 19 '16 at 21:08
  • 1
    Or perhaps get a proper DBMS such as PostgreSQL Anyway http://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im could be of help – Antti Haapala -- Слава Україні Jul 20 '16 at 05:55

1 Answers1

2

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.

Dekel
  • 60,707
  • 10
  • 101
  • 129
  • 2
    One of the first things mentioned [in the sqla documentation](http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#autocommit-mode) is: "**Warning** “autocommit” mode should **not be considered for general use**." – Ilja Everilä Jul 19 '16 at 21:53
  • 1
    @IljaEverilä, autocommit=True is really NOT a good practice, this is why it is mentioned this way in the documentation, HOWEVER, if there is no need for a transactional code (read-only script, for example) it will be much cheaper for the server to have that transaction with autocommit=True. The fact that SQLAlchemy have autocommit=False for every session is not always such a good practice. Moreover - If you are using the MyISAM engine it doesn't make sense to have transaction and there its even better to have autocommit=True. – Dekel Jul 22 '16 at 20:24
  • One could say it doesn't make sense to use MyISAM. Handling a transaction *can* be beneficial for read-only sessions - think repeatable read. It all depends. All in all autocommit will not even help here, if and when the locking transaction is in the other session, as autocommit is just a short lived transaction. Though OP is saying that they only read, it would seem the flush before the query is issuing writes. With autocommit the flush still also occurs in a transaction. Perhaps they've modified some instance without thinking about it, but we cannot know. – Ilja Everilä Jul 22 '16 at 20:52