Re: Foreign Key constraint issue in MultiThreading
Below is the SHOW ENGINE InnoDB STATUS output:-
=====================================
160204 4:56:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3432656 1_second, 3432493 sleeps, 340941 10_second, 23396 background, 23377 flush
srv_master_thread log flush and writes: 3440174
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5229459, signal count 1949746
Mutex spin waits 1355985, rounds 41824909, OS waits 1091493
RW-shared spins 3064146, rounds 107255237, OS waits 2851768
RW-excl spins 560892, rounds 38497499, OS waits 1183614
Spin rounds per wait: 30.84 mutex, 35.00 RW-shared, 68.64 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
160121 7:35:27 Transaction:
TRANSACTION 1779711, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 1 row lock(s)
MySQL thread id 2933808, OS thread handle 0x2b6d1006c700, query id 517841435 172.16.11.243 dbtrial update
insert into T_ENT_FTR_LM_ATTR (AttrValue, LMAttrId, AttrId, EntFtrLMId) values ('0', 990, 135, 103909),('4', 537, 43, 103909),('', 636, 91, 103909),('0', 343, 27, 103909),('1', 213, 17, 103909),(null, 546, 47, 103909),('', 421, 33, 103909),('TRUE', 200, 16, 103909),('3', 304, 24, 103909),('', 434, 34, 103909),('0', 174, 14, 103909),('0', 512, 40, 103909),('0', 148, 12, 103909),('0', 278, 22, 103909),('1', 96, 8, 103909),('4', 499, 39, 103909),('0', 44, 4, 103909),('', 740, 99, 103909),(null, 548, 48, 103909),('0', 161, 13, 103909),('', 662, 93, 103909),('365', 979, 134, 103909),('1', 525, 41,
Foreign key constraint fails for table `emsent_perftest`.`t_ent_ftr_lm_attr`:
,
CONSTRAINT `FK_T_ENT_FTR_LM_ATTR_EntFtrLMId_T_ENT_FTR_LM_EntFtrLMId` FOREIGN KEY (`EntFtrLMId`) REFERENCES `t_ent_ftr_lm` (`EntFtrLMId`) ON DELETE NO ACTION ON UPDATE NO ACTION
Trying to add in child table, in index `PRIMARY` tuple:
DATA TUPLE: 6 fields;
0: len 4; hex 800195e5; asc ;;
1: len 4; hex 80000087; asc ;;
2: len 6; hex 000001779711; asc w ;;
3: len 7; hex 870000029206d3; asc ;;
4: len 1; hex 30; asc 0;;
5: len 4; hex 800003de; asc ;;
But in parent table `emsent_perftest`.`t_ent_ftr_lm`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 4; hex 800195d1; asc ;;
1: len 6; hex 00000177863c; asc w <;;
2: len 7; hex fd00000191016b; asc k;;
3: len 4; hex 8000157b; asc {;;
4: len 4; hex 80000070; asc p;;
5: SQL NULL;
6: len 4; hex 8000048f; asc ;;
7: len 4; hex 80000005; asc ;;
8: len 1; hex 01; asc ;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
160204 4:56:08
*** (1) TRANSACTION:
TRANSACTION 1B290CC, ACTIVE 1 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 87 lock struct(s), heap size 14776, 376 row lock(s), undo log entries 182
MySQL thread id 2947612, OS thread handle 0x2b6d0f06d700, query id 559830197 172.16.11.243 dbtrial
insert into T_ENT_LINE_ITEM (activationType, cascadeDates, Cost, createDate, CreatedBy, EndDate, ENTId, fixedQuantity, isEnabled, NumberActivationBased, LicenseType, LineItemState, LineItemType, LinkdLineItemId, ModifiedBy, ModifiedDate, overrideAllowed, PRDId, quantity, remainingQuantity, StartDate, SuiteId, TxnENTId, TxnType) values (1, 0, 0, '2016-02-04 04:56:08', 'admin', '2500-12-31 00:00:00', 690, 1, 1, 0, 2, 2, 1, 706, 'admin', '2016-02-04 04:56:08', 0, 5, 1000, 1000, '2015-01-15 00:00:00', null, null, 2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1117 page no 4 n bits 496 index `IX_T_LI_LINKDLINEITEMID` of table `emsent_perftest`.`t_ent_line_item` trx id 1B290CC 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 1B29085, ACTIVE 2 sec inserting
mysql tables in use 2, locked 2
74 lock struct(s), heap size 14776, 372 row lock(s), undo log entries 182
MySQL thread id 2947498, OS thread handle 0x2b6d0fad6700, query id 559830343 172.16.11.243 dbtrial
insert into T_ENT_LINE_ITEM (activationType, cascadeDates, Cost, createDate, CreatedBy, EndDate, ENTId, fixedQuantity, isEnabled, NumberActivationBased, LicenseType, LineItemState, LineItemType, LinkdLineItemId, ModifiedBy, ModifiedDate, overrideAllowed, PRDId, quantity, remainingQuantity, StartDate, SuiteId, TxnENTId, TxnType) values (1, 0, 0, '2016-02-04 04:56:08', 'admin', '2500-12-31 00:00:00', 686, 1, 1, 0, 2, 2, 1, 708, 'admin', '2016-02-04 04:56:08', 0, 5, 1000, 1000, '2015-01-15 00:00:00', null, null, 2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1117 page no 4 n bits 496 index `IX_T_LI_LINKDLINEITEMID` of table `emsent_perftest`.`t_ent_line_item` trx id 1B29085 lock mode S
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 1117 page no 4 n bits 496 index `IX_T_LI_LINKDLINEITEMID` of table `emsent_perftest`.`t_ent_line_item` trx id 1B29085 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 (2)
------------
TRANSACTIONS
------------
Trx id counter 1B2913D
Purge done for trx's n:o < 1B29130 undo n:o < 0
History list length 2513
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1B290DB, not started
MySQL thread id 2947613, OS thread handle 0x2b6d0f6c6700, query id 559830215 172.16.11.243 dbtrial
---TRANSACTION 1B2913C, not started
MySQL thread id 2947612, OS thread handle 0x2b6d0f06d700, query id 559831074 172.16.11.243 dbtrial
---TRANSACTION 1B290B0, not started
MySQL thread id 2947611, OS thread handle 0x2b6d0f789700, query id 559828666 172.16.11.243 dbtrial
---TRANSACTION 1B290A0, not started
MySQL thread id 2947610, OS thread handle 0x2b6d0f685700, query id 559830058 172.16.11.243 dbtrial
---TRANSACTION 0, not started
MySQL thread id 2947603, OS thread handle 0x2b6c50b1b700, query id 559831079 172.16.27.56 root
SHOW ENGINE InnoDB STATUS
---TRANSACTION 0, not started
MySQL thread id 2947602, OS thread handle 0x2b6d0f1f3700, query id 559811350 172.16.27.56 root
---TRANSACTION 1B290E4, not started
MySQL thread id 2947498, OS thread handle 0x2b6d0fad6700, query id 559831001 172.16.11.243 dbtrial
---TRANSACTION 1B290C9, not started
MySQL thread id 2945738, OS thread handle 0x2b6d0f991700, query id 559828922 172.16.11.243 dbtrial
---TRANSACTION 1B290DD, not started
MySQL thread id 2945737, OS thread handle 0x2b6d0f644700, query id 559828287 172.16.11.243 dbtrial
---TRANSACTION 1B25811, not started
MySQL thread id 2945662, OS thread handle 0x2b6d103b9700, query id 559731578 172.16.13.66 dbtrial
---TRANSACTION 1B2913B, not started
MySQL thread id 2945564, OS thread handle 0x2b6d0ff27700, query id 559831069 172.16.13.66 dbtrial
---TRANSACTION 1B29128, not started
MySQL thread id 2945416, OS thread handle 0x2b6d0f2f7700, query id 559830950 172.16.13.66 dbtrial
---TRANSACTION 1B29121, not started
MySQL thread id 2944901, OS thread handle 0x2b6d153ce700, query id 559830923 172.16.13.66 dbtrial
---TRANSACTION 1B29076, not started
MySQL thread id 2944938, OS thread handle 0x2b6d15a07700, query id 559829498 172.16.11.243 dbtrial
---TRANSACTION 1B28373, not started
MySQL thread id 2944945, OS thread handle 0x2b6d0f88d700, query id 559798164 172.16.11.243 dbtrial
---TRANSACTION 1B29134, not started
MySQL thread id 2944944, OS thread handle 0x2b6d1012f700, query id 559831034 172.16.11.243 dbtrial
---TRANSACTION 1B28F57, not started
MySQL thread id 2944917, OS thread handle 0x2b6d0f2b6700, query id 559822131 172.16.13.66 dbtrial
---TRANSACTION 1B2911F, not started
MySQL thread id 2944942, OS thread handle 0x2b6d0f1b2700, query id 559830934 172.16.13.66 dbtrial
---TRANSACTION 1B29120, not started
MySQL thread id 2941033, OS thread handle 0x2b6d1670b700, query id 559830893 localhost 127.0.0.1 rdsadmin
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
2841 OS file reads, 7858360 OS file writes, 4634169 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 7.80 writes/s, 1.80 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 5 merges
merged operations:
insert 39, delete mark 2, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 5528387, node heap has 70 buffer(s)
0.48 hash searches/s, 3.24 non-hash searches/s
---
LOG
---
Log sequence number 8673069465
Log flushed up to 8673069465
Last checkpoint at 8673069465
0 pending log writes, 0 pending chkp writes
2862958 log i/o's done, 0.88 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2858876928; in additional pool allocated 0
Dictionary memory allocated 1475585
Buffer pool size 170496
Free buffers 28780
Database pages 141646
Old database pages 52267
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9592, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2777, created 233117, written 4471747
0.00 reads/s, 0.00 creates/s, 6.68 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 141646, unzip_LRU len: 0
I/O sum[688]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2958, id 47747317139200, state: sleeping
Number of rows inserted 31226285, updated 1474204, deleted 4840986, read 8057222758
0.12 inserts/s, 0.48 updates/s, 0.12 deletes/s, 5.40 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================