We have the first example of scheduler deadlock in the willd for MySQL for the new scheduler:

https://github.com/apache/airflow/pull/8550/checks?check_run_id=1258193958#step:6:1439

Would be great to track it down and fix before the prod release:

**NOTE**  For now the issue has been mitigated (according to the documentation of MySQL) by applying restarts (with exponential back-off) in #12046. This should help with beta-testing of Airlfow by the users and to see if the retries on deadlock has some potential negative effects (according to the documentation - they might have if they happen frequently enough). 

Some more analysis might be performed between now and 2.0.0 which might result in deciding that this issue does not need any more fixes than the current mitigation or some actual changes in the code that might result in preventing the deadlocks.


airflow/jobs/scheduler_job.py
"""
    Process a Python file containing Airflow DAGs.
    This includes:
    1. Execute the file and look for DAG objects in the namespace.
    2. Pickle the DAG and save it to the DB (if necessary).
    3. For each DAG, see what tasks should run and create appropriate task
    instances in the DB.
    4. Record any errors importing the file into ORM
    5. Kill (in ORM) any task instances belonging to the DAGs that haven't
    issued a heartbeat in a while.
    Returns a list of SimpleDag objects that represent the DAGs found in
    the file
    :param dag_ids: If specified, only look at these DAG ID's
    :type dag_ids: List[str]
    :param log: Logger to save the processing process
    :type log: logging.Logger
    """
    UNIT_TEST_MODE: bool = conf.getboolean('core', 'UNIT_TEST_MODE')
    def __init__(self, dag_ids: Optional[List[str]], log: logging.Logger):