Record blocking when attempting to update approx 5

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Record blocking when attempting to update approx 5

Author

Topic

AceWare
Starting Member

2 Posts

Posted - 2012-09-24 : 20:30:25

Multiposted on social.technet.microsoft.com/Forums/en-US/sqlnetfx

The code below is very preliminary test code to see if we can use clr sql to update grain handler records.
It is a simple test at this stage to see if I can loop thru approx 5000 records updating an arbitary field for each record.
Currently the batch pricing process via a front end is taking 45 minutes.
It has a lot of fairly convoluted logic that does not suit being written in T-SQL.
I will use stored procedures with T-SQL at the beginning and end of the process where it suits.
Once in production, the batch process is intended to be run stand alone by a single user - if it experiences locking issues from other users of the system - I just intend to abort the process and issue a message to the user that other user must not be using the system.
The scale of the operation allows this to be feasible - ie I do not intend to cope with record locks beyond warning other users to get out of the system.

The problem I am experiencing is infinite time out on updates for the same specific set of records.
I am the only user of the test database being used.
The same set of records experiences a time out in the update each time.
(We do not experience any record lock problems when using the existing front end batch code.)

At the end of the reader loop I can specifically update the problem records - the timeout problem only occurs when attempting to update within the loop. You will see lines of code where I successfully update the problem records one by one.

I am guessing that I must be locking multiple records within a page block (don't really know what I am talking about here)

Here are the counter numbers and id fields of the problem records:
(this is what comes back from the SqlContext.Pipe.Send(strError); line of code, slightly tweaked up for readability)

Recd# Id
----- ------------
706 002-21078503
718 002-21259566
1133 003-00452040
1149 002-19605577
1263 00000433
2117 002-21193694
2133 002-21236494
2507 002-21190216
2540 002-21281006
3634 002-21203655
3913 002-21140890

and here is the code:
I admit I am being naughty using SQL login to sa - will fix soon.
Timeout is set to 1 just to get a quick turn around everytime I try a new idea - I have set it to 300 with no improvement.
I have tried using a separate connection for the ExecuteNonQuery command but this interestingly enough gave me more than twice as many problem records.
I have tried creating and disposing cmdUpdate every time around the reader loop - with no improvement.

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using Microsoft.CSharp;
using System.Data.SqlClient;
using System.Configuration;

public class TransformLoopProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TransLoop(DateTime dteProcDate, String strSQLServer, String strInitialCatalog)
{

int intRecordCount;
IDataRecord Record;
string strIDNumber;
string strConnection;
string strError = "";

strConnection = "Data Source=" + strSQLServer + ";Initial Catalog=" + strInitialCatalog + ";Persist Security Info=True;User ID=sa;Password=xxxx;MultipleActiveResultSets=True ";

SqlConnection connection = new SqlConnection(strConnection);

using (connection)
{
connection.Open();

SqlCommand CmdReadLoop = new SqlCommand("ace_ToBeTransformed", connection);
CmdReadLoop.CommandTimeout = 0;
SqlDataReader reader = CmdReadLoop.ExecuteReader();

SqlCommand cmdUpdate = new SqlCommand();
cmdUpdate.CommandTimeout = 1;
cmdUpdate.Connection = connection;
cmdUpdate.CommandText = "UPDATE tblAQImport SET Paddock = @Paddock WHERE IDNumber = @IDNumber;";

using (reader)
{
intRecordCount = 0;
while (reader.Read())
{

intRecordCount ++;
Record = (IDataRecord)reader;
strIDNumber = Convert.ToString(Record["IDNumber"]);
cmdUpdate.Parameters.AddWithValue("@Paddock", "test9");
cmdUpdate.Parameters.Add("@IDNumber", SqlDbType.VarChar);
cmdUpdate.Parameters["@IDNumber"].Value = strIDNumber;

try
{
cmdUpdate.ExecuteNonQuery();
}
catch (Exception e)
{
strError = strError + intRecordCount.ToString() + " " + strIDNumber + "\n";
}
cmdUpdate.Parameters.Clear();

}
}
reader.Close();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21078503'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21259566'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '003-00452040'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-19605577'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '00000433'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21193694'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21236494'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21190216'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21281006'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21203655'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21140890'";
cmdUpdate.ExecuteNonQuery();

cmdUpdate.Dispose();
}

connection.Close();
SqlContext.Pipe.Send(strError);

}
}

Any suggestions very gratefully received.

Tony Epton

rosie

AceWare
Starting Member

2 Posts

Posted - 2012-09-27 : 00:24:27

It's Fixed !

Following Mart's advice - I removed the isnull() function in the datasource (not shown anywhere here) and this halved the number of errors.

From examining the locks via various queries I gleaned from web searches I also realised that the outer read loop and the inner update were somehow blocking each other and this made me think that somehow the read and update processes were not staying synchronised.

The datasource had no sort order and I have seen this cause random results in the past.

On a hunch and with no hard logic, I added a sort order on IDnumber and this fixed the problem.

If anyone can theorise how the lack of a sort order caused this problem I would love to hear.

Many thanks

Tony

rosie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources