Foreword performance, how to maximize the performance of the database is the problem that every DBA needs to face, running like a small amount of data, but when a large amount of data is slow, such a thing, have you touched? ? How to better improve the concurrency access performance of the database? Yes, "lock" and solve the key to the problem.
Prepare knowledge lock mode, most of the content is extracted from SQL Server 2000 Books If you are familiar with the type of SQL Server's lock, you can have this chapter.
Microsoft? SQL Server? 2000 has multi-size locks, allowing a transaction to lock different types of resources. In order to minimize the cost of the lock, SQL Server automatically locks the resource to the level of the task. Locking at a smaller particle size (eg, line) can increase concurrency but require a large overhead, because if many rows are locked, you need to control more locks. Locking on a larger particle size (eg, a table) is quite expensive because the locking of the entire table limits other transactions to access any part of the table, but the required overhead is low because there is less lock that requires maintenance.
SQL Server can lock the following resources (listed in order of granularity). Resource description RID line identifier. Used to lock a row in the table alone. Lock lock in the key index. Used to protect the keys range in serial transactions. PG 8 kilobytes (KB) data page or index page. Ext adjacent eight data pages or index pages. Tab includes all the tables, including all data and indexes. DB database
SQL Server uses a different lock mode to lock resources, which determines how concurrent transaction access resources.
SQL Server uses the following resource lock mode. Lock mode Description Sharing (S) is used to do not change or do not update the operation (read-only operation), such as a SELECT statement. Update (U) is used in updatable resources. Prevent a common form of deadlocks when multiple sessions are read, locked, and then possible resource updates. It is used for data modification operations such as INSERT, UPDATE, or DELETE. Ensure that the same resource is not updated simultaneously. Intention to create a hierarchy of the lock. The type of intent lock is: intentional sharing (IS), intentional zone (IX), and interested row sharing (Six). The architecture is used when performing operations depending on the operation of the table architecture. The type of architecture lock is: architecture modification (SCH-M) and architectural stability (SCH-S). Large Capacity Update (BU) replicates data in the table and specifies the TabLock prompt.
A shared lock sharing (S) lock allows concurrent transaction reading (SELECT) a resource. Any other transactions cannot modify data when there is a shared (s) lock. Once the data has been read, the shared (S) lock on the resource is immediately released unless the transaction isolation level is set to re-read or higher level, or the shared (S) lock is kept in the transactional cycle.
Updating the Lock Update (U) Lock prevents usual deadlocks. General update mode consists of a transaction, this transaction reads a record, obtains a resource (page or row) shared (S) lock, then modify the line, this operation requires lock to convert to a row it (X) lock. If the two transactions get a resource shared mode lock, then try to update the data at the same time, an transaction attempt to convert the lock to a row it (X) lock. The conversion of shared mode to the row lock must wait for a while, because a transaction is not compatible with the shared mode lock of other transactions; a lock waiting. The second transaction is trying to get the row (X) lock for updates. Since both transactions are converted into a row (X) lock, and each transaction waits for another transaction to release the shared mode lock, there is a deadlock. To avoid this potential deadlock problem, please use the update (U) lock. Only one transaction can get a resource update (U) lock. If the transaction is modified, the update (U) lock is converted to a row it (X) lock. Otherwise, the lock is converted to a shared lock. It is resistant to the resource from being accessed by the lock. Other transactions cannot read or modify the data of the lock lock lock.
The intent lock intent lock indicates that SQL Server requires a shared (S) lock or row (x) lock on some of the underlying resources in the hierarchy. For example, placing a shared intent lock in a table-level indicates that the transaction is intended to place a shared (S) lock on the page or line in the table. In the table-level set intent lock prevents another transaction, it will then get row (X) locks on the table containing that page. The intent lock can improve performance because SQL Server only checks the intent lock in the table-level to determine if the transaction can safely obtain the lock on the table. There is no need to check each row or lock on each page to determine if the transaction can lock the entire table. The intent lock includes intentional sharing (IS), intentional rows it (ix), and sharing it with intentions. Lock mode Description Interest Sharing (IS) By placing S locks on each resource, indicating that the intention of the transaction is the partial (rather than all) underlying resources in the reading hierarchy. The intentional row (ix) is to place an X lock on each resource, indicating that the intention of the transaction is the partial (rather than all) underlying resources in the hierarchy. IX is an ultra-set of IS. Sharing (SIX) with intention is to place IX locks on each resource, indicating that the intention of the transaction is to read all underlying resources in the hierarchy and modify the part (rather than all) underlying resources. Allow concurrent IS locks on top resources. For example, the SIX lock of the table places a SIX lock on the table (allowing concurrent IS lock), placing the IX lock on the currently modified page (placing the X-lock on the modified line). Although each resource can only have a SIX lock in a period of time to prevent other transactions from updating the resource, other transactions can read the underlying resources in the hierarchy by getting the table-level IS lock.
Scheduled Locked Data Definition Language (DDL) operation (for example adds a column or removing table), using the architecture modification (SCH-M) lock. When compiling queries, the architecture stability (SCH-S) lock is used. Architecture Stability (SCH-S) lock does not block any transaction lock, including the row (X) lock. Therefore, other transactions (including transactions, which are rowned (X) locks on the table can continue to run. However, DDL operation cannot be performed on the table.
Large-capacity update lock When copying the large capacity to the table, and specifies the TabLock prompt or sets the Table Lock on Bulk table option using SP_TableOption, the massive update (BU) lock will be used. The Large Capacity Update (BU) lock allows the process to copy data into the same table to the same table while preventing other processes that do not perform large-capacity copy data from accessing the table.
Lock prompts that we can view the current lock by observing the results of sp_lock, but, sp_lock returns the result is a pile of ID, although we can get the meaning representative of the ID through functions such as Object_name, but it is inconvenient for all. There is a management stored procedure in the Resource Kit, called sp_lock2, which can return more detailed results, and obtain this code in the reference. In general, when we use the SQL statement, SQL Server will select a suitable lock mode and range from the type of SQL statement, such as SELECT, UPDATE, etc., and the resources used.
But sometimes we need more refined control locking behavior, then we can change the behavior of the lock by locking prompts. Lock Tip Description Holdlock keeps the shared lock to the transaction completion, not immediately release the lock when the corresponding table, row, or data pages are no longer needed. Holdlock is equivalent to serializable. NOLOCK Do not make a shared lock and do not provide a lock lock. When this option takes effect, you may read uncommitted transactions or a set of pages that read the middle rollback. It is possible to have dirty reading. Apply only to the SELECT statement. Paglock uses a page lock in places usually using a single table lock. ReadCommitted is scanned with the same lock semantic manager as running the read isolation level. By default, SQL Server 2000 operates at this isolation level. Readpast skips lock lines. This option causes a transaction that is hopped by other transaction (these rows can usually be displayed in the result set), rather than blocking the transaction, making it waiting for other transactions to release the locks on these lines. The READPAST lock prompt is only suitable for running a transaction that is submitted to the read isolation level and is only read after the row-level lock. Only for the SELECT statement. ReadunCommitted is equivalent to NOLOCK. RepeATableRead performs scanning with the same lock semantic transaction as running in repeatable read isolation level. Rowlock uses a row lock without using a packet lock and table-level lock. Serializable performs scanning with the same lock semantic same lock semanties as running on a serial interval level. Also equivalent to Holdlock. Tablock uses a chain lock or page lock with a tabletage. Before the end of the statement, SQL Server has always held the lock. However, if you specify the Holdlock, the lock will have been held before the end of the transaction. Tablockx is locked using a table. This lock prevents other transactions from reading or updating tables and holds them before the statement or transaction. Updlock reads the update lock when reading the table without using the shared lock and keeps the lock until the end of the statement or transaction. The advantage of UPDLOCK is to allow you to read data (without blocking other transactions) and update the data in later, and ensure that the data has not been changed since the last read data. Xlock uses a row to lock and keep it at the end of the transaction on all data processed by the statement. This lock can be specified using PAGLOCK or TABLOCK, and the lock is used to lock the particle size of the appropriate level.
Now simple example shows how we will use these lockouts to improve performance. 1) Specify the table lock generally, SQL Server is not aware of its lock, especially for a relatively large table. Because it maintains a large-scale row, it will seriously affect the concurrency performance of the system. However, if there is no concurrent operation, there are many Page Lock and Ext Lock will also affect performance in a certain extent. After all, the lock management needs to be overhead. So, in the case where it is confirmed that there is no concurrent operation (or low operation priority), we can use TabLockX to specify the use of the table lock. Update dbo.shortMessage with (Tablockx) set OtherPartynumber = u.username from dbo.userlist u where u.userid = dbo.shortmessage.Orpartyid2) Do not use locks in SELECT, it will send a shared lock for resources . However, when the query and update are very frequent, we don't want to reduce performance because of each other, and we don't care about the latest data. Don't worry about dirty and not submitted, we can specify whether SELECT is not Using locks or, when a large number of updates or inserts data, I want to know what is the extent due to the reason, and this time, because the presence of the blanket, make I can't or share the lock to query the table. Then we can also use the lock. Select count (*) from dbo.shortmessage with (NOLOCK)
3) When using the row of his lock, this is nothing to do with performance, but it is useful in some special circumstances. For example, when we query some records, you don't want it to be inquired by other processes, then we can use his lock. . Select * from dbo.shortmessage with (xlock holdlock) Where otherpartyid = 1 Here I also use a HOLDLOCK, which is to keep this lock to the end of the entire transaction
4) Skip Locks If you can't get blocked because you can't get shared lock, we can skip the lock line, such as 3), other queries You can skip the lock line. Select * from dbo.shortmessage with (readpast)
5) Specify Using a row lock, if we need to make a large-scale query in a highly release environment, but we don't want this query too much to influence other inquiry, and this query is not high, then us You can specify a row lock. Select * from dbo.shortmessage with (Rowlock)
Summary For several examples of the use of lock tips, there are still many examples of locking prompts, and they need to discover themselves. Simply put, use a smaller lock, such as a row lock, help to increase the system's concurrent release, but increase the overhead of the lock; use a larger lock, such as a table-level lock, help to improve The performance of a single process, but it will damage the system's concurrent release. Use a higher lock level, such as racing his lock, can improve the isolation level of the transaction; reducing the lock level, such as not using the lock, can improve the performance of the system, but will damage the integrity of the transaction.
