MySQL Bugs: #88611: Transactions get auto-enlisted in mysql connector .net

Bug #88611	Transactions get auto-enlisted in mysql connector .net
Submitted:	22 Nov 2017 16:09	Modified:	30 Nov 2017 6:27
Reporter:	Karl Johansson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / NET Documentation	Severity:	S3 (Non-critical)
Version:	8.0.9-dmr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a transaction gets started on a connection, all commands that are executed afterwards get auto-enlisted in the transaction. Is this intended behaviour? It's not obvious at all, and the MysqlCommand's Transaction property is NULL if you break at the cmd.ExecuteNonQuery() but in actual fact the command will participate in the transaction. In the code example, the row with xxx_id = 1 is locked for the duration of the sleep even though there is no explicit enlistment in the transaction.

How to repeat:
class Test
{
	public void Run()
	{
		using (MySqlConnection sql = new MySqlConnection("server=127.0.0.1;uid=xxx;pwd=xxx;database=xxx;charset=utf8mb4;SslMode=none;"))
		{
			sql.Open();
			var cmd = sql.CreateCommand();
			cmd.CommandText = "select * from xxx where xxx_id = 1 FOR UPDATE";
			var trx = sql.BeginTransaction();
			//cmd.Transaction = trx;
			cmd.ExecuteNonQuery();

			System.Threading.Thread.Sleep(10000000);
		}
	}
}

Suggested fix:
Either force explicit enlistment or update the manual to make this behaviour clear.

> If a transaction gets started on a connection, all commands that are executed afterwards get auto-enlisted in the transaction. Is this intended behaviour?

It's somewhat unavoidable behaviour. In MySQL Server, once a client begins a transaction, all subsequent commands (on that connection) are part of that transaction until the client commits or rolls back the transaction. To execute a command outside of that transaction, one has to open a separate connection.

This doesn't match the ADO.NET API very well; as you've noticed, the client can optionally set the MySqlCommand.Transaction property or not, and might expect that to have an effect.

To counteract this misunderstanding, SqlClient, Microsoft.Data.Sqlite, and MySqlConnector (https://github.com/mysql-net/MySqlConnector) all throw an InvalidOperationException when attempting to execute a DbCommand with a missing or wrong Transaction property.

The MySql.Data connector may not be able to make this change as it could break existing code that relies on this bug. (So they might just need to update the documentation, as you suggest.)

Hello Karl Johansson,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.

Posted by developer:
 
Added a new FAQ in the C/NET Developer Guide to clarify the transaction behavior of classic MySQL.

Reassigned to the C/NET team who will update the API reference guides.