[Subtask] Investigate caught nested transaction failures and define recovery semantics

### Describe the subtask

SqlSessions shares a transaction through a nesting count. An inner rollback at positive depth is not remembered. Controlled tests on H2, MySQL, and PostgreSQL insert a User, throw an inner OCC exception, catch it outside the inner boundary, and commit the outer transaction; the User persists. H2 controls confirm that uncaught failures roll back and successful nesting commits normally.

This proves utility behavior, not a reachable production partial-commit bug. Audit real callers that catch inner failures and establish whether mutation can precede the caught error under compliant OCC writers.

Deliverables:
- Document reachable call chains, or explain why current callers cannot trigger a harmful partial commit.
- Define whether inner failure poisons the outer transaction or allows recovery; review rollback-only versus savepoints only if a change is justified.
- If changed, test caught/uncaught failures, normal nesting, zero-write missing-entity recovery, resource cleanup, and thread reuse.

Do not silently roll back while returning success, or introduce transaction-wide behavior changes solely from the synthetic reproduction.

### Parent issue

https://github.com/apache/gravitino/issues/10238

Start by tracing SqlSessions transaction-sharing and the real callers that catch inner failures, checking whether mutation can precede a caught OCC error. Run or inspect the controlled H2, MySQL, and PostgreSQL tests for caught and uncaught failures, normal nesting, and zero-write recovery. Done means documented call chains or a justification that no harmful partial commit is reachable, with recovery semantics and any justified changes covered by the requested cleanup and reuse tests.

Written by the indexing model from the issue text.