Send single payload in BeginTransactionAsync

[DomoYao]

Discussion summary: Revert change in #774 and use Pipelining = false for databases that don't support a single payload. This will reduce the round-trips from 2 to 1 when starting a transaction.

---

private async ValueTask<MySqlTransaction> **BeginTransactionAsync**(IsolationLevel isolationLevel, bool? isReadOnly, IOBehavior ioBehavior, CancellationToken cancellationToken)
	{
		using (var cmd = new MySqlCommand($"set session transaction isolation level {isolationLevelValue};", this) { NoActivity = true })
		{
			await cmd.ExecuteNonQueryAsync(ioBehavior, cancellationToken).ConfigureAwait(false);

			var consistentSnapshotText = isolationLevel == IsolationLevel.Snapshot ? " with consistent snapshot" : "";
			var readOnlyText = isReadOnly switch
			{
				true => " read only",
				false => " read write",
				null => "",
			};
			var separatorText = (consistentSnapshotText.Length == 0 || readOnlyText.Length == 0) ? "" : ",";
			cmd.CommandText = $"start transaction{consistentSnapshotText}{separatorText}{readOnlyText};";
			await cmd.ExecuteNonQueryAsync(ioBehavior, cancellationToken).ConfigureAwait(false);
		}

		var transaction = new MySqlTransaction(this, isolationLevel);
		CurrentTransaction = transaction;
		return transaction;
	}

Can't the default level of the database be used?
This class seems to be one more database operation.

[bgrainger]

What do you propose instead?

[bgrainger]

Previously:

• Using / not using SESSION: Remove "session" from "set transaction" statement #263 Ability to use session transaction isolation level #801
• Splitting into two commands: Composite commands are not supported #774

[DomoYao]

What do you propose instead?
Thanks @bgrainger .
I think, no transaction level specified, no transaction setup.

if (isolationLevel != IsolationLevel.Unspecified)
{
await cmd.ExecuteNonQueryAsync(ioBehavior, cancellationToken).ConfigureAwait(false);
}

I tested that one less "set session transaction isolation level repeatable read;" script execution.There is a performance gain, but I don't know if there are other effects

[bgrainger]

This would be a significant behavioural change for all existing users if connection.BeginTransaction() no longer set a default isolation level and used the database default instead.

If we need to optimise performance, I'd rather revert #774 and instruct the (small number of) users who are affected to opt out by using Pipelining = false. (It probably would have been done that way in the first place if the Pipelining connection string option existed when that issue was fixed.)

Is there any other reason to make this change, other than reducing DB roundtrips?

[DomoYao]

Thank @bgrainger , my purpose is to optimise performance, through configuration optimization is a better solution.

[bgrainger]

Fixed in 2.3.6.