opt: no home region error in explicit txn has no home region

With `enforce_home_region` set, it seems that there is different behavior for single statements using implicit transactions vs. statements in explicit transactions. Here is a repro, using `cockroach demo --global --nodes 9 --multitenant=false --insecure`:

```sql
CREATE DATABASE d PRIMARY REGION "us-east1" REGIONS "europe-west1", "us-west1";
SHOW CREATE DATABASE d;
USE d;

CREATE TABLE abc (
a INT,
b INT,
c INT,
PRIMARY KEY (a),
INDEX (b)
) LOCALITY REGIONAL BY ROW;
SHOW CREATE TABLE abc;

INSERT INTO abc (a, b, c, crdb_region) VALUES (1, 1, 1, 'europe-west1'), (2, 2, 2, 'us-west1');

SET enforce_home_region = on;
SET enforce_home_region_follower_reads_enabled = on;

-- assuming locality is 'us-east1'
SHOW LOCALITY;

-- when this statement runs in an implicit transaction, it fails, but does have a home region (which is reported)
SELECT a FROM abc WHERE a = 1 LIMIT 1;

-- when run in an explicit transaction, however, it fails but does *not* have a home region:
BEGIN;
SELECT a FROM abc WHERE a = 1 LIMIT 1;
```

Here's how it looks on current tip of master (`v23.1.0-alpha.8-dev`):

```
root@127.0.0.1:26257/d> SELECT a FROM abc WHERE a = 1 LIMIT 1;
a
-----
1
(1 row)
(error encountered after some results were delivered)
ERROR: Query is not running in its home region. Try running the query from region 'europe-west1'. For more information, see https://www.cockroachlabs.com/docs/stable/cost-based-optimizer.html#control-whether-queries-are-limited-to-a-single-region
SQLSTATE: XCHR1

root@127.0.0.1:26257/d> BEGIN;
BEGIN

Time: 0ms total (execution 0ms / network 0ms)

root@127.0.0.1:26257/d OPEN> SELECT a FROM abc WHERE a = 1 LIMIT 1;
ERROR: Query has no home region. Try using a lower LIMIT value or running the query from a different region. For more information, see https://www.cockroachlabs.com/docs/stable/cost-based-optimizer.html#control-whether-queries-are-limited-to-a-single-region
SQLSTATE: XCHR2
```

Jira issue: CRDB-25921

Start by running the provided `cockroach demo --global --nodes 9 --multitenant=false --insecure` SQL reproduction, comparing the implicit and explicit transaction cases with `enforce_home_region` enabled. Trace the transaction and home-region error handling from the SQL execution entry point; done means explicit transactions report the appropriate home-region behavior consistently with implicit statements.

Written by the indexing model from the issue text.