Lock wait timeout exceeded

I've previously asked this question, to explain briefly, I'm trying to execute a command in the Commands panel in Laravel Forge, and found out it has a maximum time out of 2 mins.

reply from James "The Commands panel has a time out of 2 minutes for commands to run in. It's not designed for long-running commands. You should SSH into the server and run this command manually."

To explain what this command does, this is fixing (by inserting) the lacking of income records of every ACTIVE subscription, which will be identified based on the hour difference from the last income that has been inserted. Every subscription has a max of 200 records in the incomes table, so if the script detected that a subscription has already reached the 200 income records, it will update the status to COMPLETED.

The lacking of income records

I have this script and I'm running this manually because I only run this if I saw something wrong happen or reported, earlier I restarted the server, the task which is inserting the income of a subscription every hour has failed and some are not inserted. I will run this script to fill those that are not inserted.

Error

Due to the Forge's maximum time out of 2 mins in the Commands panel, I ran this script in my SSH, suddenly I got this error.

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction (SQL: update subscriptions` set status = COMPLETED, subscriptions.updated_at = 2022-02-14 17:53:10 where (select count(*) from incomes where subscriptions.id = incomes.subscription_id and incomes.deleted_at is null) >= 200 and status != COMPLETED and subscriptions.deleted_at is null)

What are your workarounds in this kind of error?

The code from my previous question was optimized to the below code (thanks to @Nakov and apokryfos).

Subscription.php (model)

public function latestIncome()
{
    return $this->hasOne(Income::class)->latestOfMany();
}

public function income()
{
    return $this->hasMany(Income::class);
}

Income.php (model)

public function subscription()
{
    return $this->belongsTo(Subscription::class);
}

Command

namespace App\Console\Commands;

class SomeCommand extends Command
{
    protected $signature = 'command:here';

    public function handle()
    {
        Subscription::has('income', '>=', 200)
            ->where('status', '!=', 'COMPLETED')
            ->update([ 'status' => 'COMPLETED' ]);

        foreach (Subscription::with('latestIncome')->withCount('income')->where('status', 'ACTIVE')->lazy() as $subscription) {
            $count_earnings = $subscription->income_count;
            $recent_bonus = $subscription->latestIncome;

            if ($recent_bonus) {
                $hour_difference = now()->diffInHours($recent_bonus->created_at);

                if ($hour_difference > 1) {
                    $to_insert = 200 - $count_earnings;
                    $max = $hour_difference;

                    if ($hour_difference > $to_insert) {
                        $max = $to_insert;
                    }

                    // Some performance gain here
                    Income::insert(collect()->pad($max, [
                        'user_id' => $subscription->user_id,
                        'subscription_id' => $subscription->id,
                        'amount' => (100 * 0.002) * 100,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ])->all());

                    Log::info('Fix for:'.$subscription->id.' | User:'.$subscription->user_id.' | Total:'.$max);
                }
            }
        }

        // Update anything that got pushed over that threshold
        Subscription::has('income', '>=', 200)
            ->where('status', '!=', 'COMPLETED')
            ->update([ 'status' => 'COMPLETED' ]);
    }
}