MySQL Forums
Forum List  »  InnoDB

Re: DeadLock issue in MultiThreading due to foreign key constraint.
Posted by: Rishi anjan
Date: March 29, 2016 11:13PM

Thanks Peter.

Below are the sql queries for your refrence executed by hibernate on single thread:-

Hibernate: insert into Entitlement (Eid, Descr, Qty, RemainingQty) values (?, ?, ?, ?)
Hibernate: insert into LineItem (AttrId, AttrValue, EntId, LinkedLineItemId) values (?, ?, ?, ?)
Hibernate: insert into LineItem (AttrId, AttrValue, EntId, LinkedLineItemId) values (?, ?, ?, ?)
Hibernate: select entitlemen0_.EntId as EntId1_1_, entitlemen0_.Eid as Eid2_1_, entitlemen0_.Descr as Descr3_1_, entitlemen0_.Qty as Qty4_1_, entitlemen0_.RemainingQty as Remainin5_1_ from Entitlement entitlemen0_ where entitlemen0_.Eid=?
Hibernate: select lineitem0_.EntId as EntId4_1_2_, lineitem0_.LineItemId as LineItem1_2_2_, lineitem0_.LineItemId as LineItem1_2_1_, lineitem0_.AttrId as AttrId2_2_1_, lineitem0_.AttrValue as AttrValu3_2_1_, lineitem0_.EntId as EntId4_2_1_, lineitem0_.LinkedLineItemId as LinkedLi5_2_1_, lineitem1_.LineItemId as LineItem1_2_0_, lineitem1_.AttrId as AttrId2_2_0_, lineitem1_.AttrValue as AttrValu3_2_0_, lineitem1_.EntId as EntId4_2_0_, lineitem1_.LinkedLineItemId as LinkedLi5_2_0_ from LineItem lineitem0_ left outer join LineItem lineitem1_ on lineitem0_.LinkedLineItemId=lineitem1_.LineItemId where lineitem0_.EntId=?
Hibernate: delete from LineItem where LineItemId=?
Hibernate: insert into Entitlement (Eid, Descr, Qty, RemainingQty) values (?, ?, ?, ?)
Hibernate: insert into LineItem (AttrId, AttrValue, EntId, LinkedLineItemId) values (?, ?, ?, ?)
Hibernate: insert into LineItem (AttrId, AttrValue, EntId, LinkedLineItemId) values (?, ?, ?, ?)
Hibernate: insert into LineItem (AttrId, AttrValue, EntId, LinkedLineItemId) values (?, ?, ?, ?)
Hibernate: update Entitlement set Eid=?, Descr=?, Qty=?, RemainingQty=? where EntId=?


Below are the result of "Show Engine InnoDB Status" for your refrence on 40 thread when deadlock occured:-

