How to verify TransactionScope works with MySQL?

Question

I want to add read uncommitted isolation level.i know it is possible to do with SQL statement. but I want to try with TransactionScope and I tried but not getting any isolation statements on MySQL general logs. Questions 1 does transactionscope class work with MySQL. If yes then how to verify.

Answer 1

I want to add read uncommitted isolation level.i know it is possible to do with SQL statement.

The "standard" way to do this would be to use the MySqlConnection.BeginTransaction API:

using (var transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
{
    // do stuff
    transaction.Commit();
}

does transactionscope class work with MySQL

Yes, MySQL Connector/NET can support TransactionScope, but not for distributed transactions. It's a known issue that Connector/NET doesn't support XA Transactions with TransactionScope.

If you want to use TransactionScope, then MySQL Connector/NET should inherit its isolation level:

var txOptions = new System.Transactions.TransactionOptions();
txOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;

using(var transaction = new TransactionScope(TransactionScopeOption.Required, txOptions))
using (var connection = new MySqlConnection("... connection string ..."))
{
    connection.Open();
    // ...
    transaction.Complete();
}

If you need to support true distributed transactions with TransactionScope, then switch to MySqlConnector as your ADO.NET driver. Unlike Connector/NET, it fully supports distributed transactions.