Locks used by SQL Server to specify queries

Server

Objective
performance, how to maximize the performance of the database is the problem that every DBA needs to face, in a small amount of data running fly, and in a large number of data is slow as a snail, such things have you ever met it? How to improve the concurrency access performance of the database better? Yes, "lock", the key to solving the problem.

Preliminary knowledge
Lock mode, most content excerpt from SQL Server 2000 Books Online
If you are already familiar with the type of SQL Server lock, you can skip this chapter

Microsoft? SQL Server? 2000 has a multiple-granularity lock, allowing one transaction to lock different types of resources. To minimize the cost of locking, SQL Server automatically locks resources at a level appropriate to the task. Locking in a smaller granularity, such as a row, can increase concurrency but requires significant overhead, because if you lock many rows, you need to control more locks. Locking in a larger granularity, such as a table, is quite expensive because locking an entire table restricts access to any part of the table by other transactions, but requires less overhead because fewer locks are needed to maintain.

SQL Server can lock down the following resources (listed in order of increased granularity).
Resource Description
The RID line identifier. Used to lock a row in a table individually.
The row lock in the KEY index. Used to protect the range of keys in a serializable transaction.
PG 8,000-byte (KB) data page or index page.
EXT a set of eight data pages or index pages that are adjacent to each other.
TAB includes the entire table, including all data and indexes.
DB database

SQL Server locks resources using different lock modes, which determine how concurrent transactions access resources.

SQL Server uses the following resource lock modes.
Lock mode description
Shares (S) are used for operations that do not change or do not update data (read-only operations), such as a SELECT statement.
Update (U) is used in updatable resources. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and subsequent resource updates that might occur.
Exclusive (X) for data modification operations, such as INSERT, UPDATE, or DELETE. Make sure that the same resource does not have multiple updates at the same time.
Intent is used to establish a hierarchy of locks. The type of intent lock is: intent Share (IS), intent Exclusive (IX), and intent exclusive sharing (SIX).
Schemas are used when performing operations that depend on a table schema. Schema locks are of type: Schema modification (SCH-M) and schema Stability (sch-s).
Bulk Update (BU) is used when bulk copying data to a table and specifying TABLOCK prompts.

Shared locks
Shared (S) locks allow concurrent transactions to read (SELECT) a resource. When a shared (S) lock exists on a resource, no other transaction can modify the data. Once the data has been read, the shared (s) lock on the resource is released immediately, unless the transaction isolation level is set to repeatable read or higher, or a shared (s) lock is retained with a lock hint during the transaction lifetime.

Update lock
Update (U) locks can prevent deadlocks in the usual form. The general update mode consists of a transaction that reads records, gets a shared (S) lock on a resource (page or row), and then modifies the row, which requires the lock to be converted to an exclusive (X) lock. If two transactions obtain a shared schema lock on the resource and then attempt to update the data at the same time, a transaction attempts to convert the lock to an exclusive (X) lock. The conversion of shared mode to exclusive lock must wait for some time because the exclusive lock of one transaction is incompatible with the shared mode lock of other transactions; The second transaction attempted to obtain an exclusive (X) lock for an update. A deadlock occurs because two transactions are converted to exclusive (X) locks, and each transaction waits for another transaction to free a shared-mode lock.
To avoid this potential deadlock problem, use the update (U) lock. Only one transaction at a time can obtain an update (U) lock on the resource. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared lock.

Exclusive lock
Exclusive (X) locks prevent concurrent transactions from accessing resources. Other transactions cannot read or modify data in exclusive (X) lock locks.

Intent lock
Intent locks indicate that SQL Server needs to acquire shared (S) or exclusive (X) locks on some of the underlying resources in the hierarchy. For example, a shared intent lock placed at the table level indicates that a transaction intends to place a shared (S) lock on a page or row in a table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks can improve performance because SQL Server checks intent locks only at the table level to determine whether a transaction can safely acquire locks on that table. Instead of checking each row in the table or the locks on each page to determine whether the transaction can lock the entire table.
Intent locks include intent sharing (IS), intent Exclusive (IX), and intent exclusive sharing (SIX).
Lock mode description
Intent Sharing (IS) indicates that the intent of the transaction is to read the underlying resource in the hierarchy (not all) by placing an S lock on each resource.
Intent Exclusive (IX) by placing an X lock on each resource, the intent of the transaction is to modify the underlying resources in the hierarchy, not all of them. IX is a superset of IS.
With intent exclusive sharing (SIX) by placing an IX lock on each resource, the intent of the transaction is to read all the underlying resources in the hierarchy and modify the partial (not all) underlying resources. Allow concurrent is locks on top-level resources. For example, a table's SIX lock places a SIX lock on the table (which allows concurrent is locks) and an IX lock on the currently modified page (placing an X lock on the modified row). Although each resource can have only one SIX lock for a period of time to prevent other transactions from updating the resource, other transactions can read the underlying resource in the hierarchy by obtaining the table-level is lock.

Schema lock
Use schema Modification (SCH-M) locks when performing data definition language (DDL) operations on a table, such as adding a column or dropping a table.
The schema stability (sch-s) lock is used when compiling the query. Schema stability (sch-s) locks do not block any transaction locks, including exclusive (X) locks. Therefore, when you compile a query, other transactions, including transactions that have exclusive (X) locks on the table, can continue to run. However, DDL operations cannot be performed on the table.

