pingcap / pingcap/tidb

[txn] Pessimistic RC INSERT IGNORE can silently skip a valid foreign-key row

Open
#70,600 2 comments 0 reactions 0 assignees View on GitHub
affects-25.10 affects-26.3 affects-7.5 affects-8.1 affects-8.5 component/executor found-by-ai severity/critical sig/transaction type/bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.