sql/opt: push sorting below locking

Multi-row SELECT FOR UPDATE statements can deadlock if they lock some of the same rows as another transaction. One tool for avoiding deadlocks is explicitly ordering lock acquisition the same across all transactions.

The new implementation of SELECT FOR UPDATE (used when `optimizer_use_lock_op_for_serializable` is set, or under read committed isolation) does not always interact with ORDER BY in the way we would want. When the ordering does not require a final sort, we correctly lock after the rows are already ordered. But when the ordering requires a final sort, we perform this sort _after_ the locking, instead of before. We should be doing the sort before the locking.

Here's a demonstration on v24.1.0-alpha.5:

```sql
CREATE TABLE ab (a int PRIMARY KEY, b int);
CREATE TABLE c (c int PRIMARY KEY);
SET optimizer_use_lock_op_for_serializable = on;

-- ordering does not require a final sort: works correctly
EXPLAIN (OPT, VERBOSE) SELECT * FROM ab WHERE b % 10 = 6 ORDER BY a FOR UPDATE;

-- more complex example with sort in a subplan, still works correctly
EXPLAIN (OPT, VERBOSE) SELECT * FROM ab INNER MERGE JOIN c ON (c = b) WHERE b % 10 = 6 ORDER BY c FOR UPDATE;

-- ordering requires a final sort: not pushing sort below locking
EXPLAIN (OPT, VERBOSE) SELECT * FROM ab WHERE b % 10 = 6 ORDER BY b FOR UPDATE;
```

Here's how those look:

