Allow dynamic pruning mode also for transactions

[mjonss]

Enhancement

Revert #26876 (#26719) and fix the underlying issue.

Might also be related to #28073?

See:

tidb/sessionctx/variable/session.go

Lines 1011 to 1019 in 56ba37a

	// UseDynamicPartitionPrune indicates whether use new dynamic partition prune.
	func (s *SessionVars) UseDynamicPartitionPrune() bool {
	if s.InTxn() {
	// UnionScan cannot get partition table IDs in dynamic-mode, this is a quick-fix for issues/26719,
	// please see it for more details.
	return false
	}
	return PartitionPruneMode(s.PartitionPruneMode.Load()) == Dynamic
	}

where it explicitly turns off dynamic pruning mode if there is an ongoing transaction.