MySQL Forums
Forum List  »  InnoDB

InnoDB: Lock wait timeout exceeded; try restarting transaction
Posted by: Aravind M D
Date: July 26, 2018 03:57AM

Hi All,

We are trying to alter one of our table containing 8GB of data. We are getting below error when trying to alter

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

We tried increasing innodb_lock_wait_timeout but still alter command was failing.

Below is the output of InnoDB engine status during the modification

------------
TRANSACTIONS
------------
Trx id counter 12490762283
Purge done for trx's n:o < 6763753805 undo n:o < 0 state: running but idle
History list length 212418184
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 10, OS thread handle 0x7eec55aee700, query id 413 localhost 127.0.0.1 abc init
SHOW ENGINE INNODB STATUS
---TRANSACTION 12490762190, ACTIVE 867 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 212076 lock struct(s), heap size 20379176, 15662689 row lock(s), undo log entries 15450606
MySQL thread id 1, OS thread handle 0x7eec55c68700, query id 363 localhost devops_cres copy to tmp table
ALTER TABLE abc CHANGE COLUMN C_YU C_YU INT(11) NOT NULL AUTO_INCREMENT
------- TRX HAS BEEN WAITING 353 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 875233 page no 443793 n bits 160 index `PRIMARY` of table `db`.`user` trx id 12490762190 lock mode S waiting
Record lock, heap no 23 PHYSICAL RECORD: n_fields 31; compact format; info bits 0

Processlist was not showing anything apart from replication access from Slave servers.

Could you please let us know why table getting locked during alter?

Aravind M D

Options: ReplyQuote


Subject
Views
Written By
Posted
InnoDB: Lock wait timeout exceeded; try restarting transaction
684
July 26, 2018 03:57AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.