Enhancement: reduce FK existence-check locking contention for child inserts in pessimistic transactions

## Bug Report / Enhancement

Foreign key checks during child inserts in pessimistic transactions can deadlock on referenced parent rows much more aggressively than MySQL/InnoDB. TiDB appears to lock the parent row for FK existence checks with `FOR UPDATE` semantics rather than a shared lock, which means even child inserts can deadlock when two transactions touch the same set of parent rows in different order.

This report is primarily an enhancement request to reduce unnecessary contention for FK existence checks, or document the behavior more explicitly if it is by design.

### TiDB Version

```sql
SELECT tidb_version();
```

Observed locally on TiDB playground `v8.5.4` on macOS arm64.

### Minimal Reproduction

Schema:

```sql
DROP DATABASE IF EXISTS fk_deadlock_lab;
CREATE DATABASE fk_deadlock_lab;
USE fk_deadlock_lab;

CREATE TABLE parent (
id CHAR(2) PRIMARY KEY
);

CREATE TABLE child (
id INT PRIMARY KEY,
parent_id CHAR(2) NOT NULL,
CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent(id)
);

INSERT INTO parent (id) VALUES ('p1'), ('p2');
```

Run two concurrent pessimistic transactions:

Transaction 1:

```sql
SET SESSION tidb_txn_mode = 'pessimistic';
BEGIN PESSIMISTIC;
INSERT INTO child(id, parent_id) VALUES (1, 'p1');
INSERT INTO child(id, parent_id) VALUES (2, 'p2');
COMMIT;
```

Transaction 2:

```sql
SET SESSION tidb_txn_mode = 'pessimistic';
BEGIN PESSIMISTIC;
INSERT INTO child(id, parent_id) VALUES (101, 'p2');
INSERT INTO child(id, parent_id) VALUES (102, 'p1');
COMMIT;
```

To make the race deterministic, I used a short Python harness that synchronizes both sessions after the first insert:

```bash
python3 repro_fk_deadlock.py
```

### Observed Behavior

One transaction fails with:

```text
OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
```

`information_schema.deadlocks` shows the deadlock keys are on the **parent** table primary key rows, not the child rows:

```text
(1, ..., 'insert into `child` ...', '{"db_name":"fk_deadlock_lab","table_name":"parent","handle_value":"{p2}",...}', ...)
(1, ..., 'insert into `child` ...', '{"db_name":"fk_deadlock_lab","table_name":"parent","handle_value":"{p1}",...}', ...)
```

### Expected Behavior

Because the parent rows are only being checked for existence and are not being modified, I would expect less aggressive locking for FK existence checks, closer to MySQL/InnoDB shared-lock behavior, so that concurrent child inserts do not deadlock as easily.

At minimum, it would be helpful to have:

1. A less-contentious FK existence-check locking strategy, if feasible.
2. Or a session/system option to use shared-style locking for FK existence checks.
3. Or clearer documentation that child inserts may acquire exclusive parent-row locks for FK checks in TiDB pessimistic transactions.

### Notes

- The original workload used two child tables referencing the same parent table, but the issue reproduces with only one child table.
- Disabling FK checks avoids the deadlock, but that is a significant tradeoff for applications that want database-enforced referential integrity.
- Possibly related to statement-level FK deadlock retry work in #62565, but this report is specifically about reducing the underlying FK lock contention / improving configurability.