Tagged articles
Unique Index
30 articles · Page 1 of 1
The article explains how logical deletion conflicts with unique indexes in MySQL, shows why a composite index on (code, deleted) only supports one delete-create cycle, and presents a robust solution using a generated column that becomes NULL when deleted, leveraging MySQL's allowance of multiple NULLs in unique indexes to enforce uniqueness only on active records.
Multi-tenancyMySQLUnique Index
0 likes · 15 min read

This article explains why MySQL unique indexes permit multiple NULL values due to SQL's three-valued logic, demonstrates how this breaks upsert statements and causes silent duplicates, compares behavior across databases, and presents solutions using NOT NULL constraints, generated columns, and functional indexes to enforce true uniqueness.
InnoDBMySQLNULL handling
0 likes · 16 min read

This article analyzes how OpenFeign's default timeout and hidden retry layers (Feign, LoadBalancer, HttpClient, Nginx) cause duplicate requests, demonstrates a reproducible case where a 12-second downstream service triggers three retries creating duplicate orders, and presents a database unique-index idempotency pattern to prevent duplicates.
HttpClientIdempotencyOpenFeign
0 likes · 22 min read

This comprehensive guide explains MySQL index fundamentals using a dictionary analogy, covers four essential index types with SQL examples, details six common optimization pitfalls that cause index failure, and demonstrates how to verify index usage with EXPLAIN and SHOW INDEX commands.
B+TreeComposite IndexEXPLAIN
0 likes · 18 min read

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.
Distributed LockIdempotencySpring
0 likes · 10 min read


ITPUBNov 9, 2024 · Databases
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.
Hash FieldInnoDBMySQL
0 likes · 14 min read

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.
Brand ManagementMySQLSQL
0 likes · 8 min read

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.
InnoDBMySQLUnique Index
0 likes · 8 min read

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.
InnoDBMySQLPrimary Key
0 likes · 11 min read

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.
InnoDBPrimary KeyUnique Index
0 likes · 11 min read

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.
DELETEMySQLUnique Index
0 likes · 13 min read

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.
Database InternalsInnoDBMySQL
0 likes · 25 min read

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.
Database MigrationMySQLUnique Index
0 likes · 20 min read


ITPUBFeb 18, 2023 · Databases
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.
MySQLReplicationUnique Index
0 likes · 7 min read

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.
HashMySQLNULL handling
0 likes · 13 min read

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.
MySQLNULL handlingUnique Index
0 likes · 13 min read

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.
MySQLNULL handlingUnique Index
0 likes · 13 min read

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.
Index OptimizationMySQLNULL
0 likes · 15 min read

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.
MySQLNULL handlingUnique Index
0 likes · 14 min read

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.
MySQLNULL handlingUnique Index
0 likes · 14 min read

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.
InnoDBMyISAMMySQL
0 likes · 8 min read

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.
Unique IndexfailIndexKeyTooLongindexing
0 likes · 35 min read

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.
Duplicate KeyINSERT IGNOREMySQL
0 likes · 10 min read

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.
Duplicate KeyINSERT IGNOREMySQL
0 likes · 8 min read

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.
Change BufferInnoDBMySQL
0 likes · 13 min read

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.
InnoDBMySQLUnique Index
0 likes · 9 min read

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.
MySQLUnique Indexindex design
0 likes · 7 min read

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.
JDBCMySQLUnique Index
0 likes · 9 min read


ITPUBApr 26, 2016 · Databases
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.
ConcurrencyInnoDBMySQL
0 likes · 13 min read


ITPUBNov 16, 2015 · Databases
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.
ConcurrencySQLUnique Index
0 likes · 5 min read
