【MySQL・Laravel】意図されないインデックスが使用され、update時にロックがかかってしまった時の対応

        Schema::create('items', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('owner_id')->default(0);
            $table->string('code', 80);
            $table->string('name', 100);
            $table->string('sub_name', 50)->nullable();
            $table->integer('price')->default(0);
            $table->integer('category_id')->nullable()->unsigned();
            $table->integer('tax_rate_type')->default(0);
            $table->timestamps();
            $table->softDeletes();
        });

        DB::statement("CREATE INDEX items_index_1 on items (owner_id, deleted_at)");
        DB::statement("CREATE INDEX items_index_2 on items (owner_id, category_id, deleted_at)");
        DB::statement("CREATE INDEX items_index_3 on items (owner_id, category_id, tax_rate_type, deleted_at)");
    }

CREATE TABLE `items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `owner_id` bigint(20) NOT NULL DEFAULT '0',
  `code` varchar(80) COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_bin NOT NULL,
  `sub_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `price` int(11) NOT NULL DEFAULT '0',
  `category_id` int(10) unsigned DEFAULT NULL,
  `tax_rate_type` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `existence` tinyint(1) GENERATED ALWAYS AS ((case when isnull(`deleted_at`) then 1 else NULL end)) STORED,
  PRIMARY KEY (`id`),
  KEY `items_index_1` (`owner_id`,`deleted_at`),
  KEY `items_index_2` (`owner_id`,`category_id`,`deleted_at`),
  KEY `items_index_3` (`owner_id`,`category_id`,`tax_rate_type`,`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

explain
select
 *
from
 items
where  1=1
  and  deleted_at is null
  and  owner_id = 1

explain
select
 *
from
 items
where  1=1
  and  deleted_at is null
  and  owner_id      = 1
  and  category_id   = 2

explain
select
 *
from
 items
where  1=1
  and  deleted_at is null
  and  owner_id      = 1
  and  category_id   = 2
  and  tax_rate_type = 0

explain
update  items
   set  price = 100
where  1=1
  and  deleted_at is null
  and  owner_id      = 1
  and  category_id   = 2
  and  tax_rate_type = 0

Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL:  update items ...

update  items
   set  price = 100
where  1=1
  and  deleted_at is null
  and  owner_id      = 1
  and  category_id   = 2
  and  tax_rate_type = 0

where  1=1
  and  deleted_at is null
  and  owner_id = 1

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [, index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] .

update  items force INDEX (items_index_3)
   set  price = 100
where  1=1
  and  deleted_at is null
  and  owner_id      = 1
  and  category_id   = 2
  and  tax_rate_type = 0

explain
update  items force INDEX (items_index_3)
   set  price = 100
where  1=1
  and  deleted_at is null
  and  owner_id      = 1
  and  category_id   = 2
  and  tax_rate_type = 0

delete from items USING items FORCE INDEX ( items_index_1 ) 
 where owner_id = 1

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'

SET innodb_lock_wait_timeout=300

$sql = <<<SQL
             update  items force INDEX (items_index_3)
                set  price = 100
              where  1=1
                and  deleted_at is null
                and  owner_id      = :owner_id
                and  category_id   = :category_id
                and  tax_rate_type = :tax_rate_type
SQL;

$params['owner_id']      = 1;
$params['category_id']   = 2;
$params['tax_rate_type'] = 0;

$result = DB::select($sql, $params);

    public function pruebaMethod01()
    {
        $ownerId = 1;

        $this->model = app()->make(Item::class);

        return $this->model
            ->from(DB::raw('items USING items FORCE INDEX ( items_index_1 )'))
            ->withTrashed()
            ->where('owner_id', $ownerId)
            ->forceDelete();
    }