Is your feature request related to a problem? Please describe.

Currently job adoption is handled by a reconciliation loop whereby the set of runnable jobs are claimed and then the claimed jobs are run. These loops end up scanning various indexes of the jobs table and can encounter intents. This can be problematic in that a long-running transaction which creates a job may hold off adoption of new jobs.

Describe the solution you'd like

For jobs which are intended to be run on the gateway, which is most schema change jobs and most jobs which are intended to be run semi-synchronously, we could utilize a rangefeed as a source of information about claimed jobs. Such a thing would be easy to integrate; it could legitimately just launch a goroutine to call (*jobs.Registry).resumeJob().

Describe alternatives you've considered

If cockroach supported SKIP LOCKED or some mechanism to scan below intents, that may work. However, such a thing seems hard to implement in the context of serializable isolation. See #40476 for more discussion.

Jira issue: CRDB-2639