Description:
We are using Mysql with Web apache/php application.
Periodically the whole web site is hunging with error [Lock wait timeout exceeded; try restarting transaction] on update statistical tables.
How is deadlock on insert in b_iblock_section_element table linked to updates of our statistical tables (b_stat_day,...)?
mysql> SHOW ENGINE INNODB STATUS\G;
LATEST DETECTED DEADLOCK
090311 6:19:59
*** (1) TRANSACTION:
TRANSACTION 0 14541775, ACTIVE 65 sec, process no 6728, OS thread id 1195632976 inserting
mysql tables in use 3, locked 3
LOCK WAIT 19 lock struct(s), heap size 3024, undo log entries 25
MySQL thread id 5071, query id 10282109 192.168.77.77 mkbase Sending data
INSERT INTO b_iblock_section_element(IBLOCK_SECTION_ID, IBLOCK_ELEMENT_ID) SELECT S.ID, E.ID FROM b_iblock_section S, b_iblock_element E WHERE S.IBLOCK_ID=E.IBLOCK_ID AND S.ID IN (0,39,38) AND E.ID = 236080
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 461340 n bits 608 index `UX_IBLOCK_SECTION_ELEMENT2` of table `mkbase/b_iblock_section_element` trx id 0 14541775 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 0 14548939, ACTIVE 31 sec, process no 6728, OS thread id 1187227984 inserting, thread declared inside InnoDB 500
mysql tables in use 3, locked 3
18 lock struct(s), heap size 3024, undo log entries 27
MySQL thread id 5346, query id 10282118 192.168.77.77 mkbase Sending data
INSERT INTO b_iblock_section_element(IBLOCK_SECTION_ID, IBLOCK_ELEMENT_ID) SELECT S.ID, E.ID FROM b_iblock_section S, b_iblock_element E WHERE S.IBLOCK_ID=E.IBLOCK_ID AND S.ID IN (0,31,32) AND E.ID = 236082
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 461340 n bits 608 index `UX_IBLOCK_SECTION_ELEMENT2` of table `mkbase/b_iblock_section_element` trx id 0 14548939 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 461340 n bits 608 index `UX_IBLOCK_SECTION_ELEMENT2` of table `mkbase/b_iblock_section_element` trx id 0 14548939 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)
TRANSACTIONS
Trx id counter 0 14820079
Purge done for trx's n:o < 0 14820077 undo n:o < 0 0
History list length 18
Total number of lock structs in row lock hash table 16
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 6728, OS thread id 1183025488
MySQL thread id 5418, query id 10532525 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 14820053, not started, process no 6728, OS thread id 1264974160
MySQL thread id 5376, query id 10532520 192.168.77.77 mkbase
...
---TRANSACTION 0 14820078, ACTIVE 4 sec, process no 6728, OS thread id 1084111184 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 4976, query id 10532524 192.168.77.77 mkbase Updating
UPDATE b_stat_day SET HITS = HITS + 1, FAVORITES = FAVORITES + 0, HOUR_HIT_14 = HOUR_HIT_14 + 1, WEEKDAY_HIT_3 = WEEKDAY_HIT_3 + 1, MONTH_HIT_3 = MONTH_HIT_3 + 1, HOUR_FAVORITE_14 = HOUR_FAVORITE_14 + 0, WEEKDAY_FAVORITE_3 = WEEKDAY_FAVORITE_3 + 0, MONTH_FAVORITE_3 = MONTH_FAVORITE_3 + 0 WHERE DATE_STAT = CURRENT_DATE
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 819218 n bits 192 index `IX_DATE_STAT` of table `mkbase/b_stat_day` trx id 0 14820078 lock_mode X locks rec but not gap waiting
Record lock, heap no 123 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 8fb26b; asc k;; 1: len 4; hex 8000007a; asc z;;
TABLE LOCK table `mkbase/b_stat_day` trx id 0 14820078 lock mode IX
RECORD LOCKS space id 0 page no 819218 n bits 192 index `IX_DATE_STAT` of table `mkbase/b_stat_day` trx id 0 14820078 lock_mode X locks rec but not gap waiting
Record lock, heap no 123 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 8fb26b; asc k;; 1: len 4; hex 8000007a; asc z;;
---TRANSACTION 0 14820073, ACTIVE 44 sec, process no 6728, OS thread id 1170418000 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 5343, query id 10532514 192.168.77.77 mkbase Updating
UPDATE b_stat_day SET HITS = HITS + 1, FAVORITES = FAVORITES + 0, HOUR_HIT_14 = HOUR_HIT_14 + 1, WEEKDAY_HIT_3 = WEEKDAY_HIT_3 + 1, MONTH_HIT_3 = MONTH_HIT_3 + 1, HOUR_FAVORITE_14 = HOUR_FAVORITE_14 + 0, WEEKDAY_FAVORITE_3 = WEEKDAY_FAVORITE_3 + 0, MONTH_FAVORITE_3 = MONTH_FAVORITE_3 + 0 WHERE DATE_STAT = CURRENT_DATE
------- TRX HAS BEEN WAITING 44 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 819218 n bits 192 index `IX_DATE_STAT` of table `mkbase/b_stat_day` trx id 0 14820073 lock_mode X locks rec but not gap waiting
Record lock, heap no 123 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 8fb26b; asc k;; 1: len 4; hex 8000007a; asc z;;
TABLE LOCK table `mkbase/b_stat_day` trx id 0 14820073 lock mode IX
RECORD LOCKS space id 0 page no 819218 n bits 192 index `IX_DATE_STAT` of table `mkbase/b_stat_day` trx id 0 14820073 lock_mode X locks rec but not gap waiting
Record lock, heap no 123 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 8fb26b; asc k;; 1: len 4; hex 8000007a; asc z;;
---TRANSACTION 0 14585003, ACTIVE 1271 sec, process no 6728, OS thread id 1195632976
1 lock struct(s), heap size 368
MySQL thread id 5071, query id 10311470 192.168.77.77 mkbase
TABLE LOCK table `mkbase/b_stat_session` trx id 0 14585003 lock mode IX
---TRANSACTION 0 14584852, ACTIVE 1272 sec, process no 6728, OS thread id 1180924240
23 lock struct(s), heap size 3024, undo log entries 10
MySQL thread id 5091, query id 10429882 192.168.77.77 mkbase
TABLE LOCK table `mkbase/b_stat_session` trx id 0 14584852 lock mode IX
RECORD LOCKS space id 0 page no 1196047 n bits 112 index `PRIMARY` of table `mkbase/b_stat_session` trx id 0 14584852 lock_mode X locks rec but not gap
Record lock, heap no 44 PHYSICAL RECORD: n_fields 36; compact format; info bits 0
TABLE LOCK table `mkbase/b_stat_hit` trx id 0 14584852 lock mode IX
TABLE LOCK table `mkbase/b_stat_day` trx id 0 14584852 lock mode IX
RECORD LOCKS space id 0 page no 819218 n bits 192 index `IX_DATE_STAT` of table `mkbase/b_stat_day` trx id 0 14584852 lock_mode X locks rec but not gap
Record lock, heap no 123 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 8fb26b; asc k;; 1: len 4; hex 8000007a; asc z;;
RECORD LOCKS space id 0 page no 819230 n bits 80 index `PRIMARY` of table `mkbase/b_stat_day` trx id 0 14584852 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 335; compact format; info bits 0
TABLE LOCK table `mkbase/b_stat_day_site` trx id 0 14584852 lock mode IX
RECORD LOCKS space id 0 page no 819233 n bits 280 index `IX_SITE_ID_DATE_STAT` of table `mkbase/b_stat_day_site` trx id 0 14584852 lock_mode X locks rec but not gap
Record lock, heap no 207 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 2; hex 7275; asc ru;; 1: len 3; hex 8fb26b; asc k;; 2: len 4; hex 800000ce; asc ;;
RECORD LOCKS space id 0 page no 1130501 n bits 72 index `PRIMARY` of table `mkbase/b_stat_day_site` trx id 0 14584852 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 336; compact format; info bits 0
TABLE LOCK table `mkbase/b_stat_guest` trx id 0 14584852 lock mode IX
10 LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS
How to repeat:
No foreign keys are using
Innodb storage engine.
tx_isolation READ-COMMITTED
show create table b_iblock_section_element
CREATE TABLE `b_iblock_section_element` (
`IBLOCK_SECTION_ID` int(11) NOT NULL,
`IBLOCK_ELEMENT_ID` int(11) NOT NULL,
`ADDITIONAL_PROPERTY_ID` int(18) default NULL,
UNIQUE KEY `ux_iblock_section_element` (`IBLOCK_SECTION_ID`,`IBLOCK_ELEMENT_ID`,`ADDITIONAL_PROPERTY_ID`),
KEY `UX_IBLOCK_SECTION_ELEMENT2` (`IBLOCK_ELEMENT_ID`,`IBLOCK_SECTION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Periodically repeatable
Suggested fix:
Unknown
