数据库 1205 Error 'Lock wait timeout exceeded; try restarting transaction' on query

34r2312

收到报警，mysql的从数据库在同步的过程出现问题，已停止同步。
mysql> show slave status\G                                                                 
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.199.50
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000381
          Read_Master_Log_Pos: 625068573
               Relay_Log_File: mysql-zhaowei-bj-199-52-relay-bin.000577
                Relay_Log_Pos: 542496522
        Relay_Master_Log_File: mysql-bin.000381
             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: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'v506'. Query: 'update account_log set createById=806378,createTime='2013-03-01 18:41:23.0',lastLogin='2015-01-14 11:04:53',lastPost=null,loginTimes=419,sessionId=null,machineCode=null,accountIP='123.133.132.196',accountBrowser=null,lastAccessed='web',lastTimezone=null,continuous=2 where accountId=806378'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 542496377
              Relay_Log_Space: 625068934
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1205
               Last_SQL_Error: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'v506'. Query: 'update account_log set createById=806378,createTime='2013-03-01 18:41:23.0',lastLogin='2015-01-14 11:04:53',lastPost=null,loginTimes=419,sessionId=null,machineCode=null,accountIP='123.133.132.196',accountBrowser=null,lastAccessed='web',lastTimezone=null,continuous=2 where accountId=806378'
1 row in set (0.00 sec)

经查询：

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
150114 11:34:27 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 35531892, signal count 35529980
Mutex spin waits 0, rounds 24045693, OS waits 327309
RW-shared spins 79151793, OS waits 35122704; RW-excl spins 774165, OS waits 46983
------------
TRANSACTIONS
------------
Trx id counter 2 146598135
Purge done for trx's n:o < 2 146596925 undo n:o < 0 0
History list length 9
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 2 146598134, not started, process no 14634, OS thread id 1179748672
MySQL thread id 28277, query id 1416247560 192.168.199.15 box
---TRANSACTION 0 0, not started, process no 14634, OS thread id 1097562432
MySQL thread id 28212, query id 1416247561 localhost root
show engine innodb status
---TRANSACTION 2 146597653, not started, process no 14634, OS thread id 1097828672
MySQL thread id 28028, query id 1416245550 192.168.199.15 skst
---TRANSACTION 2 146596927, not started, process no 14634, OS thread id 1098119488
MySQL thread id 27580, query id 1416245550 192.168.199.15 skst
---TRANSACTION 2 142726949, not started, process no 14634, OS thread id 1180014912
MySQL thread id 26261, query id 1416246087 192.168.199.15 skst
---TRANSACTION 0 0, not started, process no 14634, OS thread id 1096763712
MySQL thread id 26260, query id 1416246087 192.168.199.15 skst
---TRANSACTION 2 146596928, ACTIVE 1267 sec, process no 14634, OS thread id 1097029952 fetching rows, thread declared inside InnoDB 363
mysql tables in use 2, locked 1
31574 lock struct(s), heap size 3078128, 3333275 row lock(s)
MySQL thread id 27581, query id 1414544397 192.168.199.15 skst Copying to tmp table
select machineCode,createTime,accountIp from account_log where  machineCode in (select distinct machineCode from account_log where market regexp "FirstTimeForiPhone" and machineCode is not null)
Trx read view will not see trx with id >= 2 146596929, sees < 2 146494817

解决如下：

mysql> show full processlist;                                                              
+-------+-------------+----------------------+------+---------+---------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id    | User        | Host                 | db   | Command | Time    | State                            | Info                                                                                                                                                                                               |
+-------+-------------+----------------------+------+---------+---------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     1 | system user |                      | NULL | Connect | 1907304 | Waiting for master to send event | NULL                                                                                                                                                                                               |
| 26260 | skst        | 192.168.199.15:35845 | v506 | Sleep   |     398 |                                  | NULL                                                                                                                                                                                               |
| 26261 | skst        | 192.168.199.15:35846 | v506 | Sleep   |     398 |                                  | NULL                                                                                                                                                                                               |
| 27580 | skst        | 192.168.199.15:57148 | v506 | Sleep   |     481 |                                  | NULL                                                                                                                                                                                               |
| 27581 | skst        | 192.168.199.15:57149 | v506 | Query   |    1420 | Copying to tmp table             | select machineCode,createTime,accountIp from account_log where  machineCode in (select distinct machineCode from account_log where market regexp "FirstTimeForiPhone" and machineCode is not null) |
| 28028 | skst        | 192.168.199.15:60202 | v506 | Sleep   |     481 |                                  | NULL                                                                                                                                                                                               |
| 28212 | root        | localhost            | NULL | Query   |       0 | NULL                             | show full processlist                                                                                                                                                                              |
| 28369 | box         | 192.168.199.15:63407 | v506 | Sleep   |       0 |                                  | NULL                                                                                                                                                                                               |
+-------+-------------+----------------------+------+---------+---------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> kill 27581
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> show full processlist;
+-------+-------------+----------------------+------+---------+---------+----------------------------------+-----------------------+
| Id    | User        | Host                 | db   | Command | Time    | State                            | Info                  |
+-------+-------------+----------------------+------+---------+---------+----------------------------------+-----------------------+
|     1 | system user |                      | NULL | Connect | 1907317 | Waiting for master to send event | NULL                  |
| 26260 | skst        | 192.168.199.15:35845 | v506 | Sleep   |     411 |                                  | NULL                  |
| 26261 | skst        | 192.168.199.15:35846 | v506 | Sleep   |     411 |                                  | NULL                  |
| 27580 | skst        | 192.168.199.15:57148 | v506 | Sleep   |     494 |                                  | NULL                  |
| 28028 | skst        | 192.168.199.15:60202 | v506 | Sleep   |     494 |                                  | NULL                  |
| 28212 | root        | localhost            | NULL | Query   |       0 | NULL                             | show full processlist |
| 28376 | box         | 192.168.199.15:63414 | v506 | Sleep   |       0 |                                  | NULL                  |
+-------+-------------+----------------------+------+---------+---------+----------------------------------+-----------------------+
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.199.50
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000381
          Read_Master_Log_Pos: 643933828
               Relay_Log_File: mysql-zhaowei-bj-199-52-relay-bin.000577
                Relay_Log_Pos: 643933973
        Relay_Master_Log_File: mysql-bin.000381
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 643933828
              Relay_Log_Space: 643934189
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.01 sec)
问题解决！~