Yes, it is possible to retry the DB transaction if it fails. One way to do this is to wrap the transaction code in a loop and use a counter to limit the number of retries. Here's an example:
$maxRetries = 3;
$retryCount = 0;
while ($retryCount < $maxRetries) {
try {
DB::beginTransaction();
DB::table('table1')->whereIn('my_column', $values)->lockForUpdate()->get();
$existingValues = DB::table('table2')->whereIn('my_column', $values)->pluck('my_column')->toArray();
if (!empty($existingValues)) {
throw new \Exception('Some rows are in use');
}
DB::commit();
break; // exit the loop if the transaction is successful
} catch (\Exception $e) {
DB::rollback();
$retryCount++;
if ($retryCount >= $maxRetries) {
throw $e; // rethrow the exception if the maximum number of retries is reached
}
sleep(1); // wait for a second before retrying
}
}
In this example, the code tries to execute the transaction up to three times. If the transaction fails due to a row being in use, it rolls back the transaction and waits for a second before retrying. If the maximum number of retries is reached, it rethrows the exception.
That’s assuming you actually want failed retries to throw an exception at all – you can also just handle it gracefully right there in the if block if you want some specific logic to be executed when the transaction fails X number of times. You won’t have to re-throw the exception then.
@Snapey In reality there is another extra code on top that I must refactor but right now it selects some rows that can be part of the $values without locking the table so there can be a situation where 2 users get to the lockForUpdate with the same values, so I think what it can do is to let the other one finish, then redo the query with new $values that are different and don't exist.
Of course after I modify the code soon, the first query that selects the data will lock the table already so I won't need it
