Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up导致主从不同步的解决方法-CSDN博客

本文链接：https://blog.csdn.net/u014609263/article/details/90474788

博客围绕MySQL 5.6.34版本展开，主库执行大事务delete后，从库复制中断报错。经查看，参数innodb_lock_wait_time为15秒，超时会报1025错，slave_transaction_retries为10次，重试超10次复制中断。因从库单线程复制，推测是MySQL bug，解决办法是升级版本或重做主从。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql版本：5.6.34
在主库上做了大事务的delete，从库报Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.003302
          Read_Master_Log_Pos: 84017509
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 408
        Relay_Master_Log_File: mysql-bin.003216
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1205
                   Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.

查看错误日志

2019-05-22 20:11:27 35336 [ERROR] Slave SQL: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable. Error_code: 1205
2019-05-22 20:11:27 35336 [Warning] Slave: Lock wait timeout exceeded; try restarting transaction Error_code: 1205
2019-05-22 20:11:27 35336 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.003302' position 84017509

参数innodb_lock_wait_time 设置的为15秒，单个事务在等待15秒后开始报1025错：因为锁执行超时并重启事务
参数slave_transaction_retries 设置的为10次，如果事务重试次数超过10次，复制中断
但是从库是单线程复制，按理说不应该存在锁等待的情况，查了一些资料后猜想是mysql的一个bug，解决方法只能升级版本或者重做主从。