title: System Lock / Dead lock on one of the tables in MySQL innodb
body: <p>Thanks for taking the time to look at my problem.</p>
  
  <p>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. </p>
  
  <p>We are running an RDS instance of MySQL server with 3000IOPS. This table has innodb engine and I have increased the <code>innodb_lock_wait_timeout</code> value to 300 seconds but still I sometimes face these issues. </p>
  
  <p>Could some one please let us know how I can improve performance of this table and avoid deadlock situation.</p>
  
  <p>Below is the output from innodb status</p>
  
  <pre><code> ------------------------
   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 &lt; 1400681231 undo n:o &lt; 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 &gt;= 1400681229, sees &lt; 1400681229
  </code></pre>
  
  <p>Thanks
  Sandeep Nanduri</p>