Re: Database Blocking Redux

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" <jwood@xxxxxxxxxxxxxxxx> wrote in message news:OYfV8FNfJHA.5724@xxxxxxxxxxxxxxxxxxxxxxx
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

.

Relevant Pages

  • Re: row vs page locking...
    ... so they automatically escalate to page level locks. ... 'Lock Escalation' - see Books Online. ... SQL Server 2005 is going to have a Snapshot ... Good old fashioned locking is less sexy, but I find, more productive! ...
    (microsoft.public.sqlserver.server)
  • Re: Repeatable read. What is it exactly...
    ... SQL Server will hold locks on the data you have read ... can change change the data until you commit or rollback. ... SQL Server locks the data you read. ... > as transaction Isolation levels go. ...
    (microsoft.public.sqlserver.server)
  • Re: Locking in SQL Server and Oracle
    ... I am glad you would like to stay with SQL Server :-) ... > hard it would be to convert to Oracle during our next PeopleSoft upgrade. ... > had thought that since databases are databases, tables are tables, and SQL ... > uses row-level locks exclusively. ...
    (microsoft.public.sqlserver.server)
  • Re: Need help with DAO and SQL Server locking issue
    ... I will forward your response to the client. ... > Absolutely nobody uses DAO to work with SQL Server, ... I'd advise moving code that uses recordsets ... >>Locks are being placed on tables inside of a transaction each time the ...
    (microsoft.public.sqlserver.odbc)
  • Re: question regarding truncate operation
    ... pages within it would be marked for deallocation. ... Meaning if some new ... As long as you are in a transaction, the extents are locked, so they ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.programming)