Page level lock in SQL 2008

150 pts.
Tags:
SQL 2008
SQL Server 2008
What is the meaning of page level lock in microsoft SQL 2008 enterprise edition? How many records are locked?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Sql Server stores data on 8-kilobyte data pages. A page is one of the levels of granularity when locking resources. Other levels include row, extent, table.

The database acquires the appropriate level of locks to minimize the cost, so instead of locking individual rows it could decide to lock the complete page, to minimize the number of locks needed, or it could even decide to lock a complete table, depending on the operation being performed.

Here is a list of the different granularity levels.

<a href=”http://msdn.microsoft.com/en-us/library/ms189849.aspx”>Lock Granularity and Hierarchies</a>

-CarlosDL

—————————

Depending on the size of the rows in the table it could be one row, or as many as 65536 rows (if each row was a single bit column and there were no other columns).

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    Note that you can convert "page level locks" to "row level locks" with the (very ugly) kludge of setting the page fill factor on the table to 1, which will cause SQL Server to effectively create a new page for each row of data. This was the only way to achieve row locking in Sybase in the past (maybe still?). This is not generally recommended, but in some cases where storage is not an issue, and concurrent record access is critical, it can be used to maximize concurrency.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following