Deadlock occured but can't find it's origin

Question

I've encountered a deadlock issue in my project recently and I'm seeking some insights into its origin.

The scenario is as follows:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: update `pre_txns_2403` set `status` = SUCCESS, `response_code` = 0, `response_msg` = SUCCESS, `ipay_error` = TXN, `ipay_desc` = Transaction Successful, `sp_transid` = 407508316340, `opr_id` = 407508316340, `res_dt` = 2024-03-15 08:00:14, `optional1` = 607264, `optional2` = 9935558558, `optional3` = CENTRAL BANK OF INDIA, `optional4` = 5044.00, `optional5` = e46944ca4251477b92842311c55f5e1e, `optional6` = , `optional7` = , `optional8` = , `optional9` = , `remarks1` = 316340, `remarks2` = , `remarks3` = , `remarks4` = YWY3OTAzYzNlM2ExZTJlOdjuLnfS78RXTDsN1fnMZcU=, `remarks5` = 3991805, `remarks6` = 431501, `remarks7` = 64a43d40-642e-4fa1-84d1-3f7034aa34c2, `remarks8` = , `remarks9` = , `remarks10` = , `remarks11` = , `remarks12` = , `remarks13` = , `remarks14` = , `remarks15` = LARAVEL where `ipay_id` = P240315080011LZNVJ)

Moreover, I want to shed light on the trigger point of the error:

 try {
            DB::connection($db . '__write')->beginTransaction();

            $transaction = DB::connection($db . '__write')->table($table)->where('ipay_id', $validatedData['ipayId']);
            $transaction->update($data);

            DB::connection($db . '__write')->commit();
        } catch (\Exception $e) {
            return \Ipay::response([
                'statusCode' => 'ISE',
                'internalCode' => $e->getMessage(),
                'internalCodeAppend' => '#UTU1',
            ]);
        }

My main confusion stems from the fact that when I replicated the entire operation in my local environment and ran the same script multiple times (e.g., 10 times in 1 second), I didn't encounter any issues at all. This discrepancy between the local and production environments leaves me puzzled as to why the deadlock occurred.

Unfortunately, the error message doesn't provide much information, and I'm struggling to pinpoint the exact cause of the deadlock. I'm unsure if it was indeed caused by the query or if there might be other factors at play.

I'm reaching out to the community to gather ideas or suggestions on how to trace and identify the root cause of this issue. If anyone has insights or suggestions on how to troubleshoot deadlock scenarios effectively or how to analyze the sequence of queries leading up to the deadlock, I would greatly appreciate your input.

Additionally, if needed, I can provide a list of the queries that my script executed sequentially for further context.

Thank you all for your help and support.

Answer 1

The deadlock may come from a table lock on related table FK. Ensure these are indexed.