During flow setup on the gateway node, we check whether the flow has any concurrency. If yes, we make sure to use the leaf txn for the flow. If no, we may use the root txn, which doesn't support concurrent requests. That logic is handled here in ServerImpl.setupFlow. Apply-joins are effectively nested-loop joins from postgres. They fully re-plan and then execute their right input for each row from their left input. This means each apply-join iteration creates its own flow. In order to do this, apply-join execution nodes keep track of a transaction, which is captured here in DistSQLPlanner.wrapPlan through the planner and evalCtx. The problem is that the transaction is captured before the main query makes its decision about which transaction to use, and so we can end up executing the right side of an apply-join with the root txn when the main query has concurrency that necessitates use of the leaf txn.

This caused a query to repeatedly fail in a customer issue, where multiple apply-joins were planned in the inputs of a Union, which was planned as a ParallelUnorderedSynchronizer. This caused the apply-joins to be executed concurrently with one another, and since they used the root txn, KV returns a concurrent txn use detected error when concurrent requests are received. Note that even if there was only one concurrent use of the root txn while the main query used a leaf txn, the usage would still be incorrect even though it wouldn't hit that particular error condition (see internal discussion here).

I've been unable to reproduce the error the customer ran into so far, but suggesting a query rewrite to remove the apply-joins fixed the issue for them. We should fix apply-join execution to use the correct transaction - probably either by modifying the captured transaction if the main query has concurrency, or by just always using a leaf txn for apply-joins.

See also #41992, which tracks the general case of expressions capturing the wrong transaction, and refactoring to prevent this problem.

Jira issue: CRDB-25005