CODEWITHSUNDEEP
mysqlinnodbdeadlockrds
Sandeep
Sandeep

Reputation: 11

System Lock / Dead lock on one of the tables in MySQL innodb

Thanks for taking the time to look at my problem.

We have a huge table in our database with about 73L records and updating this table sometimes leads to dead lock in the innodb status log.

We are running an RDS instance of MySQL server with 3000IOPS. This table has innodb engine and I have increased the innodb_lock_wait_timeout value to 300 seconds but still I sometimes face these issues.

Could some one please let us know how I can improve performance of this table and avoid deadlock situation.

Below is the output from innodb status

 ------------------------
 LATEST DETECTED DEADLOCK
 ------------------------
 2015-02-15 15:39:04 2ba739b85700
 *** (1) TRANSACTION:
 TRANSACTION 1400680480, ACTIVE 0 sec fetching rows
 mysql tables in use 4, locked 4
 LOCK WAIT 756 lock struct(s), heap size 95784, 146885 row lock(s), undo log entries 1
 MySQL thread id 88039, OS thread handle 0x2ba73c21d700, query id 15197460 172.31.1.195 ygroot updating
 update yg_inventory.DAILY_INVENTORY_DETAILS SET BOOKING_ID= NAME_CONST('bookingId',XXXXXXXXXXXX),TICKET_ID= NAME_CONST('ticketId',_utf8'XXXXXXX' COLLATE 'utf8_general_ci'),BOOKING_STATUS= NAME_CONST('seatStatus',_utf8'LOCKED' COLLATE 'utf8_general_ci'),PASSENGER_NAME= NAME_CONST('userName',_utf8'XXXXX' COLLATE 'utf8_general_ci'),
       PASSENGER_AGE= NAME_CONST('age',28),PASSENGER_GENDER= NAME_CONST('gender',_utf8'M' COLLATE 'utf8_general_ci'),PNR_NUMBER= NAME_CONST('pnrNumber',_utf8'DXHD2087' COLLATE 'utf8_general_ci')
       where inventory_id= NAME_CONST('inventoryId',xxx) and service_date= NAME_CONST('serviceDate',_latin1'2015-02-16' COLLATE 'latin1_swedish_ci') and journey_date= NAME_CONST('journeyDate',_latin1'2015-02-16' COLLATE 'latin1_swedish_ci') and BOARDING_CITY_NAME= NAME_CONST('fromCityOfService',_utf8'XXXX' COLLATE 'utf8_general_ci')  and DESTINATION_CITY_NAME= NAME_CONST('to
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 891 page no 27784 n bits 240 index `PRIMARY` of table `yg_inventory`.`DAILY_INVENTORY_DETAILS` trx id 1400680480 lock_mode X locks rec but not gap waiting
 Record lock, heap no 130 PHYSICAL RECORD: n_fields 26; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 1400681233
 Purge done for trx's n:o < 1400681231 undo n:o < 0 state: running but idle
 History list length 1295
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 0, not started
 MySQL thread id 88108, OS thread handle 0x2ba73cd8a700, query id 15203081 103.6.158.188 ygroot init
 show engine innodb status
 ---TRANSACTION 1400681227, not started
 MySQL thread id 88110, OS thread handle 0x2ba73e6ee700, query id 15203033 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400681220, not started
 MySQL thread id 88111, OS thread handle 0x2ba73b8f9700, query id 15202948 172.31.1.195 ygappadmin cleaning up
 ---TRANSACTION 0, not started
 MySQL thread id 88107, OS thread handle 0x2ba73e5a9700, query id 15202372 103.6.158.188 ygroot cleaning up
 ---TRANSACTION 1400681028, not started
 MySQL thread id 88109, OS thread handle 0x2ba737367700, query id 15201526 172.31.1.195 ygappadmin cleaning up
 ---TRANSACTION 1400681136, not started
 MySQL thread id 88106, OS thread handle 0x2ba73aa80700, query id 15202485 172.31.1.195 ygappadmin cleaning up
 ---TRANSACTION 1400681072, not started
 MySQL thread id 88104, OS thread handle 0x2ba73a19d700, query id 15202465 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400681128, not started
 MySQL thread id 88105, OS thread handle 0x2ba73e1da700, query id 15202462 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400680564, not started
 MySQL thread id 88071, OS thread handle 0x2ba73a878700, query id 15198406 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400680991, not started
 MySQL thread id 88047, OS thread handle 0x2ba73dd89700, query id 15201317 172.31.1.195 ygappadmin cleaning up
 ---TRANSACTION 1400680450, not started
 MySQL thread id 88032, OS thread handle 0x2ba73e054700, query id 15197167 172.31.1.195 ygappadmin cleaning up
 ---TRANSACTION 1400681214, not started
 MySQL thread id 88031, OS thread handle 0x2ba73a9bd700, query id 15202885 172.31.1.195 ygappadmin cleaning up
 ---TRANSACTION 1400681205, not started
 MySQL thread id 88024, OS thread handle 0x2ba748c61700, query id 15202803 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400681232, not started
 MySQL thread id 88019, OS thread handle 0x2ba73e199700, query id 15203071 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400678534, not started
 MySQL thread id 87983, OS thread handle 0x2ba736264700, query id 15183429 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400676175, not started
 MySQL thread id 87896, OS thread handle 0x2ba73609d700, query id 15160860 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400673932, not started
 MySQL thread id 87816, OS thread handle 0x2ba73746b700, query id 15116419 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400671701, not started
 MySQL thread id 87744, OS thread handle 0x2ba73db40700, query id 15073635 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400669215, not started
 MySQL thread id 87657, OS thread handle 0x2ba73e62b700, query id 15051389 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400667253, not started
 MySQL thread id 87579, OS thread handle 0x2ba735fda700, query id 15035335 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400664618, not started
 MySQL thread id 87476, OS thread handle 0x2ba73d66d700, query id 15015550 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400661900, not started
 MySQL thread id 87388, OS thread handle 0x2ba73cbc3700, query id 14997598 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400665867, not started
 MySQL thread id 85565, OS thread handle 0x2ba737632700, query id 15196777 123.238.96.102 ygroot cleaning up
 ---TRANSACTION 1400658678, not started
 MySQL thread id 87317, OS thread handle 0x2ba739ac2700, query id 14976516 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400655897, not started
 MySQL thread id 87226, OS thread handle 0x2ba73a260700, query id 14950857 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400653342, not started
 MySQL thread id 87149, OS thread handle 0x2ba746f2e700, query id 14925049 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400650544, not started
 MySQL thread id 87045, OS thread handle 0x2ba73de0b700, query id 14814935 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400647969, not started
 MySQL thread id 86954, OS thread handle 0x2ba7385f0700, query id 14595487 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400644872, not started
 MySQL thread id 86866, OS thread handle 0x2ba73f4a4700, query id 14561401 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400642201, not started
 MySQL thread id 86771, OS thread handle 0x2ba73d569700, query id 14541434 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400639514, not started
 MySQL thread id 86671, OS thread handle 0x2ba73e31f700, query id 14510294 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400636665, not started
 MySQL thread id 86577, OS thread handle 0x2ba73c772700, query id 14479904 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400633377, not started
 MySQL thread id 86473, OS thread handle 0x2ba73f3a0700, query id 14439515 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400630232, not started
 MySQL thread id 86366, OS thread handle 0x2ba73b426700, query id 14402799 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400627516, not started
 MySQL thread id 86273, OS thread handle 0x2ba7445cb700, query id 14373349 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400624620, not started
 MySQL thread id 86179, OS thread handle 0x2ba73952c700, query id 14339074 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400621003, not started
 MySQL thread id 86055, OS thread handle 0x2ba735e54700, query id 14294908 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 1400617798, not started
 MySQL thread id 85946, OS thread handle 0x2ba73aa3f700, query id 14235152 172.31.1.195 ygroot cleaning up
 ---TRANSACTION 0, not started
 MySQL thread id 85354, OS thread handle 0x2ba738b45700, query id 15196769 123.238.96.102 ygroot cleaning up
 ---TRANSACTION 0, not started
 MySQL thread id 85109, OS thread handle 0x2ba73b8b8700, query id 13927573 123.238.96.102 ygroot cleaning up
 ---TRANSACTION 1400681120, not started
 MySQL thread id 24, OS thread handle 0x2ba7363a9700, query id 15202396 localhost 127.0.0.1 rdsadmin cleaning up
 ---TRANSACTION 1400681228, ACTIVE 1 sec
 MySQL thread id 88103, OS thread handle 0x2ba73e770700, query id 15203055 172.31.1.195 ygroot cleaning up
 Trx read view will not see trx with id >= 1400681229, sees < 1400681229

Thanks Sandeep Nanduri

Upvotes: 1

Views: 6186

Answers (1)

Schwern
Schwern

Reputation: 164919

You can use innotop to analyze deadlocks. This article goes into some detail about analyzing deadlocks with and without innotop.

Upvotes: 2

Related Questions