=====================================
160330 5:12:40 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 440666 1_second, 440644 sleeps, 40075 10_second, 40386 background, 40386 flush
srv_master_thread log flush and writes: 441475
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 47386, signal count 46960
Mutex spin waits 7001, rounds 210510, OS waits 6506
RW-shared spins 40606, rounds 1218431, OS waits 40594
RW-excl spins 82, rounds 8503, OS waits 271
Spin rounds per wait: 30.07 mutex, 30.01 RW-shared, 103.70 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
160330 5:12:00
*** (1) TRANSACTION:
TRANSACTION 8F4DB, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 5
MySQL thread id 281, OS thread handle 0x7f0f65134700, query id 3644499 10.164.27.14 dbtrial update
insert into LineItem (AttrId, AttrValue, EntId, LinkedLineItemId) values (2, 'attributeVal', 976, 2646)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1490 n bits 736 index `fk_line_item` of table `emsent_demoma`.`lineitem` trx id 8F4DB 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 8F4DD, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1248, 5 row lock(s), undo log entries 4
MySQL thread id 274, OS thread handle 0x7f0f782b0700, query id 3644508 10.164.27.14 dbtrial update
insert into LineItem (AttrId, AttrValue, EntId, LinkedLineItemId) values (2, 'attributeVal', 978, 2650)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1490 n bits 736 index `fk_line_item` of table `emsent_demoma`.`lineitem` trx id 8F4DD 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 0 page no 1490 n bits 736 index `fk_line_item` of table `emsent_demoma`.`lineitem` trx id 8F4DD 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 8F4F9
Purge done for trx's n:o < 8F4F1 undo n:o < 0
History list length 2388
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 8F306, not started
MySQL thread id 272, OS thread handle 0x7f0f783d6700, query id 3640182 10.164.27.14 dbtrial
---TRANSACTION 8F4DA, not started
MySQL thread id 273, OS thread handle 0x7f0f651f8700, query id 3644469 10.164.27.14 dbtrial
---TRANSACTION 8F4DD, not started
MySQL thread id 274, OS thread handle 0x7f0f782b0700, query id 3644516 10.164.27.14 dbtrial
---TRANSACTION 8F312, not started
MySQL thread id 276, OS thread handle 0x7f0f782e1700, query id 3640221 10.164.27.14 dbtrial
---TRANSACTION 8F313, not started
MySQL thread id 275, OS thread handle 0x7f0f64f7b700, query id 3640231 10.164.27.14 dbtrial
---TRANSACTION 8F4D6, not started
MySQL thread id 277, OS thread handle 0x7f0f7818a700, query id 3644466 10.164.27.14 dbtrial
---TRANSACTION 8F4D7, not started
MySQL thread id 278, OS thread handle 0x7f0f650a1700, query id 3644467 10.164.27.14 dbtrial
---TRANSACTION 8F492, not started
MySQL thread id 279, OS thread handle 0x7f0f65196700, query id 3643872 10.164.27.14 dbtrial
---TRANSACTION 8F4DC, not started
MySQL thread id 280, OS thread handle 0x7f0f6503f700, query id 3644505 10.164.27.14 dbtrial
---TRANSACTION 8F4DB, not started
MySQL thread id 281, OS thread handle 0x7f0f65134700, query id 3644514 10.164.27.14 dbtrial
---TRANSACTION 8F304, not started
MySQL thread id 282, OS thread handle 0x7f0f7849a700, query id 3640184 10.164.27.14 dbtrial
---TRANSACTION 8F310, not started
MySQL thread id 265, OS thread handle 0x7f0f6500e700, query id 3640217 10.164.27.14 dbtrial
---TRANSACTION 8F307, not started
MySQL thread id 284, OS thread handle 0x7f0f64fac700, query id 3640174 10.164.27.14 dbtrial
---TRANSACTION 0, not started
MySQL thread id 286, OS thread handle 0x7f0f78374700, query id 3644740 10.164.27.14 dbtrial
Show Engine InnoDB Status
---TRANSACTION 0, not started
MySQL thread id 285, OS thread handle 0x7f0f78312700, query id 3640516 10.164.27.14 dbtrial
---TRANSACTION 8F292, not started
MySQL thread id 283, OS thread handle 0x7f0f65070700, query id 3639210 10.164.27.14 dbtrial
---TRANSACTION 8F4F5, not started
MySQL thread id 263, OS thread handle 0x7f0f78407700, query id 3644730 10.164.28.110 dbtrial
---TRANSACTION 8E65A, not started
MySQL thread id 264, OS thread handle 0x7f0f78469700, query id 3609598 10.164.28.110 dbtrial
---TRANSACTION 8F4ED, not started
MySQL thread id 260, OS thread handle 0x7f0f78438700, query id 3644686 10.164.28.110 dbtrial
---TRANSACTION 8CD53, not started
MySQL thread id 259, OS thread handle 0x7f0f6525a700, query id 3551359 10.164.28.110 dbtrial
---TRANSACTION 8CD35, not started
MySQL thread id 257, OS thread handle 0x7f0f652bc700, query id 3609617 10.164.28.110 dbtrial
---TRANSACTION 8CD71, not started
MySQL thread id 258, OS thread handle 0x7f0f65165700, query id 3551356 10.164.28.110 dbtrial
---TRANSACTION 8CD77, not started
MySQL thread id 256, OS thread handle 0x7f0f7824e700, query id 3551372 10.164.28.110 dbtrial
---TRANSACTION 8B1E4, not started
MySQL thread id 253, OS thread handle 0x7f0f78056700, query id 3609620 10.164.28.110 dbtrial
---TRANSACTION 8F27C, not started
MySQL thread id 134, OS thread handle 0x7f0f78159700, query id 3638903 10.164.28.110 dbtrial
---TRANSACTION 8F4F7, not started
MySQL thread id 108, OS thread handle 0x7f0f784cb700, query id 3644735 10.164.28.110 dbtrial
---TRANSACTION 8F465, not started
MySQL thread id 110, OS thread handle 0x7f0f783a5700, query id 3643361 10.164.28.110 dbtrial
---TRANSACTION 8F462, not started
MySQL thread id 109, OS thread handle 0x7f0f781bb700, query id 3643356 10.164.28.110 dbtrial
--------
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
1095 OS file reads, 859409 OS file writes, 395295 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.20 writes/s, 0.60 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 5 buffer(s)
0.13 hash searches/s, 1.47 non-hash searches/s
---
LOG
---
Log sequence number 1243754518
Log flushed up to 1243754518
Last checkpoint at 1243751139
0 pending log writes, 0 pending chkp writes
234109 log i/o's done, 0.33 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 927817
Buffer pool size 8192
Free buffers 6494
Database pages 1693
Old database pages 604
Modified db pages 10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1084, created 609, written 544674
0.00 reads/s, 0.00 creates/s, 0.73 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: 1693, unzip_LRU len: 0
I/O sum[0]: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. 25741, id 139704107198208, state: sleeping
Number of rows inserted 53486, updated 203472, deleted 50984, read 1050861
0.07 inserts/s, 0.27 updates/s, 0.07 deletes/s, 1.20 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.