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.SqlClient.SqlConnection = New
System.Data.SqlClient.SqlConnection("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(strSQL,Conn)
Dim DR As System.Data.SqlClient.SqlDataReader
Try
DR = SqlCmd.ExecuteReader
Do While DR.Read()
<only reading with DR.item("columnname")>
Loop
Catch ex As Exception
errorhandler(ex.ToString)
Finally
If DR.IsClosed = False Then DR.Close()
SqlCmd.Dispose()
End TryNils Pommerien (fishinet@.gmx.de) writes:
> 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"
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/techin.../2000/books.asp|||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:Xns93ED17B437BFYazorman@.127.0.0.1...
> Nils Pommerien (fishinet@.gmx.de) writes:
> > 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"
> 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.|||Oscar Santiesteban Jr. (oscarsantiesteban@.worldnet.att.net) writes:
> Erland,
> Would a "SELECT * FROM TABLE WITH NOLOCK" work in his case? Assuming he
> does not care if the data changes.
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/techin.../2000/books.asp|||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!|||Ray Higdon (rayhigdon@.higdonconsulting.com) writes:
> 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?
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/techin.../2000/books.asp
