On Thu, Aug 29, 2013 at 6:01 PM, diverman <pa...@schon.cz> wrote: > 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> >> wrote: >> > On Thu, Aug 29, 2013 at 4:48 PM, diverman <pa...@schon.cz> 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 > 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 unblocked! > Query OK, 0 rows affected, 1 warnings > mysql> > > Pavel >
I'm not quite sure what your point is. I assume you can also reproduce it with plain MySQL (you may need to "set @@autocommit=0;" in one or both consoles). I can't test it myself as I don't have MySQL 5.5. 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.
