Vengala Vinay

Having 9+ Years of Experience in Software Development

Home » Step-by-Step: Diagnosing queued job processing stalls due to MySQL database lock wait times on AWS Servers

Step-by-Step: Diagnosing queued job processing stalls due to MySQL database lock wait times on AWS Servers

Identifying the Bottleneck: Queued Jobs and MySQL Lock Waits

A common symptom of stalled background job processing on AWS, particularly when using services like SQS or Redis for job queuing, is a gradual slowdown or complete halt in execution. While application-level issues or resource exhaustion on worker instances are frequent culprits, a silent killer often lurks within the database layer: MySQL lock wait contention. This post details a systematic approach to diagnosing and resolving such stalls, focusing on identifying and mitigating excessive lock wait times in your MySQL database, especially when running on AWS infrastructure.

Initial Triage: Monitoring Queued Jobs and Worker Health

Before diving deep into database internals, it’s crucial to confirm the symptoms. Are your job queues growing uncontrollably? Are worker instances idle or showing high CPU/memory usage without processing jobs? Standard monitoring tools (CloudWatch, Datadog, Prometheus) should provide visibility into:

  • Queue depth (e.g., SQS `ApproximateNumberOfMessagesVisible`)
  • Worker instance CPU/Memory utilization
  • Worker instance network I/O
  • Application logs for worker errors or timeouts

If queue depth is increasing and workers appear healthy but inactive, the database is a prime suspect. The jobs likely require database interaction, and if those interactions are blocked by locks, the jobs will stall.

Deep Dive: MySQL Performance Metrics and Lock Analysis

The key to diagnosing lock wait issues lies in examining MySQL’s internal status variables and the `performance_schema`. We’ll focus on identifying long-running transactions and the locks they hold.

1. Real-time Lock Monitoring with `SHOW ENGINE INNODB STATUS`

This command provides a wealth of information about the InnoDB storage engine, including active transactions and lock waits. Connect to your MySQL instance (e.g., an RDS instance) and execute:

SHOW ENGINE INNODB STATUS;

Pay close attention to the `TRANSACTIONS` and `LOCKS` sections. Look for:

  • `TRANSACTIONS`: Identify transactions that have been running for an unusually long time (indicated by the `Age` column). Note their `trx id` and `lock id`.
  • `LOCKS`: Observe the `Waiting threads` and `Owned locks` sections. If a thread is waiting for a lock, and the owner of that lock is a long-running transaction, you’ve found a potential deadlock or blocking scenario. The output will show which `trx id` is waiting for which `lock id`, and which `trx id` currently owns that `lock id`.

Example snippet from `SHOW ENGINE INNODB STATUS` (simplified):

---TRANSACTION--
Trx id counter 123456789
Purge done for trx's 123456780, 123456781 undo entries
...
---LOCKS--
--------------------------
LATEST DETECTED DEADLOCK
--------------------------
...
--------------------------
ROW LOCKS
--------------------------
...
--- WAITING THREAD 123 ---
...
  waited for lock:
    key: ...
    mode: X
    transaction id: 123456789 (thread 123)
--- THREAD 124 ---
...
  transaction id: 123456790 (thread 124)
  locks held:
    key: ...
    mode: X
    transaction id: 123456790 (thread 124)

2. Querying `performance_schema` for Lock Waits

The `performance_schema` provides more granular and queryable insights into lock waits. Ensure it’s enabled in your MySQL configuration (it usually is by default on modern versions). Key tables to query include:

  • `performance_schema.events_waits_current`
  • `performance_schema.events_waits_history`
  • `performance_schema.events_waits_history_long`
  • `performance_schema.data_locks`
  • `performance_schema.data_lock_waits`

To find currently blocking locks, you can use a query like this:

SELECT
    wt.REQUESTING_THREAD_ID,
    wt.REQUESTING_ENGINE_TRANSACTION_ID,
    wt.OBJECT_SCHEMA,
    wt.OBJECT_NAME,
    wt.INDEX_NAME,
    wt.LOCK_TYPE,
    wt.LOCK_MODE,
    wt.LOCK_STATUS,
    wt.LOCK_DATA,
    dl.ENGINE_TRANSACTION_ID AS BLOCKING_TRANSACTION_ID,
    dl.ENGINE_TRANSACTION_STATE AS BLOCKING_TRANSACTION_STATE,
    dl.ENGINE_TRANSACTION_START_TIME AS BLOCKING_TRANSACTION_START_TIME,
    dl.ENGINE_TRANSACTION_WAIT_INFO AS BLOCKING_TRANSACTION_WAIT_INFO,
    dl.ENGINE_TRANSACTION_ROWS_SCANNED AS BLOCKING_TRANSACTION_ROWS_SCANNED,
    dl.ENGINE_TRANSACTION_ROWS_MODIFIED AS BLOCKING_TRANSACTION_ROWS_MODIFIED,
    dl.ENGINE_TRANSACTION_IS_VALID AS BLOCKING_TRANSACTION_IS_VALID,
    dl.ENGINE_TRANSACTION_IS_READ_ONLY AS BLOCKING_TRANSACTION_IS_READ_ONLY,
    dl.ENGINE_TRANSACTION_IS_BTR_TRX AS BLOCKING_TRANSACTION_IS_BTR_TRX,
    dl.ENGINE_TRANSACTION_IS_ROLLING_BACK AS BLOCKING_TRANSACTION_IS_ROLLING_BACK,
    dl.ENGINE_TRANSACTION_IS_COMMITTING AS BLOCKING_TRANSACTION_IS_COMMITTING,
    dl.ENGINE_TRANSACTION_IS_DEADLOCK AS BLOCKING_TRANSACTION_IS_DEADLOCK,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_SIGNAL AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_SIGNAL,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_TIMEOUT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_TIMEOUT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ERROR_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_CONNECTION_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_SESSION_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_THREAD_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT_SHUTDOWN AS BLOCKING_TRANSACTION_IS_ROLLBACK_ON_KILL_STATEMENT_KILL_ABORT_SHUTDOWN,
    dl.ENGINE_TRANSACTION_IS_ROLLBACK_ON_KILL_QUERY_KILL_SHUT