Relation auto-loading bypasses the connection pool: BelongsTo & friends resolve through a thread-local DataMapper

[Yaraslaut]

Summary

Every lazy relation loader installed by DataMapper::ConfigureRelationAutoLoading — BelongsTo,
CompositeForeignKey, HasMany, HasOneThrough, HasManyThrough — resolves its connection through
DataMapper::AcquireThreadLocal() (src/Lightweight/DataMapper/DataMapper.hpp:3004 and the 8 sites
following it). That is a thread_local DataMapper constructed once per thread from
SqlConnection::DefaultConnectionString() (src/Lightweight/DataMapper/DataMapper.cpp:8-12), entirely
outside DataMapperPool.

Relation traffic — often the majority of an application's queries — therefore never touches the pool.

Why this is a problem

1. The loading connection is unrelated to the one the record came from. Read a record through
   GlobalDataMapperPool().Acquire(), or through a DataMapper explicitly built against a second
   database/DSN, then touch record.belongsTo: the query goes to whatever the default connection
   string happened to be the first time that thread lazy-loaded anything. In a multi-database or
   multi-tenant setup that silently reads from the wrong database rather than failing.

2. Uncommitted state is invisible, and lock contention is self-inflicted. A lazy load inside a
   transaction runs on a different connection, so it cannot see the caller's uncommitted rows and can
   block on the caller's own locks. (Neighbouring pool/transaction gap: DataMapperPool::Return performs no transaction cleanup -- a connection returned mid-transaction is silently inherited by the next caller #583.)

3. Connections escape the pool's accounting. One extra connection per thread that ever lazy-loads:
   not counted against PoolConfig::maxSize, never idled, never health-checked (Connection pool has no acquire timeout, liveness check, idle eviction or max lifetime #562), released only at
   thread exit. Under the async layer, where ODBC work runs on Async::IExecutor worker threads, this
   quietly doubles the connection footprint.

4. The cached instance never picks up a later SetDefaultConnectionString. Not hypothetical —
   src/tools/dbtool-gui/AppController.cpp:706-727 carries a 15-line comment and a move-assign hack to
   force the thread-local to rebuild when the user switches connection profile, because otherwise
   "switching profiles / DSNs appears to succeed but keeps talking to the old database."

5. Per-connection tuning does not reach it. Prefetch depth today, and the prepared-statement cache
   capacity from Add prepared-statement caching for repeated SqlStatement executions #552 / Add an opt-in prepared-statement cache for repeated SqlStatement executions #576, are per-connection settings. Nobody configures the auto-loading connection,
   so a carefully tuned application still runs its relation traffic over one untuned connection per
   thread.

Constraint any fix must preserve

The thread-local exists because an auto-loader must not capture the DataMapper that produced the
record: a record returned by value from QuerySingle/First/... may outlive it, and the loaders
deliberately capture values (field.Value(), the PK, OrderedValuesOf(record)) for exactly that
reason — see the NRVO comment at DataMapper.hpp:3010-3025. A replacement must keep working when the
originating mapper is long gone, and must stay cheap: it sits on the auto-load path.

Suggested direction

• Acquire from a pool for the duration of one load and return immediately — a PooledDataMapper
  scoped to the loader body — instead of pinning a connection for the life of the thread.
• Make the pool a property of the record's auto-loaders rather than a global: the mapper that configures
  auto-loading records which pool to load from, defaulting to GlobalDataMapperPool(), so a record
  read from pool A lazy-loads from pool A. Rework sharing DataMapper state respective to BelongsTo and external use of threading models #397 sketched precisely this as a
  ScopedDataMapperAccessor held by the relation field; the pool half of that design landed, the loader
  half did not.
• Decide the fate of DataMapper::AcquireThreadLocal(): keep it as the documented fallback for the
  no-pool case (SqlMigration.cpp:147 and the dbtool-gui also use it), or deprecate it once the
  auto-loaders no longer depend on it — in which case AppController.cpp's rebuild hack can go too.
• Watch the interaction with DataMapperPool::Return performs no transaction cleanup -- a connection returned mid-transaction is silently inherited by the next caller #583: a pooled connection handed to a loader must not carry a stranger's
  open transaction, and vice versa.

Test coverage the fix should add

• A record read via mapper/pool A lazy-loads against A, with a different default connection string
  installed globally (this fails today).
• No connection is created outside the pool during lazy loading: assert pool checkout counts rise and
  fall around a lazy load, and that the process opens no extra connection (SqlLogger connection hooks).
• A lazy load inside an open transaction observes the caller's uncommitted rows.
• The async path: lazy loading from an executor worker thread does not permanently pin a connection per
  worker.

Related

#397 (original design, closed with only the pool half implemented), #583, #562, #563 (eager loading
would remove much of this traffic but not the correctness bug), #552 / #576.