As far as I understood the deadlock mechanics, a deadlock usually occurs when 2 parallel transactions are started and both lock two different tables and after they locked their first table they try to lock the other table which was already locked by the other transaction. Then the Deadlock kicks in because the lock will never finish due to both processes waiting for the other one to finish.
Now I receive lots of deadlock messages in my log, but the magic about this is: We do not use transactions (auto commit) and the SQL which receives the deadlock error does not include more than one table, can you explain why this error occurs?
Error Message:
06:35:06 | 1213 : Deadlock found when trying to get lock; try restarting transaction
SQL Statement:
UPDATE request_table
SET report_id ='',
change_date ='2022-09-14 06:35:03'
WHERE customer_id_1 = '283649'
AND customer_id_2 = '2893463'
AND report_request_id =''
AND report_type ='vat'
Update: As requested I added the table schema - I hope it shows, that there are no triggers or other automatic processes going on at the table
--
-- structure for table `request_table`
--
CREATE TABLE `request_table` (
`id` int(11) NOT NULL,
`customer_id_1` int(11) NOT NULL,
`customer_id_2` int(11) NOT NULL,
`change_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`report_type` varchar(255) COLLATE latin1_german1_ci NOT NULL,
`report_request_id` varchar(255) COLLATE latin1_german1_ci DEFAULT NULL,
`start_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`end_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`submitted_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`report_processing_status` varchar(255) COLLATE latin1_german1_ci NOT NULL,
`report_id` varchar(255) COLLATE latin1_german1_ci DEFAULT NULL,
`processing_message` varchar(255) COLLATE latin1_german1_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PACK_KEYS=0;
--
-- Indices for table `request_table`
--
ALTER TABLE `request_table`
ADD PRIMARY KEY (`id`),
ADD KEY `customer_id_1` (`customer_id_2`),
ADD KEY `customer_id_2` (`portal_account_id`),
ADD KEY `report_request_id` (`report_request_id`),
ADD KEY `report_type` (`report_type`),
ADD KEY `report_processing_status` (`report_processing_status`),
ADD KEY `submitted_date` (`submitted_date`);
--
-- AUTO_INCREMENT for table `request_table`
--
ALTER TABLE `request_table`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38030718;
It is a simple table and a simple UPDATE statement. Best I can offer is a parallel process Updating the same row at the same time, but that shouldn't result in deadlock?! I can nearly guarantee, that any other SQL statements are similarly simple because the whole server does not use overly complex statements to prevent behaviour like this.