bug: two Connections on one file in one process do not lock against each other — a write that reports success is silently discarded

[dpsiderius]

Description

Two Connections opened on the same file in the same process do not lock
against each other. A write on one can be silently discarded by the other's
commit, with no error anywhere and PRAGMA integrity_check reporting ok.

Measured, not inferred:

1. Connection A takes BEGIN IMMEDIATE and inserts row 2.
2. Connection B inserts row 3 — and gets Ok(1).
3. A commits.
4. The file holds [1, 2]. Row 3 is gone. integrity_check says ok.

Cause

POSIX fcntl record locks are scoped to (process, inode), not to a file
descriptor or an object. Two Connections in one process each acquire their
own locks on the same inode and do not conflict, because the kernel
considers them the same lock holder.

This crate already knows this. src/vfs/lock.rs:96 documents the scoping, and
check_reserved_lock states outright that it cannot see another handle in the
same process. What is missing is the thing stock SQLite builds on top of it:
unixInodeInfo in os_unix.c, a process-wide registry keyed by
(device, inode) that mediates between handles before any syscall is made.

This answers a question #491 was closed without answering

#491 ("Pager WAL lock guards each open an independent fd") asked, verbatim:

Does this crate's current design ever actually hold two of these guards
concurrently within one process? […] Multiple Pager instances opened
against the same file within one process (embedding scenario, or a test
harness) — plausible exposure.

It was closed as COMPLETED on 2026-08-24 with no comment recording an
answer. The answer is yes, it is reachable, and it loses data. #491 scoped
itself to the three -shm WAL guards; this is the general case and includes
rollback-journal mode.

Why it matters now

It is a pre-existing engine defect, not introduced by the embedding API — but
spec 013 Requirement 4 exists precisely so that a pool can hold a
Connection, which makes two handles on one file the expected deployment
rather than an exotic one. The SQE consumer has had to build a Weak registry
keyed by canonical path on their side to avoid it; every consumer would have
to build the same thing.

Cross-process locking is correct — verified against the pinned sqlite3 — so
this is specifically the same-process case.

Scope

• A process-wide registry keyed by the file's (device, inode), consulted
  before the fcntl layer, so two handles on one inode serialise against each
  other the way two processes already do.
• src/vfs/unix.rs already does something adjacent for a single
  UnixVfsFile (sharing one File per path via
  Rc<RefCell<FileLockState>>), but that sharing is per-VFS-instance and two
  Connections each build their own VFS.
• The -shm guards in src/vfs/shm.rs that Pager WAL lock guards each open an independent fd, risking cross-guard lock loss within one process #491 named should end up going
  through the same registry, which closes Pager WAL lock guards each open an independent fd, risking cross-guard lock loss within one process #491 properly rather than by
  assertion.

Non-goals

• Cross-process locking, which already works.
• WAL's multi-reader/single-writer semantics beyond making the in-process case
  behave like the cross-process one.

Acceptance Criteria

• tests/unit/api_threading_test.rs::in_process_connections_lock_against_each_other
  — the #[ignore]d ratchet that exists today — passes with the
  #[ignore] removed
• Connection B's write in the sequence above either blocks until A commits
  or fails with SQLITE_BUSY; it must not report success and be discarded
• The same sequence across two processes still behaves as it does today
  (regression guard — the fix must not serialise by breaking the existing
  correct path)
• A pool holding N handles to one file makes progress rather than
  deadlocking
• Pager WAL lock guards each open an independent fd, risking cross-guard lock loss within one process #491's -shm guards go through the registry, and Pager WAL lock guards each open an independent fd, risking cross-guard lock loss within one process #491 is referenced as
  answered rather than assumed

Complexity

Estimate: medium-large
Reasoning: The design is settled by prior art — unixInodeInfo is the
reference and the shape is a keyed registry — but it sits underneath the VFS,
so it touches the layer every other test depends on, and the failure mode it
prevents is invisible to our own reads. The care is in the lifecycle: the
registry entry must outlive individual handles and be removed exactly when the
last one goes, which is the same pInode->pUnused/setPendingFd problem
#412 and #491 circled without landing.

Refs: 013/Req-4, #491, #412, #705