Hi, of course I searched the web before submitting the question. None of them pointed to what is the real problem.
This can be easily reproduced with sqlalchemy: *console 1:* mysql> create table if not exists Foo (i int) engine MyISAM; Query OK, 0 rows affected (0,00 sec) mysql> *console 2:* >>> foo = engine.execute('select * from Foo;') >>> *now go back to console 1:* mysql> create table if not exists Foo (i int); *Blocked! * *now go to console 2:* >>> del(foo) >>> *console 1: has u**nblocked!* Query OK, 0 rows affected, 1 warnings mysql> Pavel Dne Ätvrtek, 29. srpna 2013 18:23:12 UTC+2 Simon King napsal(a): > > On Thu, Aug 29, 2013 at 5:20 PM, Simon King > <si...@simonking.org.uk<javascript:>> > wrote: > > On Thu, Aug 29, 2013 at 4:48 PM, diverman <pa...@schon.cz <javascript:>> > wrote: > >> Hi, > >> > >> we observed deadlock-like problem on our multi-component system with > mysql > >> database. > >> > >> Our setup: > >> > >> 1) MySQL server 5.5 with many MyISAM tables Foo_<timestamp>, one per > day > >> (like partitioning) > >> > >> 2) C++ backend daemon > >> * issuing CREATE TABLE IF NOT EXISTS Foo_<timestamp> (...) once per > day > >> after midnight > >> * filling those tables with INSERT INTO Foo_<timestamp> during > intraday > >> > >> 3) pythonic multi-threaded backend daemon (we call it 'worker') > >> * reads Foo_<timestamp> tables every minute via ScopedSession and > mapped > >> classes 'Foo' > >> * fills memcache with statistics computed from Foo_* tables > >> > >> 4) pythonic/apache/mod_wsgi single-threaded, multi-process webserver > >> * runs also on shared codebase 'foobar', so it uses also > ScopedSession > >> (same as 'worker') > >> * codebase is same when runing in webserver-mode and when running in > >> worker-mode (ScopedSession is always used). > >> > >> We observed that during rollover to next day the C++ BE daemon is > blocked on > >> 'Waiting for table metadata lock: CREATE TABLE IF NOT EXISTS > Foo_xxxxxxxxx > >> (...)' > >> > >> Webserver is also blocked. Kill the 'worker' helps, all blocked stuff > >> continues. If anyone could know: > >> > >> - why it blocks since we have MyISAM,which are transaction-less > >> - why the worker is the blockator, since it only executes SELECTs > >> - how to fix it? > >> > > > > This sounds like more of a MySQL issue than an SQLAlchemy issue, so > > you might have more luck on a MySQL mailing list. In answer to your > > first question, > > http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html suggests > > that non-transactional tables can also be involved in metadata locks. > > I'm afraid I can't help with the rest. > > > > Googling "mysql Waiting for table metadata lock" gives a few clues. > This one looks like a nice short summary: > > http://www.chriscalender.com/?p=1189 > > Simon > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.