Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

What is page locking

Posted on 1998-12-14
4
Medium Priority
?
176 Views
Last Modified: 2010-07-27
What is page locking in SQL and how large is a page?
Has a page a fix size e.g. 4KB or 8KB or a number of records?
0
Comment
Question by:flmexpert
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
Victor Spiridonov earned 0 total points
ID: 1092277
Page locking is like putting a flag on the page. The flag, depending on lock can mean: 'Don't allow any one to access this page' (in case of update lock) or for example, 'Allow to read, but don't allow to update' (in case of shared select lock). There are several lock types, that can be placed on a page,you can read about them in SQL Books Online. Page size in Sql Server is 2Kb.
0
 
LVL 2

Expert Comment

by:tschill120198
ID: 1092278
SQL 7.0 increases the page size to 8K.
0
 

Author Comment

by:flmexpert
ID: 1092279
SQL 7.0 uses record locking and why should it use also pages ?

Also there is a problem:
If i have a record with a size of 3 KB and
make "update ... where <this record>".
SQL 7 locks only this record and SQL 6.5 ?
Locks it this record with two 2 KB pages ?

0
 
LVL 2

Expert Comment

by:tschill120198
ID: 1092280
This is called "lock escalation", where SQL converts many fine-grain locks into fewer course-grain locks to reduce the overhead of managing the locks...  

Resources that it can lock (from smaller to larger granularity) are: RID -> Key -> Page -> Extent -> Table -> Database.

Look in BOL under "lock escalation".

As for the update query locking 2 pages, since the record is 3K (and therefore resides on 2 pages), it would make sense that both pages for the record be locked.  Or am I missing something?

0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question