DataReader is blocking my tables

[Nils Pommerien]

Hello Newsgroup,

I have written a very short program to get information from a whole
table out of a database. the problem is that other people couldn't
work on this table during the process. It seems that my program locks
the whole table.
I used the DataReader from the .NET Framework. Can you please take a
look at the code and give me any solution??? Thank very much, Nils

Dim strSQL As String = "SELECT * FROM TESTTABLE"
Dim Conn As System.Data.Sql Client.SqlConne ction = New
System.Data.Sql Client.SqlConne ction("Data Source=1.1.1.1;
User ID=sa;Password= secret;Persist Security Info=True;
Initial Catalog=TestDB" )
Conn.Open()
Dim SqlCmd As SqlCommand = New SqlCommand(strS QL,Conn)
Dim DR As System.Data.Sql Client.SqlDataR eader
Try
DR = SqlCmd.ExecuteR eader
Do While DR.Read()
<only reading with DR.item("column name")>
Loop
Catch ex As Exception
errorhandler(ex .ToString)
Finally
If DR.IsClosed = False Then DR.Close()
SqlCmd.Dispose( )
End Try

[Erland Sommarskog]

Re: DataReader is blocking my tables

Nils Pommerien (fishinet@gmx.d e) writes:[color=blue]
> I have written a very short program to get information from a whole
> table out of a database. the problem is that other people couldn't
> work on this table during the process. It seems that my program locks
> the whole table.
> I used the DataReader from the .NET Framework. Can you please take a
> look at the code and give me any solution??? Thank very much, Nils
>
> Dim strSQL As String = "SELECT * FROM TESTTABLE"[/color]

Well, a SELECT * from a table without any WHERE condition will require
the entire table to be locked while you get the data. Other people
should still be able to read from the table, but updates will not
be possible.

If the table is small, this is not much of an issue, because unless
you go do some huge processing for each row. But if the table is big,
you will held the locks for quite some time. In such case I would
question the wise in getting so much data to the client.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Oscar Santiesteban Jr.]

Re: DataReader is blocking my tables

Erland,

Would a "SELECT * FROM TABLE WITH NOLOCK" work in his case? Assuming he
does not care if the data changes.

Oscar...

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns93ED17B 437BFYazorman@1 27.0.0.1...[color=blue]
> Nils Pommerien (fishinet@gmx.d e) writes:[color=green]
> > I have written a very short program to get information from a whole
> > table out of a database. the problem is that other people couldn't
> > work on this table during the process. It seems that my program locks
> > the whole table.
> > I used the DataReader from the .NET Framework. Can you please take a
> > look at the code and give me any solution??? Thank very much, Nils
> >
> > Dim strSQL As String = "SELECT * FROM TESTTABLE"[/color]
>
> Well, a SELECT * from a table without any WHERE condition will require
> the entire table to be locked while you get the data. Other people
> should still be able to read from the table, but updates will not
> be possible.
>
> If the table is small, this is not much of an issue, because unless
> you go do some huge processing for each row. But if the table is big,
> you will held the locks for quite some time. In such case I would
> question the wise in getting so much data to the client.
>[/color]

[Erland Sommarskog]

Re: DataReader is blocking my tables

Oscar Santiesteban Jr. (oscarsantieste ban@worldnet.at t.net) writes:[color=blue]
> Erland,
>
> Would a "SELECT * FROM TABLE WITH NOLOCK" work in his case? Assuming he
> does not care if the data changes.[/color]

No that would not work:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nolock'.

But:

SELECT * FROM tbl WITH (NOLOCK)

would of course the remove the locking problems. I didn't mention this
possibility, because I had a feeling that he his real problem one of:

1) He's getting far more rows than he has use for.
2) He's doing something long-winding between the retrieval of each row.

So the NOLOCK would only be a band-aid on a poor design.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ray Higdon]

Re: DataReader is blocking my tables

Erland, a question for you, I've noticed that the NOLOCK statement
generates more logical IO than selecting from the entire table, do you
know the cause?

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: DataReader is blocking my tables

Ray Higdon (rayhigdon@higd onconsulting.co m) writes:[color=blue]
> Erland, a question for you, I've noticed that the NOLOCK statement
> generates more logical IO than selecting from the entire table, do you
> know the cause?[/color]

Eh, could you provide a repro?


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp