This article explains four common idempotency strategies—token, database unique index, distributed lock, and request‑body digest—detailing their core ideas, key concepts, and providing ready‑to‑copy Spring/Redis code examples to prevent duplicate requests in high‑traffic backend systems.
This article explains why a MySQL 8 InnoDB table with a unique index can still store duplicate rows—especially when indexed columns contain NULL or when logical deletion is used—and presents several practical strategies, including status counters, timestamps, extra IDs, hash fields, and batch‑insert techniques, to enforce true uniqueness.
This article examines a puzzling MySQL case‑insensitive collation issue where a user‑defined brand "yoyo" cannot be saved because the database returns an existing uppercase "YOYO" entry, explains the underlying COLLATE settings, and proposes practical solutions to prevent duplicate brand data.
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 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 explains why MySQL auto‑increment primary keys can become non‑continuous, covering storage mechanisms, unique‑index violations, transaction rollbacks, batch inserts, and step‑size settings, and provides practical examples and diagrams to help developers diagnose and avoid ID gaps.
This article examines a puzzling MySQL deadlock where two identical DELETE statements on a uniquely indexed table block each other, explains the underlying lock mechanisms, reproduces the issue with SQL and Java code, and offers practical mitigation strategies.
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.
In MySQL 5.7 replication, disabling unique_checks can cause duplicate‑key errors on a unique index that is not a primary key, leading to replication stalls, and this guide explains the root cause, temporary and permanent fixes, and step‑by‑step reproduction instructions.
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 explains why a unique index in MySQL can still allow duplicate rows when indexed columns contain NULL, explores the challenges of adding unique indexes to logically deleted tables, and presents practical solutions such as incremental delete status, timestamps, extra IDs, hash fields, and proper batch insertion techniques.
This article explains why a MySQL InnoDB table with a unique index can still contain duplicate rows—especially when indexed columns contain NULL values or when logical deletion is used—and presents several practical strategies, including schema changes and alternative locking mechanisms, to enforce true uniqueness.
This article explains why a MySQL UNIQUE index does not prevent duplicate rows when indexed columns contain NULL, explores the complications of logical‑delete tables, and presents practical solutions such as incrementing delete status, adding timestamps, delete‑id keys, hash fields, and proper batch inserts.
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 explains why a unique index on a MySQL InnoDB table may still permit duplicate rows, especially when indexed columns contain NULL values or when logical deletion is used, and presents practical solutions such as adjusting index fields, adding timestamps, hash columns, or redesigning delete logic.
This article examines MySQL's ON DUPLICATE KEY UPDATE clause, explains its official behavior, runs experiments with primary and multiple unique indexes, compares InnoDB row‑level locking to MyISAM table‑level locking, and highlights performance and deadlock risks that developers should avoid.
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 influence auto‑increment behavior, compares unique constraints with primary keys, and demonstrates three techniques—INSERT IGNORE, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—to avoid duplicate‑key errors, including their effects on auto‑increment values and potential deadlocks.
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 explains the differences between normal and unique indexes in MySQL, examines how they affect query and update performance, details InnoDB's change‑buffer mechanism, and provides practical guidance on when to prefer ordinary indexes for better write efficiency.
This article analyzes a MySQL InnoDB deadlock caused by concurrent batch inserts into a table with a composite unique index, explains the lock types involved, and offers practical strategies such as shortening transactions and lowering isolation levels to avoid similar deadlocks.
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.
This article analyzes a puzzling MySQL deadlock scenario where concurrent DELETE statements on a uniquely indexed row lead to a deadlock, explaining the lock modes, InnoDB's deadlock‑prevention strategy, and the conditions that make such deadlocks possible.
This guide explains two flash‑sale scenarios—single‑item seckill with a unique index and multi‑item seckill with quantity tracking—detailing table structures, SQL statements, concurrency handling, advantages, drawbacks, and an optimized version without locks, illustrated with step‑by‑step diagrams.
