Lock wait timeout exceeded解决优化方案

• 一 , 定位问题
• • 可以通过以下几种命令去定位
  • 二 , 解决方案
  • • 紧急措施
    • • 1.紧急措施：终止（KILL）相关的线程，释放任何被该线程持有的锁
      • 2.次之紧急措施：调整 Innodb 锁等待时间，延缓了问题的出现
      • 非紧急措施

        “Lock wait timeout exceeded; try restarting transaction” 一般出现这样的错误时，往往是因为一些事务在等待获取某个锁，但由于这个锁被其他长时间未完成的事务持有，所以导致等待超时。

        一 , 定位问题

        可以通过以下几种命令去定位

        • SHOW FULL PROCESSLIST;显示当前MySQL中的所有进程信息，包括每个进程的ID、用户、主机、数据库、命令、运行时间等。如果你看到有某个进程的运行时间特别长，或者状态一直处于"Locked"，那么就有可能是这个进程导致了锁等待超时。

          eg: 881611 root xxxx:60646 xxx Query 2046 starting xxxx xxxx

        • SHOW ENGINE INNODB STATUS;：显示InnoDB存储引擎的详细状态信息，包括当前的事务、锁情况、缓冲池状态、磁盘I/O信息等。然后通过一下两部分定位。

          • LATEST DETECTED DEADLOCK会显示最新检测到的死锁信息，
          • TRANSACTIONS"部分会显示当前 InnoDB 引擎中的事务状态，包括事务的 ID，状态，所持有的锁，以及正在等待的锁等信息。

          • SHOW OPEN TABLES;：显示当前打开的表的信息，了解哪些表可能正在被锁定。

          • SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;：显示当前的InnoDB锁情况。可以直观地查看锁的状态，需要InnoDB引擎支持。

            二 , 解决方案

            紧急措施

            1.紧急措施：终止（KILL）相关的线程，释放任何被该线程持有的锁

            eg: 以上面的例子为列 KILL 881611

            需要注意，可能会对业务产生影响，导致数据状态不一致或者业务逻辑中断 ！！！

            2.次之紧急措施：调整 Innodb 锁等待时间，延缓了问题的出现

            注意 这只是允许线程等待更长的时间来获取锁，而不是解决了锁的竞争问题。还是会导致其他线程仍然在等待获取锁 ！！！

            • 临时调整 优先推荐
              • SET GLOBAL innodb_lock_wait_timeout = 120;立即生效，但是在 MySQL 服务器重启之后就会失效。
              • 永久调整
                • MySQL 的配置文件通常是 my.cnf 或 my.ini，位于 MySQL 服务器的安装目录下。在 [mysqld] 部分添加或修改以下行：

                  [mysqld]
                  innodb_lock_wait_timeout = 120
                  

                  非紧急措施

                  1. 优化查询：优化导致问题的查询语句，例如通过添加索引来提高查询性能，或者改变查询的逻辑以避免需要获取锁。
                  2. 修改数据库的并发控制策略：例如，你可以考虑使用乐观锁（如果应用逻辑允许）。
                  3. 设置合理的连接数：如果有太多的连接在等待获取锁，那么你可能需要减少最大连接数，以减少锁争用的可能性。
                     1. 查看当前的连接数 SHOW STATUS LIKE 'Threads_connected';
                     2. 设置最大连接数 SET GLOBAL max_connections = 200;
                  4. 开启并优化慢查询日志
                     1. 确认慢查询日志是否已经开启 SHOW VARIABLES LIKE 'slow_query_log';
                     2. 开启慢查询日志 SET GLOBAL slow_query_log = 'ON';
                     3. 查看慢查询的时间阈值 SHOW VARIABLES LIKE 'long_query_time';
                     4. 查看慢查询日志的文件位置SHOW VARIABLES LIKE 'slow_query_log_file';
                  5. 调整事务的大小和复杂性
                     1. 更小、更简单的事务会导致更少的锁争用。

                  

                   
                   网站改版申请  网页制作公司官网  网站架构设计图  旅游网站建设规划书主题  西安高端品牌网站建设  凡科建站教程