0 views collected around this technical thread.
This article examines how MySQL 8.0.32 InnoDB handles locking when inserting a row that violates a unique index under the READ‑COMMITTED isolation level, detailing the preparation steps, observed lock types, the underlying mechanism, and the final cleanup actions.
This article examines how InnoDB handles row locking when an INSERT triggers a primary‑key index conflict, detailing the lock types on primary and unique indexes, the implicit‑to‑explicit lock conversion, and the role of the supremum record in the locking process.
This article explains the mechanisms behind MySQL auto‑increment primary keys, why gaps appear after failed inserts, unique‑index violations, transaction rollbacks, and batch operations, and how storage engines and innodb_autoinc_lock_mode settings influence the continuity of generated IDs.
This article reproduces a MySQL 8.0.32 InnoDB scenario where inserting a row that violates a unique index causes a next‑key exclusive lock on the primary‑key supremum record, explains the underlying implicit‑lock mechanism, walks through the relevant source‑code stack, and details the lock‑conversion and rollback steps under REPEATABLE‑READ isolation.
This article explains how to add unique indexes to large MySQL tables using Online DDL, pt‑osc, and gh‑ost, compares their advantages and drawbacks, outlines common data‑loss risks, and provides practical hook scripts and best‑practice recommendations to mitigate those risks.
This article examines why a MySQL InnoDB table with a unique index can still store duplicate rows—especially when indexed columns contain NULL values or when logical deletion is used—and presents several practical strategies such as adjusting delete status, adding timestamp or auxiliary ID fields, using hash columns, and leveraging proper bulk‑insert techniques to enforce uniqueness effectively.
This article explores a common MySQL InnoDB pitfall where unique indexes fail to prevent duplicate rows, explains how NULL values and logical deletion affect uniqueness, and presents practical strategies—including composite keys, delete status counters, timestamps, hash fields, and Redis locks—to enforce true uniqueness.
This article reproduces a MongoDB issue where queries using a hashed sharding key return no results and a unique compound index appears to fail, then explains the root cause—index entries exceeding the 1024‑byte limit and the failIndexKeyTooLong parameter—providing step‑by‑step commands, configuration changes, and practical recommendations.
This article explains how MySQL UNIQUE indexes affect data insertion and auto‑increment behavior, compares them with primary keys, and presents three techniques—INSERT IGNORE, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—to prevent duplicate‑key failures, including deadlock considerations.
This article outlines essential principles for selecting and managing database indexes, covering unique indexes, indexing frequently sorted or filtered columns, limiting index count, using short or prefix indexes, removing unused indexes, left‑most prefix matching, column cardinality, avoiding functions on indexed columns, and extending existing indexes.
This article examines three real‑world MySQL deadlock incidents—unique‑key null insertion, concurrent duplicate‑key insertion, and a JDBC parameter bug—detailing their detection, analysis, and the concrete fixes applied to prevent recurrence.