SQL Server stores rows on pages and pages on extents. Given the number of
locks to manage, it's rare for SQL Server to lock individual rows.
Ordinarily (depending on the query intent), pages are locked. A page is 8K
so it might contain several (dozen, hundred or thousand) rows. If SQL Server
finds there are too many pages locked, it escalates locks to the entire
extent and then to the entire table.
Once the rowset population is complete--the Fill method is has completed or
DataReader.Read returns False, any share locks are released. The Dataset is
always a "disconnected" set of data that can exist without benefit of a
Connection. Unlike ADO classic (COM-based ADO) where one created server-side
or updatable cursors, ADO.NET has no mechanism to hold rows on the server
with (at least) one exception--when you execute a query with a
Repeatable-Read Transaction. In this case, the entire rowset is locked until
the transaction is committed.
I discuss these operations at length in my book.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
"Jonathan Wood" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Based on my other thread, "Database Blocking -- Why?" I wonder if I could
> ask a few specific questions from someone who understands ADO/SQL better
> than I.
>
> 1. When reading rows using SqlDataReader, does reading the next row
> release the lock on the previous row? It sounds like the answer is no
> because several rows may be locked at a time. Could someone confirm?
>
> 2. How about DataSet? Is there a way to ensure any locks on the rows I've
> read get released?
>
> Thanks.
>
> Jonathan
>
Similar Threads
