mysql 死锁怎么办？

孤山一笑

lp的mysql死锁了~怎么解决？

一路同行

死锁？能具体点吗

孤山一笑

在CMD下 show processlist;
有许多进程被Locked 我用kill结束掉，又不停的产生。

一路同行

你是什么主机？独立主机

孤山一笑

不是的 也是共享的
lp 的mysql 支持远程命令行链接的啊

孤山一笑

mysql> show processlist;
+----------+----------------+------------------+------------+---------+------+--------+---------------------------------
---------------------------------------------------------------------+
| Id       | User           | Host             | db         | Command | Time | State  | Info
                                                                     |
+----------+----------------+------------------+------------+---------+------+--------+---------------------------------
---------------------------------------------------------------------+
| 16560780 | test | localhost        | airfo8_btk | Query   |   87 | Locked | select p2c.categories_id from pr
oducts p, products_to_categories p2c where p.products_id = '1440' an |
| 16560787 | test | 116.77.10.6:3438 | airfo8_btk | Query   |    0 | NULL   | show processlist
                                                                     |
| 16560868 | test | localhost        | airfo8_btk | Query   |   69 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16560876 | test | localhost        | airfo8_btk | Query   |   67 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561009 | test | localhost        | airfo8_btk | Query   |   24 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561039 | test | localhost        | airfo8_btk | Query   |   14 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561109 | test | localhost        | airfo8_btk | Query   |    1 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
+----------+----------------+------------------+------------+---------+------+--------+---------------------------------
---------------------------------------------------------------------+

孤山一笑

我这个主机上一共建了3个数据库，其中一个是因为我之前在phpmyadmin查询数据的时候，参数写错了。造成了大量的循环。

其它两个数据库正常，现在这个数据库对应的网站已经无法打开郁闷。

已经联系了LP的客服。等待回信中。

孤山一笑

转眼之间是这样，不停的增加~

mysql> show processlist;
+----------+----------------+------------------+------------+---------+------+--------+---------------------------------
---------------------------------------------------------------------+
| Id       | User           | Host             | db         | Command | Time | State  | Info
                                                                     |
+----------+----------------+------------------+------------+---------+------+--------+---------------------------------
---------------------------------------------------------------------+
| 16560780 | test| localhost        | airfo8_btk | Query   |  277 | Locked | select p2c.categories_id from pr
oducts p, products_to_categories p2c where p.products_id = '1440' an |
| 16560787 | test| 116.77.10.6:3438 | airfo8_btk | Query   |    0 | NULL   | show processlist
                                                                     |
| 16560868 | test| localhost        | airfo8_btk | Query   |  259 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16560876 | test| localhost        | airfo8_btk | Query   |  257 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561009 | test| localhost        | airfo8_btk | Query   |  214 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561039 | test| localhost        | airfo8_btk | Query   |  204 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561109 | test| localhost        | airfo8_btk | Query   |  191 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561122 | test| localhost        | airfo8_btk | Query   |  188 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561203 | test| localhost        | airfo8_btk | Query   |  170 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561210 | test| localhost        | airfo8_btk | Query   |  168 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561256 | test| localhost        | airfo8_btk | Query   |  149 | Locked | select distinct m.manufacturers_
id as id, m.manufacturers_name as name from products p, products_to_ |
| 16561259 | test| localhost        | airfo8_btk | Query   |  148 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561332 | test| localhost        | airfo8_btk | Query   |  125 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561342 | test| localhost        | airfo8_btk | Query   |  122 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561433 | test| localhost        | airfo8_btk | Query   |   88 | Locked | select distinct m.manufacturers_
id as id, m.manufacturers_name as name from products p, products_to_ |
| 16561504 | test| localhost        | airfo8_btk | Query   |   69 | Locked | select distinct m.manufacturers_
id as id, m.manufacturers_name as name from products p, products_to_ |
| 16561512 | test| localhost        | airfo8_btk | Query   |   67 | Locked | select distinct m.manufacturers_
id as id, m.manufacturers_name as name from products p, products_to_ |
| 16561529 | test| localhost        | airfo8_btk | Query   |   62 | Locked | select distinct m.manufacturers_
id as id, m.manufacturers_name as name from products p, products_to_ |
| 16561536 | test| localhost        | airfo8_btk | Query   |   58 | Locked | select distinct m.manufacturers_
id as id, m.manufacturers_name as name from products p, products_to_ |
| 16561537 | test| localhost        | airfo8_btk | Query   |   58 | Locked | select p.products_id, p.products
_image from products p join products_to_categories ptc on p.products |
| 16561547 | test| localhost        | airfo8_btk | Query   |   53 | Locked | select distinct m.manufacturers_
id as id, m.manufacturers_name as name from products p, products_to_ |
| 16561656 | test| localhost        | airfo8_btk | Query   |   12 | Locked | select distinct m.manufacturers_
id as id, m.manufacturers_name as name from products p, products_to_ |
| 16561657 | test| localhost        | airfo8_btk | Query   |   12 | Locked | select distinct m.manufacturers_
id as id, m.manufacturers_name as name from products p, products_to_ |
| 16561673 | test| localhost        | airfo8_btk | Query   |    6 | Locked | select * from products_descripti
on pd join products p on pd.products_id = p.products_id join special |
+----------+----------------+------------------+------------+---------+------+--------+---------------------------------
---------------------------------------------------------------------+
24 rows in set (0.77 sec)

一路同行

进程结束不了吗？要联系LP主机上了

孤山一笑

是的 无法结束 数据部本身有100多M
我还有个想法就是用Cron jobs备份数据到ftp上
然后重新建个数据库 导入
删除原数据库