MySQL Innodb Duplicate Unique Index Root Causes

When unique_checks=0, Innodb bypasses real-time uniqueness checks for non-primary indexes. Key mechanisms include:

1. ​Write Optimization: Inserts bypass the buffer pool and use the ​Change Buffer​ to reduce I/O overhead.
2. ​Deferred Validation: Duplicates are only detected when pages are loaded into memory (e.g., via SELECT).

​Risk Demonstration

-- Create test table with unique index `b`
CREATE TABLE test_dup (
    id INT PRIMARY KEY,
    b INT UNIQUE,
    c VARCHAR(50)
) ENGINE=InnoDB;

-- Insert initial data
INSERT INTO test_dup (id, b, c) VALUES (1, 100, 'A');

-- Disable unique_checks and insert duplicate
SET unique_checks=0;
INSERT INTO test_dup (id, b, c) VALUES (2, 100, 'B'); -- Silently inserted
COMMIT;

-- Query shows only one row, but index is corrupted
SELECT * FROM test_dup; -- Returns 1 row
CHECK TABLE test_dup; -- Reveals duplicate key error

‍

Key Takeaways​

• ​Official Warning: Disabling unique_checks risks silent duplicates. Ensure data integrity beforehand.
• ​Fix: Rebuild the index or use innodb_force_recovery to recover.

​Scenario 2: RR Isolation Level's Phantom Read Trap​

​Principle Analysis​
Under ​Repeatable Read (RR)​​ isolation:

1. ​Persistent Read View: Transactions inherit a snapshot on first read, ignoring subsequent updates.
2. ​Phantom Insertion: A concurrent update may overwrite a unique index value without triggering a conflict.

​Risk Demonstration

-- Session 1
START TRANSACTION;
UPDATE testuniq SET a=300 WHERE id=10; -- Locks row `id=10`

-- Session 2
START TRANSACTION;
UPDATE testuniq SET a=100 WHERE id=20; -- Bypasses uniqueness check due to MVCC
COMMIT; -- Succeeds (duplicate `a=100`)

-- Session 1 commits, causing a conflict
COMMIT; -- ERROR: Duplicate entry '100' for key 'a'

‍

MySQL’s Design Rationale​
This behavior aligns with MySQL’s implementation of MVCC. Mitigation options:

• Use SERIALIZABLE isolation for strict consistency.
• Add application-level pre-checks before updates.

​Summary & Best Practices​

1. ​Scenario 1: Avoid unique_checks=0 for production data imports unless guaranteed uniqueness.
2. ​Scenario 2: Use SERIALIZABLE isolation or application logic to avoid phantom writes.
3. ​Monitoring: Enable innodb_unique_check and regularly audit INFORMATION_SCHEMA.INNODB_METRICS.

Let me know if you need further refinements! 😊