Deadlock occured but can&#39;t get the origin of it

Level 74

Deadlocks are unfortunately quite common in the database when you use transactions.

From what I can read in your code you do it in three step.

Start transaction
Do the update
Commit the changes.

I never used transactions in Laravel, but I work with transactional databases, and we always do it in four steps.

Start the transaction
Select the record for update
Update if previous select was successful
Commit the changes.

So my guess is that someone else has taken out a lock on that particular record that you are trying to update.

1 like

Level 2

@Tray2 Thanks for your reply but as you can see my code snippet , I have done all the steps as you mentioned

Start transaction (BeginTransaction())
Do the update (Db Query)
Commit the changes. (DB::Commit())

even after this I am getting Deadlock ..

Level 74

@rohansinghrawat Since you hare handling stuff manually you should try to lock the record, which you don't do in your code.

DB::transaction(function() {
  $record = Model::where('id', 1)
      ->lockForUpdate()
       ->get();
    //You need to handle the exception thrown if the record is already locked.
   $record->field = 'New value';
   $record->save();  
});

1 like

Snapey

Level 122

does that data look like what you might write in the function?

AFAIK starting a transaction does not provide any restricted access to the data therefore should never be the cause of a deadlock. In this case your transaction does nothing because either the database was updated or it wasnt, and if it wasnt then there is nothing to rollback.

Perhaps you have a lock further out, where you get the record to be updated?

Level 2

@Snapey So basically Here transaction is doing nothing , this was my concern too but question arises what could be the exact issue here , is my record is getting locked somewhere else and code tries to update it ??? because as per my case

1. this record is created first 
2. some business logic 
3. vendor's response  
4. update the record created in first step (with whatever response ,DEADLOCK appeared here)

Could you please suggest any ideas or leads or any additional information regarding this

Snapey

Level 122

@rohansinghrawat is there any chance that you could be processing the same ipay_id from two different threads, eg if user double-clicks button

Level 2

@Snapey that's the best part here ...as I am aware of my whole process ,

the query I provided executes first then I also update the same record with ipay_id after the success of my first query result ..but it failed on first attempt ...

I could also provide my whole script queries .. step by step executing

Snapey

Level 122

@rohansinghrawat You must be locking the database somewhere......

Level 2

@Snapey just one more clarification is Table locked or particular row?? because numerous operations are working as usual

Level 74

@rohansinghrawat That depends on your code, if you do a SELECT * FROM table then the whole table, but if you do a SELECT * FROM table WHERE id = 1 then only that single row will be locked.

1 like

Level 2

@Tray2 We've implemented a master-slave concept in our database architecture, segregating read and write operations into separate databases. Specifically, we utilize DB__read for read operations and DB__write for write operations. The issue at hand pertains to operations conducted within the DB__write database.

Within the pre_txns_2403 table, our operations are streamlined into two distinct workflows: one for fetching transactions and another for creating and updating them. The specific scenario in question involves the creation of a record with an ipay_id of 'XXXXX', followed by the subsequent update of the same record with additional information, as outlined previously.

Your continued support is greatly appreciated. Thank you!