MySQL Forums
Forum List  »  InnoDB

Lock Wait TimeOut Different row Update/Delete
Posted by: Sulthan Hakiem
Date: March 31, 2022 08:32AM

I've table contains field like table structure below. When Update and Delete, I always put period, branch, acc fields for where criteria. But why getting lock wait timeout when I've updated table with different criteria when my store procedure paralel running ?

I've debug my stored procedure, check on show engine status, information_schema.innodb_trx and sys.innodb_lock_waits, but not getting information why lock wait timeout always coming.


Here is table structure

`acc_ledger_monthly` (
`alm_id` varchar(32) NOT NULL,
`alm_period` int(11) DEFAULT NULL,
`alm_branch` int(11) DEFAULT NULL,
`alm_acc` int(11) DEFAULT NULL,
`alm_curr` int(11) DEFAULT NULL,
`alm_beg_balance` double DEFAULT '0',
`alm_debet` double DEFAULT '0',
`alm_kredit` double DEFAULT '0',
`alm_last_balance` double DEFAULT '0',
`created_by` varchar(10) DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_by` varchar(10) DEFAULT NULL,
`updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`alm_id`),
KEY `idx_fk_acc_ledger_monthly_1` (`alm_branch`) /*!80000 INVISIBLE */,
KEY `idx_fk_acc_ledger_monthly_2` (`alm_acc`) /*!80000 INVISIBLE */,
KEY `idx_fk_acc_ledger_monthly_3` (`alm_curr`) /*!80000 INVISIBLE */,
KEY `idx_fk_acc_ledger_monthly_4` (`alm_period`),
KEY `idx_acc_ledger_monthly_1` (`alm_period`,`alm_branch`,`alm_acc`,`alm_curr`),
CONSTRAINT `fk_acc_ledger_monthly_1` FOREIGN KEY (`alm_branch`) REFERENCES `adm_branch` (`branch_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_acc_ledger_monthly_2` FOREIGN KEY (`alm_acc`) REFERENCES `acc_account` (`acc_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_acc_ledger_monthly_3` FOREIGN KEY (`alm_curr`) REFERENCES `acc_currency` (`curr_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Options: ReplyQuote


Subject
Views
Written By
Posted
Lock Wait TimeOut Different row Update/Delete
127
March 31, 2022 08:32AM


Sorry, only registered users may post in this forum.

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.