Hi,
We using remoting concept to perform our function in SqlServer.
We use One Broker(Server) and more than 100 Clients.
So my problem is that when more than one request is sent from the clients to one broker
the each and every clients request gets processed parallely.
public bool UpdateData(String ClientErrorInfo, String OrderIDSDGXML, int UserID)
{
bool retVal = false;
SqlConnection Conn = null;
Conn = new SqlConnection(dbconnRMS);
SqlTransaction UpdateTransaction = null;
try
{
if (OrderIDSDGXML != "")
{
Conn.Open();
SqlCommand sqlCmdTakeOwnership = new SqlCommand("UpdateProcedure");
sqlCmdTakeOwnership.Connection = Conn;
sqlCmdTakeOwnership.CommandType = CommandType.StoredProcedure;
UpdateTransaction = Conn.BeginTransaction();
string[] OrderIds;
OrderIds = OrderIDSDGXML.Split(',');
for (int i = 0; i < OrderIds.Length - 1; i++)
{
Int64 CurrentOrderIdInArray = Convert.ToInt64(OrderIds.GetValue(i).ToString());
sqlCmdTakeOwnership.Transaction = UpdateTransaction;
sqlCmdTakeOwnership.Parameters.Clear();
sqlCmdTakeOwnership.Parameters.Add("@OrderID", SqlDbType.BigInt).Value = CurrentOrderIdInArray;
sqlCmdTakeOwnership.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
sqlCmdTakeOwnership.Parameters.Add("@RevisionDetails", SqlDbType.VarChar, 4000).Value = "";
sqlCmdTakeOwnership.ExecuteNonQuery();
}
OrderIds = null;
if(UpdateTransaction!=null)
UpdateTransaction.Commit();
}
retVal = true;
if (Conn.State == ConnectionState.Open)
Conn.Close();
}
catch (Exception ex)
{
if(UpdateTransaction!=null)
UpdateTransaction.Rollback();
retVal=false;
if (Conn.State == ConnectionState.Open)
Conn.Close();
}
return retVal;
}
So If three users accessing the broker at the same time When First clients request is in the middle the second clients request starts and comes the third clients request.
So the first clients request gets completed successfully but the second and third requests get transaction as follows.
The current transaction cannot be committed and cannot support operations that write to the log file. Rollback the transaction. Uncommittable transaction is detected at the end of the batch.The transaction is rolled back.
Please help me to get rid of this problem.