| name | alibabacloud-history-lock-diagnose |
| description | Alibaba Cloud PolarDB/RDS MySQL historical lock wait root cause analysis skill.
Diagnoses and analyzes historical lock wait issues, identifies the complete lock chain and lock holder.
Triggers: lock diagnosis, lock wait troubleshooting, deadlock analysis, PolarDB lock issue, RDS lock issue,
lock wait timeout, transaction blocking, metadata lock wait, InnoDB lock analysis, database lock troubleshooting,
MySQL lock wait, database performance troubleshooting, lock conflict analysis.
Use when the user mentions database lock, deadlock, lock wait, transaction blocking, or slow queries related to locks.
|
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
