MySQL Storage Engines and Deadlock Detection

Three MySQL notes focus on storage engines and deadlocks: 存储引擎.md (Storage Engine), 基础面试题.md (Basic Interview Questions), and checkmysqldeadlock.md. This page distinguishes engine capabilities from troubleshooting paths.

Core Mechanism

• Storage engines: InnoDB (row locks, MVCC, crash recovery) vs MyISAM (table locks, no transactions); sources include comparison diagrams.
• Deadlock detection: checkmysqldeadlock.md gives troubleshooting steps via SHOW ENGINE INNODB STATUS, information_schema, and performance_schema.

Applicability

• Use InnoDB for transactions and concurrent writes; consider MyISAM/Archive for read-only archives.

Not Applicable and Risks

• MySQL versions (5.7 vs 8.0) differ greatly; deadlock log formats and parameters drift.

Minimum Verification

1. Create tables with specific engines and compare locking and transactional behavior.
2. Create cross-locking deadlocks and review INNODB STATUS.

Evidence and Uncertainty

• Source facts: ingest-mysql-storage contains 3 source notes.
• Synthesis: This page converges engine selection and deadlocks into minimal checks.
• Unconfirmed: MySQL versions, parameters, and production configs need rechecking.

Related Pages

• mysql-performance-troubleshooting
• database-schema-drift
• multi-service-readiness

Sources and related documents

• ingest-mysql-storage
• MySQL Performance Triage: Symptom → Metric → Layer → Safe Mitigation
• Database Schema Drift: Locating with the history, schema, and query Views
• Multi-Service Startup Readiness: running, ready, and Dependency/Failure/Restart Propagation