MySQL Connector/Net -- Mysql.data.MySqlClient需要使用5.2.3版,之前版本对分布式事务支持存在bug。
1. 测试同一个链接字符串执行多个命令。
string mySqlConnString1 = "server=localhost;uid=root;database=test;Pwd=1234;";
string mySqlConnString2 = "server=127.0.0.1;uid=root;database=test;Pwd=1234;";
string insertSql = "insert into users(userId, userName) values ('{0}','{1}')";
[Test]
public void TestDTC()
{
using (TransactionScope scope = new TransactionScope())
{
// 执行相同的语句,第二句会引发主键冲突异常
MySqlExecuteNonQuery(mySqlConnString1, string.Format(insertSql, "Id1", "Name1"));
MySqlExecuteNonQuery(mySqlConnString1, string.Format(insertSql, "Id1", "Name1"));
scope.Complete();
}
}
void MySqlExecuteNonQuery(string connString, string sql)
{
using (MySqlConnection cnx = new MySqlConnection(connString))
{
MySqlCommand cmd = new MySqlCommand(sql, cnx);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
}
}
结果数据库全部没有任何更新。测试通过。
2. 测试在同一个数据库两个不同的链接字符串上执行命令
public void TestDTC()
{
using (TransactionScope scope = new TransactionScope())
{
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连接
string mySqlConnString1 = "server=localhost;uid=root;database=test;Pwd=1234;";
string sqlSvrConnString1 = @"server=(local);uid=sa;Pwd=sa;database=pubs;";
string insertSql = "insert into users(userId, userName) values ('{0}','{1}')";
[Test]
public void TestDTC()
{
using (TransactionScope scope = new TransactionScope())
{
SqlServerExecuteNonQuery(sqlSvrConnString1, string.Format(insertSql, "Id1", "Name1"));
MySqlExecuteNonQuery(mySqlConnString2, string.Format(insertSql, "Id1", "Name1"));
scope.Complete();
}
}
void MySqlExecuteNonQuery(string connString, string sql)
{
using (MySqlConnection cnx = new MySqlConnection(connString))
{
MySqlCommand cmd = new MySqlCommand(sql, cnx);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
}
}
void SqlServerExecuteNonQuery(string connString, string sql)
{
using (SqlConnection cnx = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(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)支持同一个连接字符串内多个连接实例的事务,不支持不同连接字符串以及不同数据库之间的事务。