Row Locking vs. Page Locking

August 1996 / Reviews / SQL Server: The Sequel's Better / Row Locking vs. Page Locking

New in Microsoft SQL Server 6.5 is the option to use row locking rather than page locking on insert operations. Locking mechanisms prevent users from changing data that another user has already changed, until the changes are committed. Row locking locks only the row that is being changed . Page locking locks an entire physical storage unit (2 KB in SQL Server), which may include unchanged records adjacent to the changed record.
Such fine granularity minimizes the possibility of lock contention and improves throughput. However, page locking has its own benefits: Locking larger storage units requires fewer locks to manage and reduces system overhead. F or updates of records in large tables, locks will likely be spread over a large-enough area and held for short-enough durations to minimize the potential drawbacks of page locking. But when multiple users insert records into a table in sequential order, several consecutive records are likely to be targeted for the same page, resulting in serious bottlenecks.

The Locking Competition
illustration_link (17 Kbytes)
Entire physical storage unit ("page") is locked during update.

Go to previous article: Row Locking vs. Page Locking

Go to next article: Microsoft's Suite Victory

Matthew Wilson
My approach to software engineering is far more pragmatic than it is theoretical--and no language better exemplifies this than C++.

more...

BYTE Digest editors every month analyze and evaluate the best articles from Information Week, EE Times, Dr. Dobb's Journal, Network Computing, Sys Admin, and dozens of other CMP publications—bringing you critical news and information about wireless communication, computer security, software development, embedded systems, and more!

Find out more

BYTE CD-ROM

NOW, on one CD-ROM, you can instantly access more than 8 years of BYTE.

The Best of BYTE
Volume 1: Programming Languages

In this issue of Best of BYTE, we bring together some of the leading programming language designers and implementors...

Copyright © 2005 CMP Media LLC, Privacy Policy, Your California Privacy rights, Terms of Service
Site comments: webmaster@byte.com
SDMG Web Sites: BYTE.com, C/C++ Users Journal, Dr. Dobb's Journal, MSDN Magazine, New Architect, SD Expo, SD Magazine, Sys Admin, The Perl Journal, UnixReview.com, Windows Developer Network