### Apache Airflow version

2.7.0

Also reproduced on 2.5.0

### What happened

Recently we added some automation to restarting Airflow tasks with "clear" command so we use this feature a lot. We often clear tasks in RUNNING state, which means that they go into RESTARTING state. We noticed that a lot of those tasks get stuck in RESTARTING state. Our Airflow infrastructure runs in an environment where any process can get suddenly killed without graceful shutdown.

We run Airflow on GKE but I managed to reproduce this behaviour on local environment with SequentialExecutor. See **"How to reproduce"** below for details.

### What you think should happen instead

Tasks should get cleaned after scheduler restart and eventually get scheduled and executed.

### How to reproduce

After some code investigation, I reproduced this kind of behaviour on local environment and it seems that RESTARTING tasks are only properly handled if the original restarting task is gracefully shut down so it can mark task as UP_FOR_RETRY or at least there is a healthy scheduler to do it if they fail for any other reason. The problem is with the following scenario:

1. Task is initially in RUNNING state.
2. Scheduler process dies suddenly.
3. The task process also dies suddenly.
4. "clear" command is executed on the task so the state is changed to RESTARTING state by webserver process.
5. From now on, even if we restart scheduler, the task will never get scheduled or change its state. It needs to have its state manually fixed, e.g. by clearing it again.

A recording of steps to reproduce on local environment:
https://vimeo.com/857192666?share=copy

### Operating System

MacOS Ventura 13.4.1

### Versions of Apache Airflow Providers

N/A

### Deployment

Official Apache Airflow Helm Chart

### Deployment details

N/A

### Anything else

_No response_

### Are you willing to submit PR?

- [X] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)

"""
        Enqueues TaskInstances for execution.
        There are three steps:
        1. Pick TIs by priority with the constraint that they are in the expected states
        and that we do not exceed max_active_runs or pool limits.
        2. Change the state for the TIs above atomically.
        3. Enqueue the TIs in the executor.
        HA note: This function is a "critical section" meaning that only a single executor process can execute
        this function at the same time. This is achieved by doing ``SELECT ... from pool FOR UPDATE``. For DBs
        that support NOWAIT, a "blocked" scheduler will skip this and continue on with other tasks (creating
        new DAG runs, progressing TIs from None to SCHEDULED etc.); DBs that don't support this (such as
        MariaDB or MySQL 5.x) the other schedulers will wait for the lock before continuing.
        :param session:
        :return: Number of task instance with state changed.
        """
        if self.job.max_tis_per_query == 0:
            max_tis = self.job.executor.slots_available
        else:
            max_tis = min(self.job.max_tis_per_query, self.job.executor.slots_available)
        queued_tis = self._executable_task_instances_to_queued(max_tis, session=session)
        self._enqueue_task_instances_with_queued_state(queued_tis, session=session)
        return len(queued_tis)
    def _process_executor_events(self, session: Session) -> int: