SQL Server lock mechanism

Server
Author: Song Lihuan [Guide: The basic theory of locks used in various large databases is consistent, but there are differences in concrete implementations. SQL Server is more focused on managing locks by the system. When the user has the SQL request, the system analyzes the request, automatically satisfies the locking condition and the system performance to add the appropriate lock to the database, simultaneously the systems often automatically optimizes the processing during the operation, implements the dynamic lock. For the general users, through the system of automatic locking management mechanism can meet the basic requirements, but if the data security, database integrity and consistency have special requirements, you need to understand the SQL Server lock mechanism, grasp the database locking method. ]

    Lock is a very important concept in database, it is mainly used to guarantee database integrality and consistency under multi-user environment. We know that data inconsistency occurs when multiple users can manipulate data in the same database at the same time. That is, if there are no locks and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. These include missing updates, dirty reads, non-repeatable reads, and hallucination reads: 1. A loss update problem occurs when two or more transactions select the same row and then update the row based on the value originally selected. Every transaction has no knowledge of the existence of other transactions. The final update overrides updates made by other transactions, which results in data loss. For example, two editors made electronic copies of the same document. Each editor changes its copy independently, and then saves the changed copy, overwriting the original document. The editor who finally saved its change copy overwrites the changes made by the first editor. This problem can be avoided if the second editor is able to make changes after the first editor finishes. 2. Dirty reading means that when a transaction is accessing the data and the data has been modified and the modification has not been submitted to the database, another transaction accesses the data and then uses the data. Because this data is not yet submitted data, then another transaction read the data is dirty data, the operation based on dirty data may be incorrect. For example, an editor is changing an electronic document. During the change process, another editor copies the document (which contains all the changes made so far) and sends it to the intended user. After that, the first editor decided that the current changes were wrong, and then deleted the edits and saved the document. The document that is distributed to the user contains edits that no longer exist, and those edits should be considered never existed. This problem can be avoided if no one is able to read the changed document until the first editor determines the final change. 3. Non-repeatable reading refers to reading the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction and the modification of the second transaction, the data read by the first transaction two times may be different. This makes it happen that the data read two times within a transaction is not the same, so it is called a non repeatable read. For example, an editor reads the same document two times, but between two reads, the author rewrites the document. When the editor reads the document for the second time, the document has changed. The original read cannot be duplicated. You can avoid this problem if the editor can read the document only after the author has finished writing it all. 4. Hallucination reading is a phenomenon that occurs when a transaction is not executed independently, for example, the first transaction modifies the data in a table that involves all the rows of data in the table. With, the second transaction also modifies the data in this table, which is inserting a row of new data into the table. So, it's going to happen later. The user of the first transaction discovers that there are no modified data rows in the table, as if there were hallucinations. For example, an editor changes the document submitted by the author, but when the production department merges its changes to the master copy of the document, it discovers that the author has added the unedited new material to the document. This problem can be avoided if no one is able to add new material to the document until the editor and production department finish processing the original document. Therefore, the method of processing multi-user concurrent access is lock. Locks are a major means of preventing other transactions from accessing the specified resource control and implementing concurrency control. When a user locks an object in the database, other users can no longer access the object. The effect of lock on concurrent access is reflected in the granularity of the lock. In order to control the locked resources, we should first understand the space management of the system. In a SQL Server 2000 system, the smallest space management unit is a page, and a page has 8 K. All data, logs, and indexes are stored on the page. In addition, there is a limit to using a page, which is that a row of data in a table must be on the same page and not spread across pages. The Space management unit above the page is the disk area, and a disk area is 8 consecutive pages. The minimum occupancy unit for tables and indexes is the disk area. A database is made up of one or more tables or indexes, that is, a plurality of disk areas. A lock placed on a table restricts concurrent access to the entire table; Locks placed on the panel limit access to the entire disk area; Locks placed on the data page restrict access to the entire data page; Placing a lock on a row restricts concurrent access to that row.

