Datareader and Isolation Level

  • Thread starter Thread starter Tom Pester
  • Start date Start date
T

Tom Pester

Hi,

I am using a datareader that has been filled with a whole table (select *
from table).
I am processing every record in a loop which takes some time (this is bad
design I know, but its good enough for now)

I think the default isolation level is "read committed" so during this
period no updates/deletes are possible.
Read committed is to strict for what I want so I rewrote the query like this
: select * from table (nolock).

Furthermore I found out that if an error occurs in the loop, so the
connection isnt closed, the locks are held on the table until the .net
garbage collecter kicks in.

My question is :
- Can I somehow change the default isolation level, in the connection string
for example?
- Is there a property in the ADO.NET command or datareader class that lets
me set this programmatically.
(I found the property isolationlevel but couldn't get it to work)

All the best,
Tom
 
1) The DataReader opens a data stream that blocks the connection until the
last row is read.
2) While you're sitting on rows, portions of the database are sharelocked on
the server.
3) There are a number of more viable ways to change all the rows in a table
without bringing them to the server. That's what stored procedures are for.
4) Add a Cn.Close in the Try/Catch/Finally block to ensure that the
connection is closed if something goes wrong. Make doubly sure your
connection is closed before exiting.
5) If you create a Transaction, you can set the IsolationLevel, but I don't
think this will help a bit.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Lo again William. It seems you are helping every person in the world with an
ADOproblem. Nice quest :)

I followed your advice from our last discussion so I understand what you say
in your answer and now I always make sure I close the connection.

But this question is about isolation level. If I write the query like this
"select * from table (NOLOCK)" then I can manipulate the data in the table
while the datareader is busy with it in an asp.net page (looping through
it).
This scenario doesn't occur much in real life since we want consistent
results. But I knew that the page would handle some data in a few minutes
which wasn't necessary (the rows were at the end of the table)

So I tried to delete these row so the page wouldn't handle them. Since the
default isolation level is read committed I couldn't do a delete of these
rows in the query analyzer.
Now that I add the NOLOCK I can do it. I suppose this resembles "read
uncommitted" behavior.

My question is thus :
- Can I somehow change the default isolation level, in the connectionstring
for example?
- Is there a property in the ADO.NET command or datareader class that lets
me set this programmatically.
(I found the property isolationlevel but couldn't get it to work)

( A positive side effect of the NOLOCK is that if I forget to close the
connection the rows aren't sharelocked which is the case without the nolock
and we dont have to wait for the garbage collector.
Of course, forgetting to close a connection results in eternal damnation )


----- Original Message -----
From: "William (Bill) Vaughn" <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
Sent: Wednesday, October 29, 2003 1:22 AM
Subject: Re: Datareader and Isolation Level
 
you have a couple options,

1) set the tansaction level in the query. you will see phantom records
(dirty reads)

set transaction isolation level read uncommited
select *
from table
set transaction isolation level read commited

2) the one you use (dirty reads), which is really the same

select *
from table (nolock)

3) use browse mode, which snapshots the query on the server before returning
rows. this probably does exactly what you want, as you get valid data, and
the locks are released before the first row is returned to the client.

select *
from table
for browse

-- bruce (sqlwork.com)
 
Are you trying to use the same connection to post changes to the rowset
being browsed with the DataReader? This is not an option.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top