You are here
DEADLOCKS, INDEXING issue in MySQL
Thu, 2014-06-26 08:10 — kalasha
Dear Team,
I am frequently facing deadlock issue in mysql for below query. Due to this i have increased the innodb lock wait time out to 1800 seconds. Please help.
LATEST DETECTED DEADLOCK ------------------------ 2014-06-26 11:05:21 13c0 *** (1) TRANSACTION: TRANSACTION 17064867, ACTIVE 17 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 8 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 2 MySQL thread id 7778, OS thread handle 0xb9c, query id 23386973 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406250843353122' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170648 67 lock_mode X waiting Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; *** (2) TRANSACTION: TRANSACTION 17063837, ACTIVE 173 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 14 lock struct(s), heap size 3112, 22 row lock(s), undo log entries 10 MySQL thread id 7765, OS thread handle 0x13c0, query id 23387033 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406251613333122' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170638 37 lock_mode X Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;;
mysql> show create table trackfield_table\G *************************** 1. row *************************** Table: trackfield_table Create Table: CREATE TABLE `trackfield_table` ( `trackfield_id` int(11) NOT NULL AUTO_INCREMENT, `track_id` varchar(20) NOT NULL, `brand_id` int(11) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, `dimension` varchar(45) DEFAULT NULL, `imei` varchar(45) DEFAULT NULL, `mode_type` int(11) DEFAULT NULL, `status` int(2) DEFAULT NULL, `reason` varchar(45) DEFAULT NULL, `reasonForRejection` int(4) DEFAULT NULL, `advt_type` int(2) DEFAULT NULL, `village_code` varchar(15) NOT NULL, `tehsil_code` varchar(15) NOT NULL, `district_code` varchar(15) NOT NULL, `state_code` varchar(15) NOT NULL, `assignee_id` int(11) DEFAULT NULL, `sub_timestamp` varchar(45) DEFAULT NULL, `meterFlag` varchar(45) DEFAULT '0', `details` varchar(200) DEFAULT NULL, `recce_done` varchar(3) DEFAULT NULL, `remarks` varchar(50) DEFAULT NULL, `image_address` varchar(120) DEFAULT NULL, `nearBy_locationId` varchar(45) DEFAULT NULL, `sms_code` varchar(45) DEFAULT NULL, `is_ValidSms` varchar(4) DEFAULT NULL, `edit_history` varchar(125) DEFAULT NULL, `_date` date NOT NULL, PRIMARY KEY (`trackfield_id`), KEY `track_id_FK` (`track_id`), KEY `brand_FK` (`brand_id`), KEY `assign_FK` (`assignee_id`), KEY `VillageCode` (`village_code`,`state_code`), KEY `advt_type_idx` (`advt_type`) ) ENGINE=InnoDB AUTO_INCREMENT=44366 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> show create table status_table\G *************************** 1. row *************************** Table: status_table Create Table: CREATE TABLE `status_table` ( `status_id` int(11) NOT NULL AUTO_INCREMENT, `status` varchar(100) NOT NULL, PRIMARY KEY (`status_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show create table rejection_reason\G *************************** 1. row *************************** Table: rejection_reason Create Table: CREATE TABLE `rejection_reason` ( `reason_id` int(11) NOT NULL AUTO_INCREMENT, `reason` varchar(100) DEFAULT NULL, PRIMARY KEY (`reason_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> explain select * from wp_schema.trackfield_table where status=1 and track_id='1406251132357045'; +----+-------------+------------------+------+---------------+-------------+---------+-------+------+--------------------- --------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------+---------------+-------------+---------+-------+------+--------------------- --------------+ | 1 | SIMPLE | trackfield_table | ref | track_id_FK | track_id_FK | 62 | const | 1 | Using index conditio ; Using where | +----+-------------+------------------+------+---------------+-------------+---------+-------+------+--------------------- --------------+ 1 row in set (0.00 sec) *************************** 20. row *************************** Id: 7765 db: wp_schema Command: Execute Time: 557 State: updating Info: Update wp_schema.trackfield_table Set status=1 where track_id='1406251132357045' *************************** 21. row *************************** Id: 7775 db: wp_schema Command: Execute Time: 32 State: updating Info: Update wp_schema.trackfield_table Set status=1 where track_id='1406251820523863' *************************** 22. row *************************** Id: 7776 db: wp_schema Command: Execute Time: 48 State: updating Info: Update wp_schema.trackfield_table Set status=1 where track_id='1406251820523863' *************************** 23. row *************************** Id: 7778 db: wp_schema Command: Execute Time: 547 State: updating Info: Update wp_schema.trackfield_table Set status=1 where track_id='1406251132357045' *************************** 24. row ***************************
Support Ticket