2

I am developing a bidding application in laravel. In this, I have to run cronjob after each minute and user can also bid from front end. So to avoid collision I am using lockForUpdate() of laravel. I have placed this in two different functions where bid ids are being processed. And In one place I am using it three times and at other its is being used one time. I have placed my code inside DB::transaction. But due to some reason I am getting deadlock error whenever same raw is processed by two users at same time.

I am using this as given below:

At One Place

DB::beginTransaction();
try
{
     -----Some Code ----

     SecondGameBids::where('id', $big)->lockForUpdate()->get();   
     SecondGameBids::where('id', $big)->update(['final_value' =>0, 'deal_status' => 1]);   

     -----Some Code ------

     SecondGameBids::where('id', $small)->lockForUpdate()->get();   
     SecondGameBids::where('id', $small)->update(['final_value' =>0, 'deal_status' => 1]); 

    ------Some Code ------

     DB::commit();
}
catch (\Exception $e) {

     DB::rollback();
}

At Other Place

 DB::beginTransaction();
    try
    {
         -----Some Code ----

         SecondGameBids::where('id', $big)->lockForUpdate()->get();   
         SecondGameBids::where('id', $big)->update(['status' => 3]);   

         ------Some Code ------
         DB::commit();
    }
    catch (\Exception $e) {

         DB::rollback();
    }

Can anyone give me some idea about how to overcome this error?

Parth Pandya
  • 1,050
  • 7
  • 15
  • 22
Gaganpreet Kaur
  • 371
  • 1
  • 3
  • 15
  • Related: [Laravel lockforupdate (Pessimistic Locking)](https://stackoverflow.com/q/34556511/2298301). Maybe you should be trying `sharedLock()` method? What version of Laravel is it? From what the [documentation](https://laravel.com/docs/5.5/queries#pessimistic-locking) suggests, `lockForUpdate()` will prevent the locked rows from even being selected. – Dhruv Saxena May 25 '18 at 07:25
  • if you run the first function twice and the 2nd time swap `$big` for `$small` then you might have a deadlock (if there's some bad timing). Other than that I'm having trouble seeing where the deadlock could happen – apokryfos May 25 '18 at 07:57
  • @DhruvSaxena I am using laravel 5.4. And I have also tried sharedLock() but still getting deadlock error. – Gaganpreet Kaur May 25 '18 at 07:57
  • @apokryfos no I am using first function only one time but using lockforupdate() three times in this function – Gaganpreet Kaur May 25 '18 at 07:59
  • One of the causes of the [dining philosophers problem](https://en.wikipedia.org/wiki/Dining_philosophers_problem) is that resources are not pre-allocated. You might have some success of solving this problem by preemptively locking all rows you need to update as soon as the transaction starts. – apokryfos May 25 '18 at 08:00
  • @apokryfos My functionality includes a number of conditions and I cannot lock all the rows at start because I get row ids on the basis of internal conditions and logic. – Gaganpreet Kaur May 25 '18 at 08:03
  • Well there's other solutions to the problem such as finding what part of your code causes the cycle of dependencies to occur and switching the order around, but your current example code is really not a complete verifiable example on where the problem actually is. – apokryfos May 25 '18 at 08:04
  • @GaganpreetKaur Since you're using a CRON job anyway, is it possible to maybe [queue](https://laravel.com/docs/5.4/queues) up every update? The question then translates into the following: _are concurrent updates really required_? The idea is, instead of you receiving concurrent update requests that result in a deadlock, you could possibly have sequential updates that will first be stored in a queue but process it only after the previous update task has run. That is, both CRON and front end will add records in a queue, and the queue supervisor can process them all sequentially. – Dhruv Saxena May 25 '18 at 08:19
  • @DhruvSaxena I have commented all lines having lockForUpdate() but I am still getting deadlock error. Is it possible to get deadlock error even without locking? – Gaganpreet Kaur May 25 '18 at 12:40
  • @GaganpreetKaur It's unusual, but possible. Maybe two or more CRON Jobs and frontend requests land up on the same `id`? If the volume of test data is fairly limited, chances of transactions overlapping with each other would increase dramatically every minute (a new job is run every minute)? If your DB Engine happens to be INNODB, please have a read at the following: [InnoDB Deadlocks](https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html), [Isolation Levels](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html) and https://stackoverflow.com/q/917640/2298301 – Dhruv Saxena May 25 '18 at 14:02
  • @GaganpreetKaur Just wonder if the problem was resolved? – Dhruv Saxena Jun 04 '18 at 18:41

0 Answers0