Re: Foreign Key constraint issue in MultiThreading

[Rishi anjan]

Thanks James for your reply.
Now we remove Foreign key and use below trigger, but still facing the same deadlock.

CREATE DEFINER=`dbtrial`@`%` TRIGGER `emsent_demoma`.`LineItem_before_delete` BEFORE DELETE ON emsent_demoma.t_ent_line_item FOR EACH ROW
BEGIN
DECLARE C INT;

SELECT COUNT(LineItemId) INTO C FROM T_Ent_Line_Item WHERE LinkdLineItemId = OLD.LineItemId;

IF (C > 0) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Can’t Delete: LineItem has dependencies';
END IF;
END;


Below is the table Structure:-

CREATE TABLE `t_ent_line_item` (
`LineItemId` int(11) NOT NULL AUTO_INCREMENT,
`ENTId` int(11) NOT NULL,
`SuiteId` int(11) DEFAULT NULL,
`PRDId` int(11) DEFAULT NULL,
`Cost` int(11) NOT NULL,
`StartDate` datetime NOT NULL,
`EndDate` datetime NOT NULL,
`isEnabled` tinyint(1) NOT NULL DEFAULT '1',
`LineItemState` int(11) NOT NULL,
`LineItemType` int(11) NOT NULL DEFAULT '1',
`LicenseType` int(11) NOT NULL,
`quantity` int(11) NOT NULL DEFAULT '0',
`remainingQuantity` int(11) NOT NULL DEFAULT '0',
`overrideAllowed` tinyint(1) NOT NULL DEFAULT '1',
`cascadeDates` tinyint(1) NOT NULL DEFAULT '0',
`NumberActivationBased` tinyint(1) NOT NULL DEFAULT '0',
`LINKDLINEITEMID` int(11) DEFAULT NULL,
`TXNTYPE` int(11) NOT NULL DEFAULT '1',
`TXNENTID` int(11) DEFAULT NULL,
`FixedQuantity` int(11) NOT NULL DEFAULT '0',
`ActivationType` int(11) NOT NULL,
`CREATEDBY` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT 'admin',
`CREATEDATE` datetime DEFAULT NULL,
`MODIFIEDBY` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT 'admin',
`MODIFIEDDATE` datetime DEFAULT NULL,
PRIMARY KEY (`LineItemId`),
KEY `IX_T_LI_LINKDLINEITEMID` (`LINKDLINEITEMID`),
KEY `IX_T_LI_ENTId_TXNTYPE` (`ENTId`,`TXNTYPE`),
KEY `FK_T_ENT_LINE_ITEM_PRDId_T_PRD_PRDId` (`PRDId`),
KEY `FK_T_LI_T_ENT_TXNENTID` (`TXNENTID`),
KEY `FK_T_ENT_LINE_ITEM_SuiteId_T_SUITE_SuiteId` (`SuiteId`),
CONSTRAINT `FK_T_ENT_LINE_ITEM_ENTID_T_ENT_ENTID` FOREIGN KEY (`ENTId`) REFERENCES `t_ent` (`ENTId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_T_ENT_LINE_ITEM_PRDId_T_PRD_PRDId` FOREIGN KEY (`PRDId`) REFERENCES `t_prd` (`PRDId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_T_LI_T_ENT_TXNENTID` FOREIGN KEY (`TXNENTID`) REFERENCES `t_ent` (`ENTId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_T_ENT_LINE_ITEM_SuiteId_T_SUITE_SuiteId` FOREIGN KEY (`SuiteId`) REFERENCES `t_suite` (`SuiteId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=660 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;