Datareader and Isolation Level

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

William
Tue Oct 28 18:22:38 CST 2003

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.
__________________________________

"Tom Pester" <tmspm@hotmail.com> wrote in message
news:ukyAc5anDHA.1708@TK2MSFTNGP12.phx.gbl...
> 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
>
>

Tom
Tue Oct 28 19:38:53 CST 2003

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" <billvaRemoveThis@nwlink.com>
Newsgroups: microsoft.public.dotnet.framework.adonet
Sent: Wednesday, October 29, 2003 1:22 AM
Subject: Re: Datareader and Isolation Level


> 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.
> __________________________________
>
> "Tom Pester" <tmspm@hotmail.com> wrote in message
> news:ukyAc5anDHA.1708@TK2MSFTNGP12.phx.gbl...
> > 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
> >
> >
>
>

bruce
Tue Oct 28 19:49:45 CST 2003

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)

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:#CVe5IbnDHA.1084@tk2msftngp13.phx.gbl...
> 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.
> __________________________________
>
> "Tom Pester" <tmspm@hotmail.com> wrote in message
> news:ukyAc5anDHA.1708@TK2MSFTNGP12.phx.gbl...
> > 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
> >
> >
>
>

William
Wed Oct 29 14:26:58 CST 2003

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.
__________________________________

"Tom Pester" <tmspm@hotmail.com> wrote in message
news:ufRkg1bnDHA.1960@TK2MSFTNGP12.phx.gbl...
> 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" <billvaRemoveThis@nwlink.com>
> Newsgroups: microsoft.public.dotnet.framework.adonet
> Sent: Wednesday, October 29, 2003 1:22 AM
> Subject: Re: Datareader and Isolation Level
>
>
> > 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.
> > __________________________________
> >
> > "Tom Pester" <tmspm@hotmail.com> wrote in message
> > news:ukyAc5anDHA.1708@TK2MSFTNGP12.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>