Minimizing table locking on a SqlDataReader loop?

Question

I have the following scenario (exception handling removed, code simplified):

SqlDataReader rdr = executeQuery(query);
while (rdr.Read()) {
   //There are thousands of records, so this happens thousands of times
   handleRecord(rdr); //Takes a couple of seconds
}
rdr.Close();

So, the table (running on an overcrowded Sql Server 2000) the query involves has shared locks for a couple of hours or more, depending on the amount of records.

These locks sometimes affect other applications querying this database so I've been asked to remove the locks as soon as possible.

So, short of the obvious

List<Record> rList = new List<Record>();
SqlDataReader rdr = executeQuery(query);
while (rdr.Read()) {
   //There are thousands of records, so this happens thousands of times
   storeRecord(rList,rdr); //Takes milliseconds
}
rdr.Close();
foreach (Record r in rList) {
    handleRecord(r);
}

that would put a limit on the amount of records I can handle on the machine's memory, is there any other alternative?

(This app I'm slowly moving to Dapper.NET, so it's already a dependency for other parts of the code, in case there's something in Dapper that could help with this scenario.)

And if the query is executed with a sql server query hint no-lock? — Me.Name, Jun 19 '12 at 13:39
I want the data as up-to-date as possible, I understand that using the hint might change that under certain circumstances. — Vinko Vrsalovic, Jun 19 '12 at 14:05
@Blam By up-to-date I meant "I want the data to be 100% valid" with NOLOCK I could be reading uncommited transactions. — Vinko Vrsalovic, Jun 19 '12 at 14:51
And it turns out that I could read not only uncommitted transactions, but get duplicate rows or even have the query error out. http://stackoverflow.com/questions/1452996/is-the-nolock-sql-server-hint-bad-practice — Vinko Vrsalovic, Jun 19 '12 at 14:58
Select into a #temp table. And a general review of your locks. Maybe some row locks. Make sure you are updating tables in the same order. What does handleRecord look like? — paparazzo, Jun 19 '12 at 15:56
Like Blam said, go with selecting into a #temp table. Since this would run within sql server, it should run quickly, releasing the locks. You then can select from the temp table without worrying about locks. This moves the restriction from your client machine to the sql server. — Rich McCollister, Jun 19 '12 at 18:40

score 1 · Answer 1 · answered Jan 18 '18 at 14:09

It's an old topic but here's a trick I sometimes use:

Order your sql query on primary key.
Read top ## records.
Read/buffer those ## records.
Use your buffer to do processing.
Remember the highest id.

Go back to 2 with one modification: You read the top ## records WHERE id > highest id.

In this way you can read and process batches of data.

score 0 · Answer 2 · answered Jun 22 '12 at 13:31

0

Will try my comment as an answer as no recent comments.

Select into a #temp table. And a general review of your locks. Maybe some row locks. Make sure you are updating tables in the same order. What does handleRecord look like?in

answered Jun 22 '12 at 13:31

paparazzo

44,497
23
105
176

Minimizing table locking on a SqlDataReader loop?

2 Answers2