Lock Wait TimeOut Different row Update/Delete

[Sulthan Hakiem]

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;