Click here to Skip to main content
15,741,892 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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();
}
Posted
Updated 13-Dec-09 16:03pm
v2

I think what you are seeing is that the table is being locked during the one by one row INSERT iterations.

You need to make this operation faster, why did you use the Thread.Sleep ?

Look at SqlBulkCopy for the 5000 inserts or other bulk copy options. One idea might be building a temp table or tables with the data you need and THEN doing the insert based on a select.

And where you can use a date datatype please use it - column "Code" should be a datetime type. This won't speed anything up, just good practice.
 
Share this answer
 
Oh Sorry.I use Thread.Sleep for represent some update process time.
and I try to optimize my saving process.
it about 0.05-0.1 ms per record (about 3-5 minute per Document)
 
Share this answer
 
Let SQLServer worry about process/update time.

Check your indexes to see if they can be optimised - indexes on the tables are a critical part of the speed of inserts and updates

If you actually are creating one parent and then 5000 children you may end up with a large transaction log causing both disk space (and log) space problems.

If you can avoid inserting row by row (look up non-logged bulk insert operations) do that.
 
Share this answer
 
Hi DABBee, thanks a lot for your discussion.
But this code is my simulate Saveing Process.
if you can create table OrderHd , and OrderLine
and Copy this code to run on your machine
While you running Saving Process
try to use Query analyser to select some row from Table OrderLine
It will not reponse until Saving Process is complete.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900