recurrence of sqlalchemy.exc.InternalError: (InternalError) (1205, u'Lock wait timeout exceeded ...

[dewey]

Guys,

I keep seeing:

sqlalchemy.exc.InternalError: (InternalError) (1205, u'Lock wait timeout exceeded

in code where there should not be any contention for the records or indexes......I'm pretty experienced on the MySQL side and this even should be incredibly rare but it's happening with some frequency.... this system has very low concurrency and these are not exceptionally long transactions......less than 20 seconds

It sometimes occurs when the script is called manually via the command line, and sometimes occurs when the script is called by the Celery/RabbitMQ Beat scheduler.

I feel certain I must be using SA in some way that's causing this but I'm not sure how.....

All tips appreciated.....Partial stack trace below

Thx

Dewey

File "/opt/paysys/python/lib/python2.7/site-packages/pymysql/err.py", line 116, in raise_mysql_exception

    _check_mysql_exception(errinfo)

  File "/opt/paysys/python/lib/python2.7/site-packages/pymysql/err.py", line 112, in _check_mysql_exception

    raise InternalError(errno, errorvalue)

sqlalchemy.exc.InternalError: (InternalError) (1205, u'Lock wait timeout exceeded; try restarting transaction')

[dewey]

I forgot to add the environment details:  Python 2.7.8,  SA  0.9.8  MySQL MariaDB 10.0.17

[dewey]

I'm creating my session like this:

engine = create_engine(_db_con_str, echo=DB_ECHO, pool_recycle=3600, isolation_level="READ UNCOMMITTED") 

Session = sessionmaker(bind=engine)

Session(expire_on_commit=False)

Is any of that the likely culprit??

[Mike Bayer]

not at all.   your code has a deadlock, so this has to do with what tables are being accessed, and how, and when.  You need to isolate where your application attempts to run two activities, likely on two separate connections, concurrently.    Looking at things like the processlist view (https://dev.mysql.com/doc/refman/5.1/en/processlist-table.html) as well as SHOW INNODB STATUS can be helpful here.

A google immediately turns up three decent stackoverflow threads, the second one has some good details about inspecting transaction status:

http://stackoverflow.com/questions/19458960/mysql-error-code-1205-lock-wait-timeout-during-update-with-inner-join

# good answer about SHOW INNODB STATUS
http://stackoverflow.com/a/6005564/34549 


http://dba.stackexchange.com/questions/21464/error-code-1205-lock-wait-timeout-exceeded-in-mysql

--
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.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[dewey]

Thanks Mike!!!  Good stuff.

These errors are happening in production on a financial system that I wrote, but to which I do not have live access for security, liability and policy reasons.

So I can't inspect the internal state of the server myself (and the SA with clearance won't go out of his way), but from reading the links you sent, it seems the two most generic / widely-applicable fixes that are likely to help would be to:

Switch from READ UNCOMMITTED  to READ COMMITTED

and 

increase the setting for innodb_lock_wait_timeout to something greater than 50 (eg 300)

One of the threads also points out that if some code with DB changes (updates/deletes) falls off without ever committing, that MySQL will sleep that thread for up to the length of wait_timeout (default of 8 hours) BUT STILL leave all of those rows locked the entire time......I wonder if one of our scheduled stored procs might be doing this since I don't think the client is making that mistake.....

Thanks for pointing me in the right direction!!