Row locking vs Page locking


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Row locking vs Page locking

  1. #1
    Jonni Faiga Guest

    Row locking vs Page locking


    Hi,
    When performing an insert of one row into a table, it looks like SQL Server
    does an exclusive page lock by default. If this is the case, then why? Even
    if all new inserts are on the same page, it could still do just a row lock?

    Also do updates of a single row also do a page lock by default?
    Does this mean I should write all my code with hints, eg:
    INSERT INTO TableXYZ with (paglock) ([L_ID],[L_Description])
    VALUES (@ID,@Description)

    Share on Google+

  2. #2
    Rob Vieira Guest

    Re: Row locking vs Page locking

    What kind of lock SQL Server will choose will vary widely depending on
    things such as:

    How wide the rows are
    How many rows are currently in the page
    How many rows are expected to be affected on the current page

    I definitely would NOT write your code with hints except for occasional odd
    situations where SQL Server has just plain blown it or where you are doing
    something atypical (SQL Server's choice will be based on the 80% side of the
    80-20 rule).

    The balance, BTW, is the fact that locking at the page level takes slightly
    less in the way of resources - so it may actually cause less contension to
    get in and out at the page level quickly rather than take the time to drill
    down to the row level on the lock.

    --
    Rob Vieira MCSD, MCT, MCDBA
    www.ProfessionalSQL.com

    "Jonni Faiga" <jonni@bigfoot.com> wrote in message
    news:3a6e854f$1@news.devx.com...
    >
    > Hi,
    > When performing an insert of one row into a table, it looks like SQL

    Server
    > does an exclusive page lock by default. If this is the case, then why?

    Even
    > if all new inserts are on the same page, it could still do just a row

    lock?
    >
    > Also do updates of a single row also do a page lock by default?
    > Does this mean I should write all my code with hints, eg:
    > INSERT INTO TableXYZ with (paglock) ([L_ID],[L_Description])
    > VALUES (@ID,@Description)
    >



    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center