共计 3083 个字符，预计需要花费 8 分钟才能阅读完成。
MySQL > show slave status\G;
Last_SQL_Errno: 1205
Last_SQL_Error: Error ‘Lock wait timeout exceeded; try restarting transaction’ on query. Default database: ‘gcore’. Query: ‘INSERT INTO `GC_Price_Archive` (`PKID`, `Barcode`, `Price`, `STID`, `STCode`, `CRCode`, `State`, `OptTime`, `CreatedTime`, `Remarks`) VALUES (‘1625018’, ‘6900451666012’, ‘10.80’, ‘10665’, ‘02710664’, ‘2706’, ‘1’, ‘2013-07-23 13:46:47’, ‘2013-07-23 14:40:55’, ‘3’)’
查看 mysql 正在运行线程
mysql > show full processlist;
| 93862 | xxxxx | 192.168.3.177:1775 | xxxxx | Query | 53 | Sending data | select count(distinct month(opttime)) into g from gcore.GC_Price_Archive A where A.barcode= NAME_CONST(‘barcodea’,6901404321200) and A.state=1 and A.opttime>=’2012-07-01 00:00:00′ and A.opttime<‘2013-07-01 00:00:00’ |
上面的 sql 执行线程与出现锁等待超时的 query 均使用 GC_Price_Archive 表，对这个等待超时导致同步停掉的问题表示很不理解。
为了让同步继续运行，我 kill 掉了正在执行的 select 查询，然后 start slave SQL_Thread。同步继续运行。
如果从服务器上经常要执行报表统计查询，可以在配置文件 my.cnf 中修改 innodb_rollback_on_timeout 选项，默认值是 50，改成更大的值，避免 slave 执行 sql 经常出现锁等待超时。
The timeout in seconds an InnoDB transaction waits for a row lock before giving up.The default value is
50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits
at most this many seconds for write access to the row before issuing the following error:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
When a lock wait timeout occurs, the current statement is rolled back (not the entire
transaction). To have the entire transaction roll back, start the server with the —
innodb_rollback_on_timeout [1605] option. See also Section 14.3.13,“InnoDB Error Handling”.
You might decrease this value for highly interactive applications or OLTP systems, to display user
feedback quickly or put the update into a queue for processing later. You might increase this value for
long-running back-end operations, such as a transform step in a data warehouse that waits for other
large insert or update operations to finish.
innodb_lock_wait_timeout [1595] applies to InnoDB row locks only. A MySQL table lock does not
happen inside InnoDB and this timeout does not apply to waits for table locks.
The lock wait timeout value does not apply to deadlocks, because InnoDB detects them immediately
and rolls back one of the deadlocked transactions.
MySQL 主从复制 Error1205
主从架构。今天发现从库 SQL 线程报错，主从复制停止了。查看错误发现：
Last_SQL_Errno: 1205
Last_SQL_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.
查看错误日志发现：
2016-05-25 07:27:09 72865 [Warning] Slave SQL: Could not execute Write_rows event on table xxx.xxx; Lock wait timeout exceeded; try restarting transaction, Error_code:
1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event’s master log mysql-bin.000121, end_log_pos 21432849, Error_code: 1205
2016-05-25 07:27:09 72865 [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
2016-05-25 07:27:09 72865 [Warning] Slave: Lock wait timeout exceeded; try restarting transaction Error_code:1205
2016-05-25 07:27:09 72865 [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.000121’ position 21432467
解决办法：
start slave;
原因：
是 MySQL 的一个 bug，可以考虑把 patch 给打上
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-08/134759.htm
