MySQL Forums
Forum List  »  Newbie

Updating a record taking long time - MySQL 5.6.14
Posted by: kalasha h
Date: June 24, 2014 12:19AM

Hi Team,

MySQL is taking more time to execute a record. We have set the innodb lock wait time out to 1800 sec. Below query is taking long time to execute due to this we are facing “Lock wait time out exceeded” error. Kindly check the below issue and help me.

Update query, explain plan, table structure, innodb engine deadlock is shared below

This update query is taking more time - update wp_schema.trackfield_table set recce_done = 'Y' where track_id = 24.753079659952466

Update wp_schema.trackfield_table Set status=(select status_id from wp_schema.status_table where status='Approved'
) where track_id='1406140905115166'




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=40759 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> select * from trackfield_table where track_id = 24.753079659952466;
Empty set (0.20 sec)

mysql>
mysql>
mysql> explain select * from trackfield_table where track_id = 24.753079659952466;
+----+-------------+------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | trackfield_table | ALL | track_id_FK | NULL | NULL | NULL | 39570 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)



mysql> explain select * from trackfield_table where track_id='1406131756458397';
+----+-------------+------------------+------+---------------+-------------+---------+-------+------+----------------------
-+
| 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 condition
|
+----+-------------+------------------+------+---------------+-------------+---------+-------+------+----------------------
-+
1 row in set (0.02 sec)


*************************** 14. row ***************************
Id: 6550
User: WPFieldUser
Host: x.x.x.65189
db: wp_schema
Command: Query
Time: 833
State: updating
Info: update wp_schema.trackfield_table set recce_done = 'Y' where track_id = 24.753079659952466
*************************** 15. row ***************************
Id: 6551
User: WPFieldUser
Host: x.x.x.65190
db: wp_schema
Command: Query
Time: 1277
State: updating
Info: update wp_schema.trackfield_table set recce_done = 'Y' where track_id = 24.874279854126442
*************************** 16. row ***************************
Id: 6553
User: WPFieldUser
Host: x.x.x.65192
db: wp_schema
Command: Sleep
Time: 1278
State:
Info: NULL
*************************** 17. row ***************************
Id: 6562
User: WPFieldUser
Host: x.x.x.65201
db: wp_schema
Command: Execute
Time: 1039
State: updating
Info: Update wp_schema.trackfield_table Set status=(select status_id from wp_schema.status_table where status='Approved'
) where track_id='1406140905115166'
*************************** 18. row ***************************
Id: 6563
User: WPFieldUser
Host: x.x.x.65202
db: wp_schema
Command: Execute
Time: 1083
State: updating
Info: Update wp_schema.trackfield_table Set status=(select status_id from wp_schema.status_table where status='Approved'
) where track_id='1406131756458397'






------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-06-24 11:39:39 1120
*** (1) TRANSACTION:
TRANSACTION 15475918, ACTIVE 1913 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 458 lock struct(s), heap size 47544, 24881 row lock(s)
MySQL thread id 6564, OS thread handle 0xf3c, query id 20130216 10.0.0.6 WPFieldUser updating
update wp_schema.trackfield_table set recce_done = 'Y' where track_id = 24.874279854126442
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 241 page no 486 n bits 128 index `PRIMARY` of table `wp_schema`.`trackfield_table` trx id 15475918 lo
ck_mode X waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
0: len 4; hex 80005fcb; asc _ ;;
1: len 6; hex 000000ec3781; asc 7 ;;
2: len 7; hex 69000014c92c88; asc i , ;;
3: len 16; hex 31343036313431313237343035313039; asc 1406141127405109;;
4: len 4; hex 80000028; asc (;;
5: len 4; hex 80000089; asc ;;
6: len 3; hex 313130; asc 110;;
7: len 15; hex 333532333834303538333635313039; asc 352384058365109;;
8: len 4; hex 80000002; asc ;;
9: len 4; hex 80000001; asc ;;
10: SQL NULL;
11: SQL NULL;
12: len 4; hex 80000001; asc ;;
13: len 8; hex 3030303033363030; asc 00003600;;
14: len 4; hex 30303031; asc 0001;;
15: len 2; hex 3031; asc 01;;
16: len 2; hex 3239; asc 29;;
17: len 4; hex 80000089; asc ;;
18: len 19; hex 323031342d30362d31342031313a32393a3030; asc 2014-06-14 11:29:00;;
19: len 5; hex 32382e3838; asc 28.88;;
20: SQL NULL;
21: len 1; hex 4e; asc N;;
22: SQL NULL;
23: len 30; hex 2252656e64616c2d44686f6e657761646920526f61642c2044686f6e6577; asc "Rendal-Dhonewadi Road, Dhonew; (total 5
9 bytes);
24: len 16; hex 31343036313431313235343135313039; asc 1406141125415109;;
25: len 0; hex ; asc ;;
26: SQL NULL;
27: SQL NULL;
28: len 3; hex 800000; asc ;;

*** (2) TRANSACTION:
TRANSACTION 15480705, ACTIVE 1661 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 2, locked 2
11 lock struct(s), heap size 3112, 13 row lock(s), undo log entries 2
MySQL thread id 6573, OS thread handle 0x1120, query id 20184141 10.0.0.6 WPFieldUser updating
Update wp_schema.trackfield_table Set status=(select status_id from wp_schema.status_table where status='Approved'),edit_hi
story='UserName: app/Status: Rejected->Approved/Modified On: 06-24-14 11:39:38' where track_id='1406011014484819'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 241 page no 486 n bits 128 index `PRIMARY` of table `wp_schema`.`trackfield_table` trx id 15480705 lo
ck_mode X locks rec but not gap

Options: ReplyQuote


Subject
Written By
Posted
Updating a record taking long time - MySQL 5.6.14
June 24, 2014 12:19AM


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.