Alibaba Cloud Historical Lock Wait Diagnosis

Diagnose historical lock wait issues on PolarDB MySQL and RDS MySQL instances. Identifies the root cause, complete lock chain, and lock holder.

Architecture: PolarDB/RDS MySQL + DAS (Database Autonomy Service) + Aliyun CLI

Supported lock types: InnoDB row locks (Record/Gap/Next-Key), MDL (Metadata Lock), Table Lock, Deadlock, FLUSH cascading, AUTO-INC Lock

Installation

Pre-check: Aliyun CLI >= 3.3.3 required

Run aliyun version to verify >= 3.3.3. If not installed or version too low, run curl -fsSL https://aliyuncli.alicdn.com/setup.sh | bash to install/update, or see references/cli-installation-guide.md for installation instructions.

Pre-check: Aliyun CLI plugin update required

[MUST] run aliyun configure set --auto-plugin-install true to enable automatic plugin installation. [MUST] run aliyun plugin update to ensure that any existing plugins are always up-to-date.

[MUST] CLI User-Agent — Every aliyun CLI command that calls a cloud API must include: --user-agent AlibabaCloud-Agent-Skills/alibabacloud-history-lock-diagnose/{session-id} (See Observability section below for session-id generation)

Runtime Requirements:

Python >= 3.7 (standard library only)
Aliyun CLI >= 3.3.3

Authentication

Pre-check: Alibaba Cloud Credentials Required

Security Rules:

NEVER read, echo, or print AK/SK values

NEVER ask the user to input AK/SK directly

ONLY use aliyun configure list to check credential status
aliyun configure list
Check the output for a valid profile (AK, STS, or OAuth identity).

If no valid profile exists, STOP here.

RAM Policy

Required permissions: hdm:GetDasSQLLogHotData, hdm:GetDeadLockHistory, hdm:GetDeadLockDetail, hdm:CreateLatestDeadLockAnalysis, hdm:DescribeSqlLogConfig, hdm:GetMySQLAllSessionAsync

[MUST] Permission Failure Handling: When any command or API call fails due to permission errors:

Read references/ram-policies.md for the full permission list

Use ram-permission-diagnose skill to guide the user

Pause and wait until permissions are granted

Parameter Confirmation

IMPORTANT: ALL parameters MUST be confirmed with the user before execution.

| Parameter | Required | Description | |-----------|----------|-------------| | InstanceId | Yes | RDS (rm-xxx) or PolarDB (pc-xxx) | | ProblemTime | Yes | Lock wait time (e.g. 2026-06-08 17:02:35.894) | | ThreadID | Yes | Blocked thread ID | | BlockedSQL | Yes | Blocked SQL statement |

Core Workflow

Observability (MUST follow for every aliyun command)

Upon loading this skill, generate a random session ID (32-char lowercase hex string) once for the entire session. Use it as {session-id} below.

Rule: Every aliyun CLI command that calls a cloud API MUST include --user-agent. Local commands (configure, plugin, version) do not support this flag.

--user-agent AlibabaCloud-Agent-Skills/alibabacloud-history-lock-diagnose/{session-id}

Step 1: Environment Check

aliyun version
aliyun configure list

Step 2: Run Diagnosis Script

[MUST] Execute the script directly with the command below. Do NOT read scripts/smart-lock-diagnosis.py — it is 3700+ lines and reading it will waste context and slow down execution. The script handles everything internally: SQL audit query, lock type detection, blocking chain analysis, deadlock diagnosis, and report generation.

SKILL_SESSION_ID={session-id} python3 scripts/smart-lock-diagnosis.py \
  --instance-id {InstanceId} \
  --time "{ProblemTime}" \
  --thread-id {ThreadID} \
  --sql "{BlockedSQL}"

The script automatically:

Checks SQL Insight config (hot data availability)
Queries SQL audit records with smart time ranges
Detects lock type (InnoDB row lock / MDL / Deadlock / FLUSH / Gap Lock etc.)
Identifies lock holder thread via time overlap analysis
Outputs structured diagnosis report with blocking chain

Step 3: Present Diagnosis Report

Extract the key findings from the script output and present to the user:

Lock holder thread — who holds the lock and what SQL was executed
Blocking chain — the wait relationship (A blocks B blocks C...)
Lock type — Record Lock / Gap Lock / MDL Lock / Deadlock / Flush Lock etc.
Transaction timeline — lock holder's BEGIN → DML → (uncommitted)
Recommendations — based on actual SQL content (shorten transactions, add indexes, etc.)

Key requirements:

Do NOT paste the full raw script output — extract and summarize key information only
Timestamps must include milliseconds (e.g., 10:39:28.390)
Recommendations must be based strictly on actual SQL content observed

Notes

The instance must have SQL Insight (SQL Audit) enabled
Hot data: GetDasSQLLogHotData retention depends on instance config (check HotRetention field). Data outside retention window may be unavailable
Deadlock analysis (GetDeadLockHistory) can query historical records beyond hot data window
This skill does not modify any user data or instance configurations. CreateLatestDeadLockAnalysis triggers DAS server-side deadlock parsing (classified as Write) but does not alter the database

Cleanup

This is a diagnostic skill. No user resources are created or modified; no cleanup required.

Reference Links

| Reference File | Description | |---------------|-------------| | references/ram-policies.md | RAM permission policy list | | references/related-commands.md | CLI command reference | | references/best-practices.md | Diagnosis best practices and known limitations | | references/acceptance-criteria.md | Acceptance criteria and anti-patterns | | references/cli-installation-guide.md | CLI installation guide | | references/transaction-lifecycle.md | Transaction lifecycle rules | | references/verification-method.md | Verification methods and checklist |