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
> <[email protected]<javascript:>>
> wrote:
> > On Thu, Aug 29, 2013 at 4:48 PM, diverman <[email protected] <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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
