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

bash

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:

1. Read references/ram-policies.md for the full permission list
2. Use ram-permission-diagnose skill to guide the user
3. Pause and wait until permissions are granted

---

Parameter Confirmation#

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

ParameterRequiredDescriptionInstanceIdYesRDS (rm-xxx) or PolarDB (pc-xxx)ProblemTimeYesLock wait time (e.g. 2026-06-08 17:02:35.894)ThreadIDYesBlocked thread IDBlockedSQLYesBlocked 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.

text

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

Step 1: Environment Check#

bash

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.

bash

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

The script automatically:

1. Checks SQL Insight config (hot data availability)
2. Queries SQL audit records with smart time ranges
3. Detects lock type (InnoDB row lock / MDL / Deadlock / FLUSH / Gap Lock etc.)
4. Identifies lock holder thread via time overlap analysis
5. 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:

1. Lock holder thread — who holds the lock and what SQL was executed
2. Blocking chain — the wait relationship (A blocks B blocks C...)
3. Lock type — Record Lock / Gap Lock / MDL Lock / Deadlock / Flush Lock etc.
4. Transaction timeline — lock holder's BEGIN → DML → (uncommitted)
5. 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 FileDescriptionreferences/ram-policies.mdRAM permission policy listreferences/related-commands.mdCLI command referencereferences/best-practices.mdDiagnosis best practices and known limitationsreferences/acceptance-criteria.mdAcceptance criteria and anti-patternsreferences/cli-installation-guide.mdCLI installation guidereferences/transaction-lifecycle.mdTransaction lifecycle rulesreferences/verification-method.mdVerification methods and checklist