Database Blocking Redux

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

Jonathan Wood

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
 
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
____________________________________________________________________________________________
 
Once the rowset population is complete--the Fill method is has completed
or DataReader.Read returns False, any share locks are released.
Click to expand...

Cool. That latter approach seems to be the one I'm zeroing in on.

Thanks.

Jonathan
 
Back
Top