SQL Server 2000 has a multiple-granularity lock that allows 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 resources such as rows, pages, extents, tables, libraries, and so on. Rows are the smallest space that can be locked, row-level locks occupy the least amount of data, so allowing other transactions to continue manipulating the same table or other data on the same page during transaction processing greatly reduces the time for other transactions to wait for processing and increases the concurrency of the system. Page-level locking refers to the fact that the data on this page cannot be manipulated by other transactions, regardless of the number of transaction processing data, and each time a page is locked during transaction manipulation. Before SQL Server 7.0, page-level locks were used. Page-level lock locks are more resource-locked than row-level locks. In a page-level lock, even if a transaction manipulates only one row of data on a page, other data rows on that page cannot be used by other transactions. As a result, when you use page-level locks, there is a waste of data, which means that data is occupied but not used on the same page. In this phenomenon, data is wasted up to no more than a row of data on a page. Table-level locks are also a very important lock. A table-level lock is when a transaction manipulates data in a table, locking the entire table where the data resides, and other transactions cannot access other data in that table. Table-level locks are generally used when transactions are handled with a large amount of data. Table-level locks are characterized by the use of less system resources, but consume more data resources. Table-level locks occupy less system resources, such as memory, than row-level and page-level locks, but occupy the largest data resources. In a table-level lock, there is a risk of a large amount of wasted data, because table-level locks lock the entire table, so other transactions cannot manipulate other data in the table. A disk lock is a special type of lock that can only be used in special cases. A cluster-level lock means that a transaction occupies a disk area that cannot be occupied by other transactions at the same time. For example, when creating a database and creating a table, the system uses this type of lock when it allocates physical space. The system is allocated space according to the disk area. When the system allocates space, a disk lock is used to prevent other transactions from using the same disk area at the same time. This type of disk lock is no longer used when the system finishes allocating space. In particular, a disk lock is not used when transactions involving data operations are involved. Database-level locking is the locking of the entire database to prevent any user or transaction from accessing the locked database. Database-level locks are a very special kind of lock that is used only forDuring the recovery operation of the database. This level of lock is the highest class lock because it controls the operation of the entire database. As long as the database is restored, the database needs to be set to Single-user mode, so the system can prevent other users from doing various things with the database. Row-level locks are an optimal lock because row-level locks are not likely to cause a waste of data being both occupied and unused. However, if multiple records in a table are frequently manipulated in a user transaction, a row-level lock is added to many of the table's record rows, and the number of locks in the database system increases dramatically, aggravating system load and impacting system performance. As a result, lock escalation is also supported in SQL Server. The so-called lock escalation refers to adjusting the size of the lock, replacing multiple low granularity locks into a few higher-grained locks to reduce system load. The system automatically upgrades row-level locks and page locks to table-level locks when there are more locks in a transaction in SQL Server and a lock escalation gate is reached. It is particularly noteworthy that in SQL Server, the lock escalation threshold and lock escalation are determined automatically by the system and do not require user settings.        in the sql  server database lock, in addition to the different resources can lock, can also use different degrees of lock mode, that is, the lock has a variety of patterns, SQL The lock mode in server includes:     1. Share locks      SQL Server, shared locks are used for all read-only data operations. Shared locks are non-exclusive and allow multiple concurrent transactions to read their locked resources. By default, when data is read, SQL Server immediately releases the shared lock. For example, when you execute the query "select  *  from  AUTHORS", first lock the first page, release the lock on the first page after reading, and then lock the second page. This allows you to modify the first page that was unlocked during the read operation. However, the Transaction Isolation level connection option setting and the lock setting in the SELECT statement can change this default setting for SQL Server. For example, "select  *  from  authors  HOLDLOCK" requires that a lock on the table be maintained throughout the query until the query completes before releasing the lock.      2. Update Locks      Update locks are used to lock resources that may be modified during the initialization phase of the modification operation, thus avoiding the use of shared locksDeadlock phenomenon. Because a shared lock is used, the operation to modify the data is divided into two steps, first obtaining a shared lock, reading the data, and then upgrading the shared lock to an exclusive lock before performing a modification operation. This way, if two or more transactions are simultaneously requesting shared locks on a transaction, these transactions will upgrade the shared locks to exclusive locks when data is modified. At this point, these transactions do not release the shared lock but wait for the other to release, causing the deadlock. If a data is applied to update the lock directly prior to modification, the deadlock can be avoided if the data is modified to exclusive lock. 3. Exclusive locks      Exclusive locks are reserved for modifying data. The resources it locks, other transactions cannot be read or modified.    4. Structural locks      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 locks. As a result, other transactions, including those with exclusive locks on the table, can continue to run when the query is compiled. However, DDL operations cannot be performed on the table. 5. Intent lock      Intent lock indicates that SQL Server has the intention of acquiring a shared or exclusive lock at the lower level of the resource. For example, a table-level shared intent lock indicates that a transaction intends to release a lock to a page or row in a table. The intent lock can be divided into shared intent lock, exclusive intent lock and shared exclusive intent lock. A shared intent lock indicates that the transaction intends to read data by placing a shared lock on the lower resources that are locked by the shared intent lock. An exclusive intent lock indicates that the transaction intends to modify the data by placing an exclusive lock on the low-level resources that are locked by the shared intent lock. A shared exclusive lock indicates that a transaction allows another transaction to use a shared lock to read the top-level resource and to place an exclusive lock on the lower level of the resource.      6. Bulk update lock   The bulk update lock is used when bulk copying data to a table, specifying a TABLOCK prompt, or setting the table lock on Bulk table option with sp_tableoption. A bulk update lock allows 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.     SQL Server System recommended that the system automatically manage locks, the system will analyze the user's SQL statement requirements, automatically add the appropriate lock for the request, and in the number of locks too much, the system will automatically lock upgrade. As mentioned earlier, the upgraded thresholds are automatically configured by the system and do not require user matchingReset In practical applications, sometimes in order for the application to run correctly and maintain the consistency of data, it is necessary to manually lock a table in the database. For example, in a transaction operation of an application, it is necessary to perform statistical operations on several tables based on a number, in order to ensure the consistency and correctness of statistical data time, from the first table to the end of all tables, other applications or transactions can no longer write data to these tables, The application wants manual lock (also known as explicit locking) technology to be used to lock the tables artificially (explicitly) from the time the first data table is counted or at the beginning of the entire transaction. You can use the SELECT, INSERT, UPDATE, and DELETE statements to specify the range of table-level locking hints to boot microsoft  SQL Server 2000 using the desired lock type. Use table-level locking prompts to change the default locking behavior when you need finer control over the type of lock the object is acquiring. The specified table-level locking hint has the following several:    1. HOLDLOCK: Keep the shared lock on the table until the entire transaction ends, rather than immediately releasing the added lock after the statement has been executed.      2. NOLOCK: Do not add shared and exclusive locks, when this option is in effect, may read UNCOMMITTED read data or "dirty data", this option applies only to select statements.      3. Paglock: Specifies to add a page lock (otherwise it is usually possible to add a table lock).    4. 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 ...    5. READPAST: Skipping data rows that have been locked, this option will enable transactions to read data while skipping data rows that have been locked by other transactions, rather than blocking until other transactions release the lock, READPAST only applies to read Committed the SELECT statement operation in a transaction operation under the isolation level.     6. ReadUncommitted: Equal to Nolock.      7. RepeatableRead: Sets the transaction to a repeatable read level of isolation.    8. Rowlock: Use row-level locks instead of coarser-grained page-level and table-level locks.     9. SERIALIZABLE: Using vs. running in serializable read isolationThe same lock semantics for level transactions perform the scan. Equal to HOLDLOCK.   10. TABLOCK: Specifies that a table-level lock is used instead of a row-level or page-level lock, which is released by SQL Server when the statement is executed, and if Holdlock is specified, the lock remains until the end of the transaction.      11. Tablockx: Specifies that exclusive locks are used on the table, which prevents other transactions from reading or updating data for this table until the statement or the entire transaction ends.    12. UPDLOCK: Specifies that the update lock is set when the data is read in the table instead of a shared lock, which is persisted to this statement or the end of the entire transaction, using UPDLOCK to allow the user to read the data first (and not to block other users from reading the data), and ensure that the data is not modified by other users for a period of time when the data is later updated.     deadlock problem in the database system, a deadlock is a lock request loop that causes multiple users (processes) to be in a state of waiting for the other to release the locked resource, while the user (process) locks a resource separately and attempts to request a lock on the other's already locked resource. This deadlock is the most typical form of deadlock, such as having two transactions A and b at the same time, transaction A has two operations: locking the table part and requesting Access table supplier, and transaction B has two operations: locking the table supplier and requesting access to the table part. As a result, a deadlock occurred between transaction A and transaction B.     deadlock in the second case, when in a database, there are a number of long-running transactions that perform parallel operations, and when the Query Analyzer handles a very complex query such as a connection query, the deadlock phenomenon can occur because of the inability to control the order of processing.     in SQL Server, the system can automatically search for and handle deadlocks on a regular basis. The system identifies all process sessions that are waiting to lock requests in each search, and SQL Server begins a recursive deadlock search if the process that is identified in the next search is still in the waiting state. When the search detects a lock request loop, SQL Server ends the deadlock by automatically selecting the thread that can break the deadlock (the deadlock victim). SQL Server rolls back the transaction that is the deadlock victim, notifies the thread's application (by returning the number 1205th error message), cancels the thread's current request, and then allows the transaction of the nonbreaking thread to continue. SQL Server typically chooses a thread that runs the least amount of transactions when undoing as a deadlock victim. In addition, the user can use the SET statement toThe deadlock_priority of the words is set to low. The Deadlock_priority option controls how important the session is in the case of deadlocks. If the session is set to low, the session becomes the preferred victim when it falls into a deadlock situation.     understand the concept of deadlock, in the application of the following methods can be used to avoid deadlock: (1) Reasonable arrangement of table access order. (2) As far as possible in the transaction to avoid user intervention, try to make a transaction less task, keep the transaction short and in a batch. (3) Data access time domain Discretization method, the data access time domain Discretization method is refers to in the client/server structure, takes various control means to control to the database or the database object access time period. Mainly through the following ways: reasonable arrangement of the implementation of the background transaction time, the use of workflow for the unified management of the background affairs. Workflow in the management of tasks, on the one hand to limit the number of threads of the same type of task (often limited to 1), to prevent excessive consumption of resources; On the other hand, reasonable arrangement of different tasks to carry out timing, time, try to avoid multiple background tasks at the same time, in addition, to avoid in the foreground transaction peak time running background tasks. (4) Data storage space discretization method. The data storage space discretization method is to take various means to scatter the data logically in one table to some discrete space, in order to improve the access performance of the table. Mainly through the following methods: First, the large table by row or column decomposition into a number of small tables; Second, decompose according to different user groups. (5) Use the lowest possible isolation level. The level of isolation is the degree to which multiuser transactions are isolated to ensure the integrity and consistency of database data, and SQL92 defines 4 levels of isolation: uncommitted read, commit read, repeatable read, and serializable. If you choose too high a level of isolation, such as serializable, although the system can achieve better isolation and more to ensure the integrity and consistency of data, but the conflict between transactions and deadlock opportunities greatly increased, greatly affecting the system performance. (6) using a bound connection,  bound connection allows two or more transaction connections to share transactions and locks, and any one transaction connection is requested to request a lock, as is the case for another transaction to request a lock, so that these transactions can be allowed to share data without locking conflicts.    

In conclusion, it is important for a qualified DBA to understand the lock mechanism of SQL Server and to master the method of database locking.