mysql - MySQL innodb 中的一个表上的系统锁/死锁

Question

感谢您花时间查看我的问题。

我们的数据库中有一个巨大的表，其中包含大约 73L 条记录，更新此表有时会导致 innodb 状态日志中出现死锁。

我们正在运行具有 3000IOPS 的 MySQL 服务器的 RDS 实例。该表具有 innodb 引擎，我已将 innodb_lock_wait_timeout 值增加到 300 秒，但有时我仍然会遇到这些问题。

有人可以告诉我们如何提高该表的性能并避免死锁情况吗。

下面是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

谢谢桑迪普·南杜里

Answer 1

您可以使用 innotop分析死锁。 This article goes into some detail about analyzing deadlocks有和没有 innotop。