问题描述:

I'm using 1.2.2 MySQLdb with Python 2.6.6 and MariaDB 5.5.30 on Debian Squeezy.

All tables is using MyIsam engine.

My code is writing some data into temporary table, then inserting it into permanent one and truncating temp after that. So, temp table is some kind of buffer.

Sometimes (very often, but not always) it freezes on truncating. MySQL command-line client shows that it's waiting for table metadata lock. There is no other processes which might lock table.

MariaDB [torgi]> show processlist;

+-----+------+-----------+-------+---------+------+---------------------------------+------------------------------+----------+

| Id | User | Host | db | Command | Time | State | Info | Progress |

+-----+------+-----------+-------+---------+------+---------------------------------+------------------------------+----------+

| 194 | root | localhost | torgi | Sleep | 29 | | NULL | 0.000 |

| 195 | root | localhost | torgi | Query | 29 | Waiting for table metadata lock | TRUNCATE `notifications_new` | 0.000 |

| 196 | root | localhost | torgi | Query | 0 | NULL | show processlist | 0.000 |

+-----+------+-----------+-------+---------+------+---------------------------------+------------------------------+----------+

3 rows in set (0.00 sec)

I've tried to turn autocommit on, to commit manually or flush tables (see commented lines). Neither helps.

Here is the code:

def upt(table, con) :

#con.autocommit(True)

cur = con.cursor()

cur.execute('REPLACE INTO `'+table+suffix+'` select * from `'+table+tmp_suffix+'`;')

#cur.execute('FLUSH tables;')

#con.commit()

cur.execute('TRUNCATE `'+table+tmp_suffix+'`;') # Freeze here!

So, why is it getting locked and how to fix it?

相关阅读:
Top