mysql 分布式事务_MySql分布式事务支持情况

MySQL Connector/Net -- Mysql.data.MySqlClient需要使用5.2.3版，之前版本对分布式事务支持存在bug。

1. 测试同一个链接字符串执行多个命令。

stringmySqlConnString1="server=localhost;uid=root;database=test;Pwd=1234;";stringmySqlConnString2="server=127.0.0.1;uid=root;database=test;Pwd=1234;";stringinsertSql="insert into users(userId, userName) values ('{0}','{1}')";

[Test]publicvoidTestDTC()

{using(TransactionScope scope=newTransactionScope())

{

// 执行相同的语句，第二句会引发主键冲突异常                MySqlExecuteNonQuery(mySqlConnString1,string.Format(insertSql,"Id1","Name1"));

MySqlExecuteNonQuery(mySqlConnString1,string.Format(insertSql,"Id1","Name1"));

scope.Complete();

}

}voidMySqlExecuteNonQuery(stringconnString,stringsql)

{using(MySqlConnection cnx=newMySqlConnection(connString))

{

MySqlCommand cmd=newMySqlCommand(sql, cnx);

cnx.Open();

cmd.ExecuteNonQuery();

cnx.Close();

}

}

结果数据库全部没有任何更新。测试通过。

2. 测试在同一个数据库两个不同的链接字符串上执行命令

[Test]publicvoidTestDTC()

{using(TransactionScope scope=newTransactionScope())

{

MySqlExecuteNonQuery(mySqlConnString1,string.Format(insertSql,"Id1","Name1"));

MySqlExecuteNonQuery(mySqlConnString2,string.Format(insertSql,"Id1","Name1"));

scope.Complete();

}

}

引发异常：Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported.

3.在多个数据库之间执行事务

一个MySql连接，一个SQLServer连接

stringmySqlConnString1="server=localhost;uid=root;database=test;Pwd=1234;";stringsqlSvrConnString1=@"server=(local);uid=sa;Pwd=sa;database=pubs;";stringinsertSql="insert into users(userId, userName) values ('{0}','{1}')";

[Test]publicvoidTestDTC()

{using(TransactionScope scope=newTransactionScope())

{

SqlServerExecuteNonQuery(sqlSvrConnString1,string.Format(insertSql,"Id1","Name1"));

MySqlExecuteNonQuery(mySqlConnString2,string.Format(insertSql,"Id1","Name1"));

scope.Complete();

}

}voidMySqlExecuteNonQuery(stringconnString,stringsql)

{using(MySqlConnection cnx=newMySqlConnection(connString))

{

MySqlCommand cmd=newMySqlCommand(sql, cnx);

cnx.Open();

cmd.ExecuteNonQuery();

cnx.Close();

}

}

voidSqlServerExecuteNonQuery(stringconnString,stringsql)

{using(SqlConnection cnx=newSqlConnection(connString))

{

SqlCommand cmd=newSqlCommand(sql, cnx);

cnx.Open();

cmd.ExecuteNonQuery();

cnx.Close();

}

}

引发异常：MySQL Connector/Net does not currently support distributed transactions.

结论：MySql目前版本(MySql5.0, MySQL Connector/Net 5.2.3)支持同一个连接字符串内多个连接实例的事务，不支持不同连接字符串以及不同数据库之间的事务。