Large-Capacity Update lock
Bulk update (BU) locks are used when data is bulk copied to a table and TABLOCK prompts are specified or the table lock on bulk Tables option is set with sp_tableoption. Bulk update (BU) locks allow a process to bulk copy data to the same table concurrently, while preventing other processes that do not bulk copy data from accessing the table.

Lock hints
We can view the current lock by looking at the results of the sp_lock, however, the result of Sp_lock return is a bunch of IDs, although we can get the meaning of ID represented by the function of object_name, but it is inconvenient after all. There is a management stored procedure in Resource Kit called Sp_lock2 that returns more detailed results, please get this code in resources.

Generally, when we use SQL statements, SQL Server automatically chooses an appropriate lock mode and range based on the type of SQL statement, such as SELECT, Update, and the resources used.

But sometimes we need finer control over locking behavior, so we can change the behavior of locks by locking hints.
Lock Tip Description
HOLDLOCK the shared lock to the transaction completion, rather than releasing the lock as soon as the corresponding table, row, or data page is no longer needed. HOLDLOCK equal to SERIALIZABLE.
NOLOCK do not issue shared locks and do not provide exclusive locks. When this option is in effect, an uncommitted transaction or a set of pages that are rolled back in the middle of the reading may be read. Dirty reads may occur. Applies only to select statements. The
Paglock uses page locks where a single table lock is typically used. The
ReadCommitted performs a scan with the same lock semantics as the transaction running at the commit read isolation level. By default, SQL Server 2000 operates at this isolation level. The
READPAST skips the lock row. This option causes the transaction to skip rows that are locked by other transactions (which are normally displayed in the result set) rather than blocking the transaction so that it waits for other transactions to release the locks on those rows. The READPAST lock hint applies only to transactions running at the commit read isolation level and is read only after row-level locks. Applies only to SELECT statements. The
readuncommitted equals NOLOCK. The
RepeatableRead performs a scan with the same lock semantics as transactions running at the repeatable read isolation level. The
Rowlock uses row-level locks instead of thicker-grained page-level and table-level locks. The
SERIALIZABLE performs a scan with the same lock semantics as a transaction running at the serializable isolation level. Equal to HOLDLOCK. The
TABLOCK uses table locks instead of finer-grained row-level or page-level locks. SQL Server holds the lock until the end of the statement. However, if HOLDLOCK is specified at the same time, the lock is held until the end of the transaction. The
Tablockx uses the exclusive locks of the table. This lock prevents other transactions from reading or updating tables and holding them until the end of the statement or transaction. The
UPDLOCK uses an update lock when reading a table, instead of a shared lock, and retains the lock until the end of the statement or transaction. The advantage of UPDLOCK is that it allows you to read data (without blocking other transactions) and update the data at a later time, ensuring that the data has not been changed since the last time the data was read. The
Xlock uses exclusive locks and remains until the end of a transaction on all data processed by the statement. You can specify the lock using Paglock or TABLOCK, in which case the exclusive lock applies to the granularity of the appropriate level.

Here's a quick example of how we can use these locking hints to improve performance.
1) Specify table lock
In general, SQL Server does not use table exclusive locks, especially for larger tables. Because maintaining a large table exclusive lock can seriously affect the concurrency performance of the system.
However, if there is no concurrent operation, there are a lot of page lock and EXT lock will also affect the performance to some extent, after all, the management of locks still need a certain cost. Therefore, we can use Tablockx to specify the use of table exclusive locks in cases where there is no concurrent operation (or low concurrent operation priority).
UPDATE dbo. Shortmessage with (Tablockx)
SET Otherpartynumber = U.username
FROM dbo. UserList u
WHERE U.userid = dbo. Shortmessage.otherpartyid

2) do not use the lock
Generally, a shared lock is issued on a resource at select time. But when querying and updating are frequent, we don't want to slow down performance because we're waiting for lock resources, and we don't care if the data we read is up to date, we don't care about dirty reads and uncommitted reads, we can specify select without lock
Or, in a mass update or insertion of data, I want to know the extent of the operation because of the impatient reason, and because of the existence of the table exclusive lock, so that I can not or share the lock to query the table, then we can not use the lock.
SELECT COUNT (*) from dbo. Shortmessage with (NOLOCK)

3 Use exclusive locks when querying
This has nothing to do with performance, but it is useful in some special situations, such as when we are querying certain records, we do not want it to be queried by other processes, then we can use exclusive locks.
SELECT * FROM dbo. Shortmessage with (Xlock HOLDLOCK)
WHERE Otherpartyid = 1
Here I also use a holdlock to keep this lock to the end of the entire transaction

4 Skip Lock Line
If some records are blocked while the query is not acquiring a shared lock, and we can skip the rows, if we can bypass the lock row, as in 3, the other query can skip the locked row.
SELECT * FROM dbo. Shortmessage with (READPAST)

5 Specifies the use of row-level locks
If we need to do a large-scale query in a highly concurrent environment, but we do not want this query to affect other queries too much, and the priority of this query is not high, then we can specify the use of row-level locks.
SELECT * FROM dbo. Shortmessage with (Rowlock)

Summarize
On the use of locking tips simple to cite a few examples, in fact, the use of locking tips is still a lot of readers to explore themselves.
Simply put, smaller-grained locks, such as row-level locks, can help increase the system's concurrency, but increase the cost of locks, while using more granular locks, such as table-level locks, can help improve the performance of a single process, but can compromise the system's concurrency.
Lock-level locks, such as exclusive locks, can improve the isolation level of a transaction, while lowering the locking level, such as not using a lock, can improve the performance of the system, but compromise the integrity of the transaction.