You are here

DEADLOCKS, INDEXING issue in MySQL

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 ***************************

Taxonomy upgrade extras: 

Hello kalasha, can you please open a support ticket at FromDuals ticketing tool? Nobody will take care here... Regards, Oli
olicomment