MySQL Forums
Forum List  »  InnoDB

DEADLOCKS, INDEXING AND PRIMARY KEY'S - INNODB TABLE
Posted by: kalasha h
Date: June 26, 2014 12:28AM

Frequently receiving dead lock issue in innodb table. Please check the below dead lock and table structure. Kindly help to fix this issue


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

Options: ReplyQuote


Subject
Views
Written By
Posted
DEADLOCKS, INDEXING AND PRIMARY KEY'S - INNODB TABLE
2412
June 26, 2014 12:28AM


Sorry, you can't reply to this topic. It has been closed.

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.