Re: Data reader / Serverside cursor
- From: "William Vaughn" <billvaNoSPAM@xxxxxxxxx>
- Date: Mon, 12 Nov 2007 12:03:32 -0800
See >>>
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"rbrowning1958" <RBrowning1958@xxxxxxxxx> wrote in message news:1194797600.941785.317030@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello,
New to ADO.NET so apologies in advance for repeating what I am sure
has been asked been before. I have read that a data reader is much
like a Server Side cursor.
Well, sort of. It's a read-only, forward-only "cursor" if it's anything. It's really a data stream. A "cursor" implies scrollability.
I understand this but have one questtion. I
use a SQL Command object to execute a Select statement, then a data
reader to retrieve the results one at a time. When is the result set
generated? When the command's executeReader is run?
Again, sort of. When the SQL is executed SQL Server starts a connection-dedicated "agent" who runs off and starts finding rows to return to the client. When the first few rows (the CacheSize) are found, the agent tells the client that there is a resultset available to process. At that point the server-side process (the agent) blocks waiting for the client to start fetching the rows. As rows are processed on the client, the server agent goes back to work finding more rows.
For example if my
select says something like "Select * from customers" - what happens if
other users change the customers table before my datareader has read
the results? I assume they are not part of my result set and that the
result was actually generated by the executeReader and not
incremnetally as I use the dataReader. Can someone confirm this,
please? If this is implementation dependent I'm talkign SQL Server.
So, in this case, suppose you fetch the first 5 rows of the rowset (that could have several hundred rows). The rest of the potential rowset has a "share" lock on it and can be changed by other clients. Any SELECT gets a snapshot of the data as it was when the agent is ready to retrieve that row. It can change an instant later and any number of times thereafter--or before the agent gets to that row. To "lock down" the rows, you're going to need a Transaction with a "repeatable read" option set. This creates a "pessimistic" lock to prevent these changes.
All of this detail boils down to the approach you take toward the design and implementation of your shared data model. I discuss this in detail in my book.
Ta
Ray
.
- References:
- Data reader / Serverside cursor
- From: rbrowning1958
- Data reader / Serverside cursor
- Prev by Date: Re: Saving a (whole) datatable in SQL Server 2000
- Next by Date: Re: sql server setting concat_null_yields_null
- Previous by thread: Data reader / Serverside cursor
- Next by thread: data cache invalidation: the old sql 2000 polling way or the new sql 2005 notification way?
- Index(es):
Relevant Pages
|
