How can I fix Transaction with Page Lock Ploblem ?
My Application has ploblem while one of client save Order Document.
Another client cannot select data from Order table until saving process is complete.
I develop C# window application with Microsoft SQL Server 2005 sp3.
I used Transaction and Isolation=ReadUncommit to save Order Document.
My Application has 3 table is OrderHd, OrderLine and OrderSerial.
Record of OrderSerial per 1 Order Document about 1,000-5,000 record
//My Simulate Saving Process
string mstrConnStr = "Data Source=serverzx; Initial Catalog=Test;User ID=sa;Password=x2y2;";
System.Data.SqlClient.SqlConnection conn = new SqlConnection(mstrConnStr);
System.Data.SqlClient.SqlCommand cmd1 = new SqlCommand("Insert Into OrderHd (OrderID , Code,Name) values (@Para0,@Para1,@Para2)",conn);
System.Data.SqlClient.SqlCommand cmd2 = new SqlCommand("Insert Into OrderLine (OrderLineID , OrderID,Detail) values (@Para0,@Para1,@Para2)",conn);
System.Data.SqlClient.SqlTransaction tr = null;
try
{
conn.Open();
cmd1.Parameters.Clear();
tr = conn.BeginTransaction(IsolationLevel.ReadUncommitted);
string strCode = DateTime.Now.ToString("yyyyMMdd : HHmmss");
string strHead = Guid.NewGuid().ToString();
cmd1.Transaction=tr;
cmd2.Transaction=tr;
cmd1.Parameters.Add("@Para0",strHead);
cmd1.Parameters.Add("@Para1",strCode);
cmd1.Parameters.Add("@Para2","Name "+strCode);
cmd1.ExecuteNonQuery();
for (int i=0;i<5000;i++)
{
string strItem = Guid.NewGuid().ToString();
cmd2.Parameters.Clear();
cmd2.Parameters.Add("@Para0",strItem);
cmd2.Parameters.Add("@Para1",strHead);
cmd2.Parameters.Add("@Para2","Detail "+i.ToString());
cmd2.ExecuteNonQuery();
System.Threading.Thread.Sleep(10);
}
tr.Commit();
}
catch (Exception ex)
{
tr.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}