What is locking?
To manage multi user access to data while maintaining data consistency, SQL Server uses a locking mechanism for data. Locks occur at three different levels and can be of three different types. A lock can be applied at a row, page, or table level.
The main decision threshold occurs at approximately three percent to five percent. If SQL Server determines that a query requires locks on three percent to five percent of the rows on a given page, it acquires a page-level lock. Similarly, if SQL Server determines that a query requires locks on three percent to five percent of the pages in a given table, it acquires a table-level lock. Because it is not always possible to accurately predict the percentage of rows or pages that require a lock, SQL Server can automatically promote from fine-grained locks to a coarser level of lock. This process is called lock escalation.
To manage multi user access to data while maintaining data consistency, SQL Server uses a locking mechanism for data. Locks occur at three different levels and can be of three different types. A lock can be applied at a row, page, or table level.
The main decision threshold occurs at approximately three percent to five percent. If SQL Server determines that a query requires locks on three percent to five percent of the rows on a given page, it acquires a page-level lock. Similarly, if SQL Server determines that a query requires locks on three percent to five percent of the pages in a given table, it acquires a table-level lock. Because it is not always possible to accurately predict the percentage of rows or pages that require a lock, SQL Server can automatically promote from fine-grained locks to a coarser level of lock. This process is called lock escalation.