```
demo@127.0.0.1:26257/demoapp/defaultdb> EXPLAIN (OPT, VERBOSE) SELECT * FROM ab WHERE b % 10 = 6 ORDER BY a FOR UPDATE;
->
info
------------------------------------------------------------------
lock ab
├── columns: a:1 b:2
├── locking: for-update
├── volatile, mutations
├── stats: [rows=333.3333]
├── cost: 1118.87
├── key: (1)
├── fd: (1)-->(2)
├── ordering: +1
├── distribution: us-east1
└── select
├── columns: a:1 b:2
├── immutable
├── stats: [rows=333.3333]
├── cost: 1118.85
├── key: (1)
├── fd: (1)-->(2)
├── ordering: +1
├── distribution: us-east1
├── interesting orderings: (+1)
├── scan ab
│ ├── columns: a:1 b:2
│ ├── stats: [rows=1000, distinct(1)=1000, null(1)=0]
│ ├── cost: 1108.82
│ ├── key: (1)
│ ├── fd: (1)-->(2)
│ ├── ordering: +1
│ ├── distribution: us-east1
│ └── interesting orderings: (+1)
└── filters
└── (b:2 % 10) = 6 [outer=(2), immutable]
(31 rows)

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

demo@127.0.0.1:26257/demoapp/defaultdb> EXPLAIN (OPT, VERBOSE) SELECT * FROM ab INNER MERGE JOIN c ON (c = b) WHERE b % 10 = 6 ORDER BY c FOR UPDATE;
info
-------------------------------------------------------------------------------------------------------------------
distribute
├── columns: a:1 b:2 c:5
├── volatile, mutations
├── stats: [rows=330]
├── cost: 2476.58617
├── key: (1)
├── fd: (1)-->(2), (2)==(5), (5)==(2)
├── ordering: +(2|5) [actual: +2]
├── distribution: us-east1
├── input distribution:
└── lock c
├── columns: a:1 b:2 c:5
├── locking: for-update
├── volatile, mutations
├── stats: [rows=330]
├── cost: 2276.56617
├── key: (1)
├── fd: (1)-->(2), (2)==(5), (5)==(2)
├── ordering: +(2|5) [actual: +2]
└── lock ab
├── columns: a:1 b:2 c:5
├── locking: for-update
├── volatile, mutations
├── stats: [rows=330]
├── cost: 2276.54617
├── key: (1)
├── fd: (1)-->(2), (2)==(5), (5)==(2)
├── ordering: +(2|5) [actual: +2]
└── inner-join (merge)
├── columns: a:1 b:2 c:5
├── flags: force merge join
├── left ordering: +2
├── right ordering: +5
├── immutable
├── stats: [rows=330, distinct(2)=97.2658, null(2)=0, distinct(5)=97.2658, null(5)=0]
├── cost: 2276.52617
├── key: (1)
├── fd: (1)-->(2), (2)==(5), (5)==(2)
├── ordering: +(2|5) [actual: +2]
├── interesting orderings: (+1) (+5)
├── sort
│ ├── columns: a:1 b:2
│ ├── immutable
│ ├── stats: [rows=333.3333, distinct(2)=98.2658, null(2)=3.33333]
│ ├── cost: 1188.08951
│ ├── key: (1)
│ ├── fd: (1)-->(2)
│ ├── ordering: +2
│ ├── prune: (1)
│ ├── interesting orderings: (+1)
│ └── select
│ ├── columns: a:1 b:2
│ ├── immutable
│ ├── stats: [rows=333.3333, distinct(2)=98.2658, null(2)=3.33333]
│ ├── cost: 1118.85
│ ├── key: (1)
│ ├── fd: (1)-->(2)
│ ├── prune: (1)
│ ├── interesting orderings: (+1)
│ ├── scan ab
│ │ ├── columns: a:1 b:2
│ │ ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(2)=100, null(2)=10]
│ │ ├── cost: 1108.82
│ │ ├── key: (1)
│ │ ├── fd: (1)-->(2)
│ │ ├── prune: (1,2)
│ │ ├── interesting orderings: (+1)
│ │ └── unfiltered-cols: (1-4)
│ └── filters
│ └── (b:2 % 10) = 6 [outer=(2), immutable]
├── select
│ ├── columns: c:5
│ ├── immutable
│ ├── stats: [rows=333.3333, distinct(5)=333.333, null(5)=0]
│ ├── cost: 1078.45
│ ├── key: (5)
│ ├── ordering: +5
│ ├── interesting orderings: (+5)
│ ├── scan c
│ │ ├── columns: c:5
│ │ ├── stats: [rows=1000, distinct(5)=1000, null(5)=0]
│ │ ├── cost: 1068.42
│ │ ├── key: (5)
│ │ ├── ordering: +5
│ │ └── interesting orderings: (+5)
│ └── filters
│ └── (c:5 % 10) = 6 [outer=(5), immutable]
└── filters (true)
(88 rows)

Time: 4ms total (execution 4ms / network 1ms)

demo@127.0.0.1:26257/demoapp/defaultdb> EXPLAIN (OPT, VERBOSE) SELECT * FROM ab WHERE b % 10 = 6 ORDER BY b FOR UPDATE;
info
----------------------------------------------------------------------
distribute
├── columns: a:1 b:2
├── volatile, mutations
├── stats: [rows=0.3333333]
├── cost: 230.013333
├── key: (1)
├── fd: (1)-->(2)
├── ordering: +2
├── distribution: us-east1
├── input distribution:
└── sort
├── columns: a:1 b:2
├── volatile, mutations
├── stats: [rows=0.3333333]
├── cost: 29.9933333
├── key: (1)
├── fd: (1)-->(2)
├── ordering: +2
└── lock ab
├── columns: a:1 b:2
├── locking: for-update
├── volatile, mutations
├── stats: [rows=0.3333333]
├── cost: 29.96
├── key: (1)
├── fd: (1)-->(2)
└── select
├── columns: a:1 b:2
├── immutable
├── stats: [rows=0.3333333]
├── cost: 29.94
├── key: (1)
├── fd: (1)-->(2)
├── interesting orderings: (+1)
├── scan ab
│ ├── columns: a:1 b:2
│ ├── stats: [rows=1, distinct(1)=1, null(1)=0]
│ │ histogram(1)=
│ ├── cost: 29.9
│ ├── key: (1)
│ ├── fd: (1)-->(2)
│ └── interesting orderings: (+1)
└── filters
└── (b:2 % 10) = 6 [outer=(2), immutable]
(44 rows)

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

Jira issue: CRDB-37282