MSDTC异常交易：C＃ [英] MSDTC Exception during Transaction: C#
问题描述
我在C＃应用程序的事务中收到MSDTC异常。功能是在从csv文件读取后将一个lakh（十万）个邮政编码记录上传到数据库表中。此操作在大约20个批处理数据库操作（每个批次包含5000个记录）中完成。如果我不使用事务，这个功能是正常的。
有趣的是使用事务的其他功能可以完成交易。这导致我假设异常消息是一个误导的消息。
有什么可能是问题的想法？
异常：分布式事务管理器（MSDTC）的网络访问已被禁用。请使用组件服务管理工具在MSDTC的安全配置中启用DTC进行网络访问。
来源：System.Transactions
内部异常：事务管理器已禁用其对远程/网络事务的支持。 （来自HRESULT的异常：0x8004D024）
注意：事务中有一个for循环。是否导致任何问题？
实际需求是：邮政编码表中有一些现有的邮政编码。每个月管理员将上传新的邮政编码csv文件。来自csv的新项目被插入。在csv（但存在于数据库中）中不可用的邮政编码被认为已经被退出并被删除。退回的邮政编码清单将返回给用户界面。新添加的邮政编码也需要退回。
private void ProcessZipCodes（StringBuilder dataStringToProcess，int UserID）
{
int CountOfUnchangedZipCode = 0;
string strRetiredZipCode =;
string strNewZipCode =;
dataStringToProcess.Remove（dataStringToProcess.Length - 1，1）;
if（dataStringToProcess.Length> 0）
{
列表< string> batchDataStringList = GetDataStringInBatches（dataStringToProcess）;
//TimeSpan.FromMinutes(0） - 使事务范围为无限大。
使用（TransactionScope transaction = TransactionScopeFactory.GetTransactionScope（TimeSpan.FromMinutes（0）））
{
foreach（stringDataString in batchDataStringList）
{
PerformDatabaseOperation（dataString，UserID）;
}
transaction.Complete（）;
}
}
}
私人列表< string> GetDataStringInBatches（StringBuilder dataStringToProcess）
{
列表< string> batchDataStringList = new List< string>（）;
int loopCounter = 0;
string currentBatchString = string.Empty;
int numberOfRecordsinBacth = 5000;
int sizeOfTheBatch = 0;
列表< string> individualEntriesList = new List< string>（）;
string dataString = string.Empty;
if（dataStringToProcess！= null）
{
dataString = dataStringToProcess.ToString（）;
}
individualEntriesList.AddRange（dataString.Split（new char [] {'|'}））;
for（loopCounter = 0; loopCounter< individualEntriesList.Count; loopCounter ++）
{
if（String.IsNullOrEmpty（currentBatchString））
{
currentBatchString = System.Convert.ToString（individualEntriesList [loopCounter]）;
}
else
{
currentBatchString = currentBatchString +|+ System.Convert.ToString（individualEntriesList [loopCounter]）;
}
sizeOfTheBatch = sizeOfTheBatch + 1;
if（sizeOfTheBatch == numberOfRecordsinBacth）
{
batchDataStringList.Add（currentBatchString）;
sizeOfTheBatch = 0;
currentBatchString = String.Empty;
}
}
返回batchDataStringList;
private void PerformDatabaseOperation（string dataStringToProcess，int UserID）
{
SqlConnection mySqlConnection = new SqlConnection（data source = myServer; initial catalog = myDB; Integrated Security = SSPI; Connection Timeout = 0）;
SqlCommand mySqlCommand = new SqlCommand（aspInsertUSAZipCode，mySqlConnection）;
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.Add（@ DataRows，dataStringToProcess.ToString（））;
mySqlCommand.Parameters.Add（@ currDate，DateTime.Now）;
mySqlCommand.Parameters.Add（@ userID，UserID）;
mySqlCommand.Parameters.Add（@ CountOfUnchangedZipCode，1000）;
mySqlCommand.CommandTimeout = 0;
mySqlConnection.Open（）;
int numberOfRows = mySqlCommand.ExecuteNonQuery（）;
}
Dev Env：Visual Studion 2005
框架：.Net 3.0
DB：SQL Server 2005
当我运行查询SELECT [Size]，Max_Size，Data_Space_Id，[File_Id]，Type_Desc，[Name] FROM MyDB.sys.database_files WHERE data_space_id = 0 - 说大小（日志）是128
更新
我们的应用程序中使用了三个不同的数据库。一个用于数据，一个用于历史记录，一个用于记录。当我在上面的连接字符串中放入了争议= false的时候，它正在工作。但是在我的开发环境中。我怀疑是否也会在生产中工作。任何有潜在风险的想法？
感谢
Lijo
有可能 c> aspInsertUSAZipCode 与链接服务器交互？如果这样做，它将尝试并将您当前的本地事务推广到分布式事务（在服务器和链接的服务器之间保留事务完整性）。
可能需要如果远程服务器上的MSDTC无法配置分发事务，则在事务之外执行此操作。我认为最好的办法是创建一个临时表，然后创建一个临时表，然后再创建一个临时表，然后执行 aspInsertUSAZipCode 使用服务器上的临时表。您可能需要使用游标。
临时表的目的是，如果出现问题，表将在您的连接终止时被删除。 p>
I am getting a MSDTC exception in a Transaction in a C# application. The functionality is to upload one lakh (one hundred thousand) zipcode records into database tables after reading from a csv file. This operation is done in around 20 batch database operations (each batch containing 5000 records). The functionality is working fine, if I don’t use transaction.
The interesting part is that other functionalities that uses transactions are able to complete their transactions. This leads me to an assumption that the exception message is a misleading one.
Any thoughts on what could be the issue?
Exception: "Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool."
Source: System.Transactions
Inner Exception: "The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024)"
Note: There is a for loop inside the transaction. Is it causing any issue?
The actual requirement is: There are some existing zipcodes in zipcode table. Each month the administrator will upload the new zipcode csv file. The new items from csv get inserted. Zipcodes which are not available in csv (but present in database) are considered to be retired and is to be deleted. The list of retired zip codes is to be returned to the User Interface. The newly added zip codes also need to be returned.
private void ProcessZipCodes(StringBuilder dataStringToProcess, int UserID)
{
int CountOfUnchangedZipCode = 0;
string strRetiredZipCode = "";
string strNewZipCode = "";
dataStringToProcess.Remove(dataStringToProcess.Length - 1, 1);
if (dataStringToProcess.Length > 0)
{
List<string> batchDataStringList = GetDataStringInBatches(dataStringToProcess);
//TimeSpan.FromMinutes(0) - to make transaction scope as infinite.
using (TransactionScope transaction = TransactionScopeFactory.GetTransactionScope(TimeSpan.FromMinutes(0)))
{
foreach (string dataString in batchDataStringList)
{
PerformDatabaseOperation(dataString, UserID);
}
transaction.Complete();
}
}
}
private List<string> GetDataStringInBatches(StringBuilder dataStringToProcess)
{
List<string> batchDataStringList = new List<string>();
int loopCounter = 0;
string currentBatchString = string.Empty;
int numberOfRecordsinBacth = 5000;
int sizeOfTheBatch = 0;
List<string> individualEntriesList = new List<string>();
string dataString = string.Empty;
if (dataStringToProcess != null)
{
dataString = dataStringToProcess.ToString();
}
individualEntriesList.AddRange(dataString.Split(new char[] { '|' }));
for (loopCounter = 0; loopCounter < individualEntriesList.Count; loopCounter++)
{
if (String.IsNullOrEmpty(currentBatchString))
{
currentBatchString = System.Convert.ToString(individualEntriesList[loopCounter]);
}
else
{
currentBatchString = currentBatchString+"|"+System.Convert.ToString(individualEntriesList[loopCounter]);
}
sizeOfTheBatch = sizeOfTheBatch + 1;
if (sizeOfTheBatch == numberOfRecordsinBacth)
{
batchDataStringList.Add(currentBatchString);
sizeOfTheBatch = 0;
currentBatchString = String.Empty;
}
}
return batchDataStringList;
}
private void PerformDatabaseOperation(string dataStringToProcess, int UserID)
{
SqlConnection mySqlConnection = new SqlConnection("data source=myServer;initial catalog=myDB; Integrated Security=SSPI; Connection Timeout=0");
SqlCommand mySqlCommand = new SqlCommand("aspInsertUSAZipCode", mySqlConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.Add("@DataRows", dataStringToProcess.ToString());
mySqlCommand.Parameters.Add("@currDate", DateTime.Now);
mySqlCommand.Parameters.Add("@userID", UserID);
mySqlCommand.Parameters.Add("@CountOfUnchangedZipCode", 1000);
mySqlCommand.CommandTimeout = 0;
mySqlConnection.Open();
int numberOfRows = mySqlCommand.ExecuteNonQuery();
}
Dev Env: Visual Studion 2005
Framework: .Net 3.0
DB: SQL Server 2005
When I run the query SELECT [Size],Max_Size,Data_Space_Id,[File_Id],Type_Desc,[Name] FROM MyDB.sys.database_files WHERE data_space_id = 0 --It says the size (of log) is 128
UPDATE We have three different databases used in our application. One for data, one for history and one for logging. When I put enlist = false in the above connectionstring, for the time being, it is working. But it is in my development environment. I am skeptic about whether it will work in production also. Any thought on potential risks?
Thanks
Lijo
Is it possible that aspInsertUSAZipCode interacts with a linked server? If it does then it will try and promote your current local transaction to a distributed transaction (with transactional integrity preserved between your server and the linked server).
It may be necessary to do this outside of a transaction, if the MSDTC on the remote server cannot be configured for distributed transactions. I think the best way to do this is to create a temporary table and then SqlBulkCopy your records into it and then execute aspInsertUSAZipCode using the temporary table on the server. You may need to use a cursor.
The purpose of the temporary table is so that if something goes wrong, the table is removed at the termination of your connection.
这篇关于MSDTC异常交易：C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
