MySQL Forums
Forum List  »  InnoDB

Dead Lock
Posted by: jongse park
Date: January 13, 2016 02:53AM

Hi

I have a error message for dead lock;
----------------------------------------------------------------------
*** (1) TRANSACTION:
TRANSACTION 2044417, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 39 lock struct(s), heap size 6544, 77 row lock(s), undo log entries 390
MySQL thread id 20756, OS thread handle 0x7f05f7f5a700, query id 1790559 172.21.65.107 ccaapp update
INSERT INTO `XXX_TABLE`(STND_YMD, EPID, USER_ID, USER_FNM, COMP_CD, SUBORG_CD, DEPT_CD, JOBPO_CD, SECU_GRD_CD, MYSINGLE_SEND_NUM, MYSINGLE_SEND_KB_SZ, XTNL_SEND_NUM, XTNL_SEND_KB_SZ, MYSINGLE_RCV_NUM, MYSINGLE_RCV_KB_SZ, XTNL_RCV_NUM, XTNL_RCV_KB_SZ) VALUES('20151129', 'M150303234910C607864', ......),('20151129', 'S020305135820C408369', ......),('20151129', 'S020305135820C408369',......),('20151129', 'M100713025316PCL7963',......),('20151129', 'S020126110932A200355', ...... ?
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 15484 n bits 120 index `PRIMARY` of table `CCADB`.`CCA_TML_USER_DAILY_AGRG` trx table locks 1 total table locks 4 trx id 2044417 lock_mode X insert intention waiting lock hold time 0 wait time before grant 0
*** (2) TRANSACTION:
TRANSACTION 2044415, ACTIVE 1 sec inserting, thread declared inside InnoDB 4933
mysql tables in use 1, locked 1
63 lock struct(s), heap size 13864, 559 row lock(s), undo log entries 2851
MySQL thread id 20754, OS thread handle 0x7f05cabab700, query id 1790583 172.21.65.107 ccaapp update
INSERT INTO `XXX_TABLE`(STND_YMD, EPID, USER_ID, USER_FNM, COMP_CD, SUBORG_CD, DEPT_CD, JOBPO_CD, SECU_GRD_CD, MYSINGLE_SEND_NUM, MYSINGLE_SEND_KB_SZ, XTNL_SEND_NUM, XTNL_SEND_KB_SZ, MYSINGLE_RCV_NUM, MYSINGLE_RCV_KB_SZ, XTNL_RCV_NUM, XTNL_RCV_KB_SZ) VALUES('20151129', 'S020305135845C602479',......),('20151129', 'M101125214300C605005', ......),('20151129', 'S020305135824C608030',......),('20151129', 'D050114094454C600539', ......),('20151129', 'S020305135824C608876', ......
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 89 page no 15484 n bits 120 index `PRIMARY` of table `CCADB`.`CCA_TML_USER_DAILY_AGRG` trx table locks 1 total table locks 4 trx id 2044415 lock_mode X lock hold time 0 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 13609 n bits 120 index `PRIMARY` of table `CCADB`.`CCA_TML_USER_DAILY_AGRG` trx table locks 1 total table locks 4 trx id 2044415 lock_mode X insert intention waiting lock hold time 0 wait time before grant 0
*** WE ROLL BACK TRANSACTION (1)
2015-11-29 22:02:36 7f0e308f1700InnoDB: transactions deadlock detected, dumping detailed information.
2015-11-29 22:02:36 7f0e308f1700
--------------------------------------------------------------------------

The Primary key of XXX_TABLE is STND_YMD and EPID column.
space id 89 page no 15484 and space id 89 page no 13609 is same space ?
I think that space id is same but those page no are different each other.
Why do they make a dead lock during inserting data ?
I want to know the reason and How to fix this problem.

Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Dead Lock
1694
January 13, 2016 02:53AM
752
January 14, 2016 05:40PM
694
January 15, 2016 01:49AM


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.