Shulou(Shulou.com)06/01 Report--

Types of resources that SQL Server can lock

SQL Server can lock different types of resources. These resource types that can be locked include: RIDs or key (keys) (row level), page (pages), object (objects) (for example, table), database (databases), and others. Rows are located in the page and are also physical blocks of data that contain table or index data. You should first be familiar with these resource types, and at a more advanced stage, you may be familiar with other locked resource types, such as extents, allocation units, heaps, or B-tree (B-trees).

In order to acquire a lock of a specific resource type, your transaction must first acquire the intention lock of the same pattern at a higher level of granularity. For example, in order to obtain an exclusive lock on a row, your transaction must first apply for an intention exclusive lock on the page on which the row is located and on the object that owns the page. Similarly, in order to obtain a shared lock at a specific level of granularity, your transaction must first apply for an intended shared lock at a higher level of granularity. The purpose of intention locks is to effectively detect incompatible lock requests and prevent them from being granted at a higher level of granularity. For example, one transaction holds a row lock, while another transaction requests an incompatible lock mode on the entire page or table where the row is located, because the intention lock acquired by the first transaction on the page and table is easy for SQL Server to identify conflicts. Intentional locks do not interfere with lock requests at the row-level granularity. For example, an intentional lock on a page does not prevent other transactions from acquiring incompatible lock modes on the rows of that page.

The lock compatibility table is as follows:

Request pattern Grant exclusive Lock (X) Grant shared Lock (S) Grant intention exclusive Lock (IX) Grant intention shared Lock (IS) grant exclusive lock request? No! no!

Do you want to grant a shared lock request? Is the intention exclusive lock request granted? Is the intended shared lock request granted? Whether it is or not.

SQL Server dynamically determines which resource type to lock. Of course, for ideal concurrency, it is best to lock only the resources that need to be locked, that is, only the rows that are affected. However, locking requires memory resources and internal administrative overhead. Therefore, when SQL Server chooses which resource type to lock, consider both concurrency and system resources.

SQL Server will first apply for fine-grained (fine-grained) locks (such as row or page locks) and, in certain cases, try to upgrade fine-grained locks to more coarse-grained (coarse-grained) locks (such as table locks). For example, when a statement acquires at least 5000 locks, and then for every 1250 new locks, lock escalation (lock escalation) is triggered if the lock upgrade attempt was unsuccessful.

In SQL Server 2008 and SQL Server 2012, you can control the behavior of lock escalation by setting a table option using the ALTER TABLE statement. If you prefer, you can also disable lock escalation, or decide to take place lock escalation at the table level (default) or partition level. A table can be physically organized into multiple smaller units called partitions. )

Translated from

Itzik Ben-Gan

Microsoft SQL Server 2012 T-SQL Fundamentals

Page 302

Lockable Resource Types

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.