• 最近热搜工具：

    当前位置：首页 > mysql数据库

    ADADADADAD

    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction[ mysql数据库 ]

    mysql数据库 时间：2024-12-03 12:11:26

    作者：文/会员上传

    如何提高mysql查询效率

    简介：

    2010.10.30
    要下班了，开发人员过来跟我说，为什么他截断一个表，才163条记录。但是需要等待很长时间。
    当时?想 应该是有等待事件，那就等着呗。于是我自己再Navicat 上操作他反应

    以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。

    2010.10.30
    要下班了，开发人员过来跟我说，为什么他截断一个表，才163条记录。但是需要等待很长时间。
    当时?想 应该是有等待事件，那就等着呗。于是我自己再Navicat 上操作他反应的那张表，结果出现下面的提示：

    这明显是出现了锁，而且这种情况下是表锁。我就问开发，是不是有人在操作这张表，他说就他一个人操作，他也早就没操作了。
    尼玛，这点印证了当初师傅的话，错误操作的人，永远都是会说：没有啊，我什么都没有做啊。

    先查看一下进程里面有没有锁：

    点击(此处)折叠或打开

      mysql> SHOW PROCESSLIST;
      +-------+------+----------------------+----------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
      | Id | User | Host | db | Command | Time | State | Info |
      +-------+------+----------------------+----------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
      | 85558 | root | 172.17.210.205:54661 | NULL | Sleep | 80883 | | NULL |
      | 85559 | root | 172.17.210.205:54662 | mdm_test | Sleep | 29632 | | NULL |
      | 86200 | root | 172.17.210.205:55170 | mdm_test | Query | 28942 | Sending data | INSERT INTO dim_pro_product_extension
      (product_no, brand_detail_no, attribute_no, attrib |
      | 86210 | root | 172.17.205.110:50237 | NULL | Sleep | 13007 | | NULL |
      | 86393 | root | 172.17.206.166:50555 | mdm_dev | Sleep | 2664 | | NULL |
      | 86394 | root | 172.17.206.166:50556 | mdm_dev | Sleep | 999 | | NULL |
      | 86403 | root | 172.17.206.166:50563 | mdm_dev | Sleep | 145 | | NULL |
      | 86404 | root | 172.17.206.166:50564 | mdm_dev | Sleep | 145 | | NULL |
      | 86409 | root | 172.17.206.166:50565 | mdm_dev | Sleep | 999 | | NULL |
      | 86410 | root | 172.17.206.166:50566 | mdm_dev | Sleep | 2664 | | NULL |
      | 86411 | root | 172.17.206.166:50569 | mdm_dev | Sleep | 145 | | NULL |
      | 86412 | root | 172.17.206.166:50570 | mdm_dev | Sleep | 145 | | NULL |
      | 86425 | root | 172.17.206.166:50593 | mdm_dev | Sleep | 114 | | NULL |
      | 86427 | root | 172.17.206.166:50595 | mdm_dev | Sleep | 114 | | NULL |
      | 86428 | root | 172.17.206.166:50596 | mdm_dev | Sleep | 114 | | NULL |
      | 86440 | root | 172.17.206.45:51526 | NULL | Sleep | 15874 | | NULL |
      | 86441 | root | 172.17.206.45:51527 | mdm | Sleep | 15684 | | NULL |
      | 86461 | root | 172.17.206.166:50620 | mdm_dev | Sleep | 145 | | NULL |
      | 86464 | root | 172.17.206.166:50623 | mdm_dev | Sleep | 145 | | NULL |
      | 86467 | root | 172.17.206.118:55598 | NULL | Sleep | 14845 | | NULL |
      | 86488 | root | 172.17.211.114:62576 | NULL | Sleep | 13999 | | NULL |
      | 86489 | root | 172.17.211.114:62593 | miu_mobile_server_ol | Sleep | 13992 | | NULL |
      | 86490 | root | 172.17.211.114:62712 | miu_mobile_server_ol | Sleep | 13986 | | NULL |
      | 86491 | root | 172.17.211.114:62772 | miu_mobile_server_ol | Sleep | 13974 | | NULL |
      | 86531 | root | 172.17.206.166:51119 | mdm_dev | Sleep | 999 | | NULL |
      | 86532 | root | 172.17.206.166:51120 | mdm_dev | Sleep | 999 | | NULL |
      | 86557 | root | 172.17.206.166:51173 | mdm_dev | Sleep | 114 | | NULL |
      | 86642 | root | 172.17.210.104:31027 | mdm | Sleep | 256 | | NULL |
      | 86643 | root | 172.17.210.104:31028 | mdm | Sleep | 2234 | | NULL |
      | 86644 | root | 172.17.210.104:31029 | mdm | Sleep | 256 | | NULL |
      | 86645 | root | 172.17.210.104:31030 | mdm | Sleep | 256 | | NULL |
      | 86652 | root | 172.17.206.114:55635 | mdm | Sleep | 853 | | NULL |
      | 86653 | root | 172.17.206.114:55636 | mdm | Sleep | 853 | | NULL |
      | 86654 | root | 172.17.206.114:55637 | mdm | Sleep | 853 | | NULL |
      | 86655 | root | 172.17.206.114:55638 | mdm | Sleep | 853 | | NULL |
      | 86656 | root | 172.17.206.114:55639 | mdm | Sleep | 911 | | NULL |
      | 86657 | root | 172.17.206.114:55640 | mdm | Sleep | 851 | | NULL |
      | 86658 | root | 172.17.206.114:55641 | mdm | Sleep | 853 | | NULL |
      | 86659 | root | 172.17.206.114:55642 | mdm | Sleep | 853 | | NULL |
      | 86660 | root | 172.17.206.114:55643 | mdm | Sleep | 825 | | NULL |
      | 86661 | root | 172.17.206.114:55644 | mdm | Sleep | 912 | | NULL |
      | 86662 | root | 172.17.206.114:55645 | mdm | Sleep | 827 | | NULL |
      | 86663 | root | 172.17.206.114:55646 | mdm | Sleep | 825 | | NULL |
      | 86664 | root | 172.17.206.114:56394 | mdm | Sleep | 912 | | NULL |
      | 86665 | root | 172.17.206.114:56395 | mdm | Sleep | 853 | | NULL |
      | 86672 | root | 172.17.206.114:56454 | mdm | Sleep | 824 | | NULL |
      | 86673 | root | 172.17.206.114:56455 | mdm | Sleep | 825 | | NULL |
      | 86674 | root | 172.17.206.114:56487 | mdm | Sleep | 851 | | NULL |
      | 86675 | root | 172.17.206.114:56488 | mdm | Sleep | 853 | | NULL |
      | 86690 | root | 172.17.206.166:51536 | mdm_dev | Sleep | 2529 | | NULL |
      | 86691 | root | 172.17.206.166:51537 | NULL | Sleep | 4628 | | NULL |
      | 86693 | root | 172.17.210.104:34433 | mdm | Sleep | 2204 | | NULL |
      | 86694 | root | 172.17.210.104:34434 | mdm | Sleep | 2234 | | NULL |
      | 86695 | root | 172.17.210.104:34441 | mdm | Sleep | 2204 | | NULL |
      | 86696 | root | 172.17.210.104:34442 | mdm | Sleep | 256 | | NULL |
      | 86697 | root | 172.17.210.104:35207 | mdm | Sleep | 2234 | | NULL |
      | 86698 | root | 172.17.210.104:35208 | mdm | Sleep | 2204 | | NULL |
      | 86699 | root | 172.17.210.104:35209 | mdm | Sleep | 2234 | | NULL |
      | 86700 | root | 172.17.210.104:35210 | mdm | Sleep | 2204 | | NULL |
      | 86701 | root | 172.17.210.104:35475 | mdm | Sleep | 2267 | | NULL |
      | 86702 | root | 172.17.210.104:35476 | mdm | Sleep | 2267 | | NULL |
      | 86703 | root | 172.17.210.104:35478 | mdm | Sleep | 2204 | | NULL |
      | 86704 | root | 172.17.210.104:35479 | mdm | Sleep | 256 | | NULL |
      | 86712 | root | 172.17.210.104:37471 | mdm | Sleep | 2040 | | NULL |
      | 86713 | root | 172.17.210.104:37472 | mdm | Sleep | 2040 | | NULL |
      | 86714 | root | 172.17.210.104:37473 | mdm | Sleep | 2040 | | NULL |
      | 86715 | root | 172.17.210.104:37474 | mdm | Sleep | 2040 | | NULL |
      | 86716 | root | 172.17.210.104:37475 | mdm | Sleep | 2040 | | NULL |
      | 86717 | root | 172.17.210.104:37476 | mdm | Sleep | 2040 | | NULL |
      | 86718 | root | 172.17.210.104:37477 | mdm | Sleep | 506 | | NULL |
      | 86719 | root | 172.17.210.104:37478 | mdm | Sleep | 2040 | | NULL |
      | 86720 | root | 172.17.210.104:37479 | mdm | Sleep | 506 | | NULL |
      | 86721 | root | 172.17.210.104:37480 | mdm | Sleep | 2040 | | NULL |
      | 86722 | root | 172.17.210.104:37481 | mdm | Sleep | 2040 | | NULL |
      | 86723 | root | 172.17.210.104:37482 | mdm | Sleep | 2040 | | NULL |
      | 86724 | root | 172.17.210.104:38325 | mdm_pro | Sleep | 1368 | | NULL |
      | 86725 | root | 172.17.210.104:38326 | mdm_pro | Sleep | 1368 | | NULL |
      | 86727 | root | 172.17.210.104:38428 | mdm_pro | Sleep | 1285 | | NULL |
      | 86728 | root | 172.17.210.104:38429 | mdm_pro | Sleep | 1285 | | NULL |
      | 86730 | root | 172.17.206.113:51041 | NULL | Sleep | 1269 | | NULL |
      | 86732 | root | 172.17.210.104:38468 | mdm_pro | Sleep | 1253 | | NULL |
      | 86733 | root | 172.17.210.104:38469 | mdm_pro | Sleep | 1253 | | NULL |
      | 86734 | root | 172.17.210.104:38470 | mdm_pro | Sleep | 1253 | | NULL |
      | 86735 | root | 172.17.210.104:38471 | mdm_pro | Sleep | 1253 | | NULL |
      | 86736 | root | 172.17.210.104:38605 | mdm_pro | Sleep | 1147 | | NULL |
      | 86737 | root | 172.17.210.104:38606 | mdm_pro | Sleep | 1147 | | NULL |
      | 86738 | root | 172.17.210.104:38607 | mdm_pro | Sleep | 1147 | | NULL |
      | 86739 | root | 172.17.210.104:38608 | mdm_pro | Sleep | 1147 | | NULL |
      | 86740 | root | 172.17.206.113:51064 | mdm | Sleep | 385 | | NULL |
      | 86741 | root | 172.17.210.205:55709 | NULL | Sleep | 1092 | | NULL |
      | 86742 | root | 172.17.210.205:55711 | mdm_test | Sleep | 1088 | | NULL |
      | 86743 | root | 172.17.210.205:55712 | mdm_test | Sleep | 786 | | NULL |
      | 86746 | root | 172.17.211.99:53039 | NULL | Sleep | 959 | | NULL |
      | 86747 | root | 172.17.211.99:53040 | mdm | Sleep | 958 | | NULL |
      | 86748 | root | 172.17.211.99:53080 | mdm_test | Sleep | 320 | | NULL |
      | 86750 | root | 172.17.211.99:53130 | mdm_test | Sleep | 800 | | NULL |
      | 86751 | root | 172.17.210.205:55718 | mdm_test | Sleep | 782 | | NULL |
      | 86752 | root | localhost | mdm_test | Query | 0 | init | SHOW PROCESSLIST |
      | 86755 | root | 172.17.206.113:51149 | NULL | Sleep | 122 | | NULL |
      | 86756 | root | 172.17.206.113:51150 | mdm | Sleep | 133 | | NULL |
      | 86759 | root | 172.17.206.113:51181 | mdm_test | Sleep | 124 | | NULL |
      | 86760 | root | 172.17.206.113:51183 | mdm | Sleep | 115 | | NULL |
      +-------+------+----------------------+----------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
      102 rows in set (0.00 sec)

      mysql>
    很明显出现了锁，也是他操作的那个库。

    当然如果没有看到正在执行的慢SQL记录线程，再去查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。

    mysql> SELECT * FROM information_schema.INNODB_TRX\G;
    ---略
    trx_mysql_thread_id:86200

    现在杀死进程：
    mysql> kill 86200;

    Query OK, 0 rows affected (0.00 sec)


    接下来阶段表的时候就很快了。

    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.docx

    将本文的Word文档下载到电脑

    推荐度：

    下载
    热门标签: 1205errorhy000

    精品

    申请https需要什么条件

    热门推荐

    hadoop分布式数据库怎样管理

    2024-12-03

    ajax和数据库交互的方法是什么

    2024-12-03

    hadoop archive适合哪些应用场景

    2024-12-06

    hbase metadata能否备份恢复

    2024-12-25

    如何使用Kafka构建可扩展的日志收集系统

    2024-12-03

    大家都在看

    测速网 inhv.cn 皖ICP备2023010105号 E-mail：251442993@qq.com 网速测试 网速测试 五险一金计算器 大小写转换 房贷计算器 个税计算器 网站建设

    本站内容来源于网友提交及搜索引擎，如果我们的某些资料侵犯了您的合法权益或对您造成了任何程度的伤害，请及时联系我们，我们将在收到通知后第一时间处理该内容。