[txn] Pessimistic RC INSERT IGNORE can silently skip a valid foreign-key row
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug description
With a pessimistic `READ-COMMITTED` transaction, `INSERT IGNORE` can use the transaction's old snapshot for foreign-key validation even though the referenced parent was committed after `BEGIN`. The statement then returns success with `0` affected rows and only warning 1452, silently dropping a valid child row.
This is reproducible with ordinary two-session commit ordering, without failpoints or storage faults.
## Minimal reproduction
```sql
DROP DATABASE IF EXISTS rc_insert_ignore_fk;
CREATE DATABASE rc_insert_ignore_fk;
USE rc_insert_ignore_fk;
SET GLOBAL tidb_enable_foreign_key = ON;
CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (
id INT PRIMARY KEY,
pid INT,
CONSTRAINT fk_parent FOREIGN KEY (pid) REFERENCES parent(id)
);
```
In session A:
```sql
USE rc_insert_ignore_fk;
SET SESSION transaction_isolation = 'READ-COMMITTED';
BEGIN PESSIMISTIC;
```
After session A has begun, in session B:
```sql
USE rc_insert_ignore_fk;
INSERT INTO parent VALUES (1);
COMMIT;
SELECT * FROM parent WHERE id = 1;
-- 1
```
Immediately after session B commits, in session A run:
```sql
INSERT IGNORE INTO child VALUES (10, 1);
SHOW WARNINGS;
SELECT ROW_COUNT();
COMMIT;
SELECT * FROM child WHERE id = 10;
```
## Expected behavior
Because the parent commit completed before the child statement and the transaction is `READ-COMMITTED`, the child row should be inserted:
```text
ROW_COUNT() = 1
child = (10, 1)
no warning 1452
```
The same result is obtained when the parent is committed before session A begins.
## Actual behavior
On current master, using real TiKV and no failpoints, the parent is visible to session B but session A reports:
```text
Query OK, 0 rows affected, 1 warning
Warning 1452 Cannot add or update a child row: a foreign key constraint fails
ROW_COUNT() = 0
child = empty
```
The result is the same with `tidb_rc_write_check_ts` enabled or disabled. Replacing `INSERT IGNORE` with plain `INSERT` exposes the false foreign-key rejection as an error instead of silently losing the row.
## Suspected cause
The pessimistic RC planner treats a simple `INSERT` as eligible to reuse `latestOracleTS` (`pkg/sessiontxn/isolation/readcommitted.go`, `planSkipGetTsoFromPD`). The write path obtains a statement snapshot with the RC check option, but `FKCheckExec.checkRows` reads through `txn.GetSnapshot()` directly (`pkg/executor/foreign_key.go`). When the parent commits after `BEGIN`, those two snapshot owners disagree. `INSERT IGNORE` converts the resulting FK failure into an ignored row in `pkg/executor/write.go`, turning the stale read into silent data loss.
## Environment
- TiDB current master: `ca95cc55e1b28678956465f160059ea0834d5fc9`
- Storage: one local TiKV, no failpoints
- Transaction mode: pessimistic `READ-COMMITTED`
- Foreign-key support: enabled
Contributor guide
Research direction
Run the two-session READ-COMMITTED reproduction first and confirm the warning, affected-row count, and missing child row. Then read planSkipGetTsoFromPD in pkg/sessiontxn/isolation/readcommitted.go, FKCheckExec.checkRows in pkg/executor/foreign_key.go, and the INSERT IGNORE handling in pkg/executor/write.go. Done means the committed parent is visible to the child statement without warning 1452 or silent row loss.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql
- Domain
- databases, distributed-systems
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 64/100