[GO] database lock SQL lock

[Guide: The basic theory of locks used in various large databases is consistent, but there are differences in the implementation. SQL Server emphasizes that the locks are managed by the system. When the user has a SQL request, the system analyzes the request, automatically in satisfying the lock condition and the system performance for the database with the appropriate lock, while the systems are often automatically optimized during operation, dynamic lock.

For the general users, through the system's automatic locking management mechanism can basically meet the usage requirements, but if the data security, database integrity and consistency have special requirements, you need to understand the SQL Server lock mechanism, the database locking method. ]

Lock is a very important concept in database, it is mainly used in multi-user environment to ensure database integrity and consistency. We know that data inconsistency occurs when multiple users can manipulate data in the same database at the same time. That is, if there is no lock 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 issues include: missing updates, dirty reads, non-repeatable reads, and hallucination reads:

1. A missing update issue occurs when two or more transactions select the same row and then update the row based on the value originally selected. Every transaction is unaware of the existence of other transactions. The last update overrides updates made by other transactions, which results in data loss. For example, two editors made an electronic copy of the same document. Each editor changes its copy independently, and then saves the changed copy, overwriting the original document. The editor who last saved its change copy overrides the changes made by the first editor. If the second editor can make changes after the first editor finishes, you can avoid the problem.

2. Dirty reading means that when a transaction is accessing the data and the data has been modified, and the modification has not yet been committed to the database, another transaction accesses the data and then uses that data. Because this data is data that has not yet been submitted, the data that is read by another transaction is dirty, and the operation based on dirty data may not be correct. For example, an editor is changing an electronic document. During the change, another editor copied the document (which contains all the changes made so far) and distributed it to the intended user. After that, the first editor thought that the changes that were being made were wrong, and then deleted the edits and saved the document. Documents that are distributed to users contain edits that no longer exist, and those edits should be considered to have 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, the data that the first transaction two reads may be different because of the modification of the second transaction. This occurs when the data that is read two times within a transaction is not the same and is therefore called 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 is not repeatable. You can avoid this problem if the editor can read the document only after the author has finished writing it all.

4. Illusion reading refers to a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table, which involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred. For example, an editor changes the document submitted by the author, but when the production department merges its changes into the primary copy of the document, it finds 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 have finished processing the original document.

Therefore, the method of handling multiuser concurrent access is locking. Locks are a primary 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 impact of locking on concurrent access is reflected in the granularity of the lock. In order to control the locked resources, you should first understand the system's space management. In a SQL Server 2000 system, the smallest space management unit is the page, and a page has 8 K. All data, logs, and indexes are stored on the page. In addition, the use of a page has a limit, which is that a row of data in a table must be on the same page, not across pages. The Space management unit above the page is the disk area, and a disk area is 8 contiguous pages. The minimum unit of use for tables and indexes is the extents. A database consists of one or more tables or indexes, which are made up of multiple extents. Locks placed on a table restrict concurrent access to the entire table; locks placed on the extents limit access to the entire extents, locks placed on data pages restrict access to the entire data page, and locks on rows restrict concurrent access to the row.

SQL Server 2000 has a multi-granularity lock that allows a transaction to lock different types of resources. To minimize the cost of locking, SQL Server automatically locks the resource at the appropriate level for the task. Locking at a smaller granularity, such as a row, can increase concurrency but requires significant overhead, because if many rows are locked, more locks need to be controlled. Locking in a larger granularity, such as a table, is quite expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions, but requires less overhead because fewer locks need to be maintained. SQL Server can lock up resources such as rows, pages, extents, tables, libraries, and so on.

Row is the smallest space that can be locked, row-level locks occupy the least amount of data resources, so during transaction processing, allowing other transactions to continue to manipulate the same table or other data on the same page, greatly reducing the time for other transactions to wait for processing, improve the concurrency of the system.

Page-level lock refers to the process of transaction manipulation, regardless of the number of transactions processed data, each time a lock page, the data on this page can not be manipulated by other transactions. Prior to SQL Server 7.0, a page-level lock was used. Page-level lock locks are more resource-locked than row-level lock-locked data resources. 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 page-level locks are used, there is a waste of data, which means that data is consumed but not used on the same page. In this phenomenon, the data is wasted up to no more than the rows of data on a single page.

Table-level locks are also a very important lock. A table-level lock is when a transaction manipulates data from a table, locks the entire table where the data resides, and other transactions cannot access other data in the table. Table-level locks are generally used when the amount of data processed by the transaction is large. Table-level locking is characterized by the use of less system resources, but consumes more data resources. Table-level locks consume less system resources, such as memory, than row-level and page-level locks, but consume the most data resources. At table-level locks, there is a high likelihood of data wasting, because table-level locks lock the entire table, and 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 is a transaction that occupies a single disk area that cannot be consumed by other transactions at the same time. For example, this type of lock is used when the system allocates physical space when the database is created and the table is created. The system allocates space according to the disk area. When the system allocates space, the 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, you do not use a disk lock when it comes to transactions that involve data manipulation.

A database-level lock is the locking of the entire database, preventing any user or transaction from accessing the locked database. A database-level lock is a very special kind of lock that is only used during the recovery operation of the database. This level of lock is the highest level of lock, because it controls the operation of the entire database. As long as the database is restored, it is necessary to set the database to single-user mode, so that the system can prevent other users from doing various operations on the database.

Row-level locks are an optimal lock because row-level locks are not a waste of data that is both occupied and unused. However, if more than one record in a table is 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 sharply, thus aggravating the system load and affecting system performance. Therefore, lock escalation is also supported in SQL Server. The so-called lock escalation refers to adjusting the granularity of the lock, replacing multiple low-granularity locks with a few higher-granularity locks to reduce system load. In SQL Server, when there are more locks in one transaction and the lock escalation gate is reached, the system automatically upgrades row-level and page locks to table-level locks. Notably, in SQL Server, the lock escalation threshold and lock escalation are determined automatically by the system and do not require user settings.

When locking in a SQL Server database, in addition to the different resources can be locked, you can also use different degrees of lock mode, that is, there are several modes of lock, SQL Server lock mode includes:

1. Shared locks are used for all read-only data operations in SQL Server. 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 a query "select * from AUTHORS", first lock the first page, after reading, release the lock on the first page, and then lock the second page. This allows you to modify the first page that is not locked during a read operation. However, the Transaction Isolation level connection option settings and the lock settings in the SELECT statement can change this default setting for SQL Server. For example, "SELECT * from AUTHORS HOLDLOCK" requires that the table be locked during the entire query process until the query is complete before releasing the lock.

2. The update lock update lock is used during the initialization phase of the modify operation to lock resources that might be modified, which avoids the deadlock caused by the use of shared locks. 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 the modify operation. This way, if there are two or more transactions concurrently requesting a shared lock on a transaction, these transactions will be upgraded to exclusive locks when the data is modified. At this point, these transactions do not release the shared lock but wait for the other party to release, which creates a deadlock. If a data is directly requested to update the lock before modification, it can avoid deadlock if it is upgraded to exclusive lock when the data is modified.

3. Exclusive lock locks are reserved for modifying data. The resources it locks, other transactions cannot be read, and cannot be modified.

4. A schema modification (SCH-M) lock is used when a structure lock performs a data definition language (DDL) operation on a table, such as adding a column or dropping a table. The schema stability (sch-s) lock is used when compiling the query. The schema stability (sch-s) lock does not block any transaction locks, including exclusive locks. As a result, other transactions, including those that have an exclusive lock 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 on the lower level of the resource. For example, a table-level shared intent lock describes a transaction intent to release an exclusive lock to a page or row in a table. The intent lock can also be divided into shared intent lock, exclusive intent lock and shared exclusive intent lock. A shared intent lock describes the transaction intent to place a shared lock on a low-level resource locked by a shared intent lock to read data. An exclusive intent lock indicates that the transaction intends to modify the data by placing an exclusive lock on the low-level resource locked by the shared intent lock. Shared exclusive lock description transactions allow other transactions to use shared locks to read top-level resources and intend to place an exclusive lock on the lower layer of the resource.

6. Bulk update lock A bulk update lock is used when you bulk copy data to a table and specify the TABLOCK hint or use sp_tableoption to set the table lock on bulk option. A bulk update lock allows a process to concurrently bulk replicate data to the same table, while preventing other processes that do not bulk copy data from accessing the table.

In SQL Server systems, it is recommended that the system automatically manage locks, which analyze the user's SQL statement requirements, automatically add appropriate locks to the request, and lock escalation automatically when the number of locks is too large. As mentioned earlier, the upgrade threshold is automatically configured by the system and does not require user configuration. In practical applications, it is sometimes necessary to artificially lock a table in a database for the application to run correctly and to maintain data consistency. For example, in a transaction operation of an application, it is necessary to perform statistical operations on several data tables according to a number, in order to ensure the consistency and correctness of the statistical data, from the beginning of the first table to the end of all tables, other applications or transactions can no longer write data to these tables, this time, The application wants to use manual locking (also known as explicit lock) technology when it is possible for a program to artificially (explicitly) lock these tables starting from the first data table or at the beginning of the entire transaction.

You can use SELECT, INSERT, UPDATE, and DELETE statements to specify the scope of a table-level locking hint to boot Microsoft SQL Server 2000 using the required lock type. Use table-level locking hints to change the default locking behavior when you need finer control over the type of lock the object obtains.

There are several table-level locking hints that you specify:

1. HOLDLOCK: Holds a shared lock on the table until the entire transaction ends, rather than releasing the added lock immediately after the statement is executed.

2. NOLOCK: No shared and exclusive locks are added, and when this option is in effect, it is possible to read UNCOMMITTED read data or "dirty data", which is only applied to select statements.

3. Paglock: Specifies that page locks are added (otherwise, table locks may be added).

4. The readcommitted performs the scan with the same lock semantics as the transaction running at the Read isolation level of the commit. By default, SQL Server 2000 operates at this isolation level:

5. READPAST: Skipping data rows that have been locked, this option will cause the transaction to read the data while skipping those 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 transactional operation under the isolation level.

6. ReadUncommitted: Equivalent to Nolock.

7. RepeatableRead: Sets the level of the transaction as repeatable read isolation.

8. Rowlock: Use row-level locks instead of coarser-grained page-level and table-level locks.

9. SERIALIZABLE: Performs a scan with the same lock semantics as transactions running at the serializable isolation level. Equivalent to HOLDLOCK.

10. TABLOCK: Specifies that a table-level lock is used instead of a row-level or page-level lock, and SQL Server releases the lock after the statement is executed, and if Holdlock is specified at the same time, the lock remains until the end of the transaction.

11. Tablockx: Specifies that an exclusive lock is used on the table, which prevents other transactions from reading or updating the table's data until the end of the statement or the entire transaction.

12. UPDLOCK: Specifies that the update lock is set when reading data in the table (update lock) instead of the shared lock, which remains until the end of the statement or the entire transaction, and uses UPDLOCK to allow the user to read the data first (without blocking other users from reading the data). And it is guaranteed that the data will not be modified by other users for a period of time when the data is later updated.

Deadlock problem

In a database system, a deadlock is one in which multiple users (processes) lock a resource, and then attempt to request a lock on a resource that is already locked, resulting in a lock request loop that causes multiple users (processes) to wait for the other party to release the state of the locked resource. This deadlock is the most typical form of deadlock, such as having two transactions A and b at the same time, and transaction a having two operations: locking the table part and requesting access to the table supplier; transaction B also has two operations: locking the table supplier and requesting access to the table part. As a result, a deadlock has occurred between transaction A and transaction B.

The second case of deadlock is when there are several long-running transactions that perform parallel operations in a database, and when Query Analyzer handles a very complex query such as a connection query, a deadlock can occur because the order of processing cannot be controlled.

In SQL Server, the system can automatically periodically search for and handle deadlock problems. The system identifies all process sessions waiting for a lock request in each search, and SQL Server begins a recursive deadlock search if the identified process is still waiting in the next search. When the search detects a lock request ring, 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 of the application (by returning the 1205th error message), cancels the thread's current request, and then allows the transaction of the non-breaking thread to proceed. SQL Server typically chooses the thread that runs the least expensive transaction when undoing as a deadlock victim. In addition, the user can use the SET statement to set the deadlock_priority of the session to low. The deadlock_priority option controls how the session's importance is measured in a deadlock situation. If the session is set to low, it will be the preferred victim when the session is locked into a deadlock condition.

Understanding the concept of deadlock, in the application can be used in some of the following methods to avoid deadlocks: (1) Reasonable arrangement of table access order. (2) Try to avoid user intervention in the transaction, try to make a transaction less task, keep the transaction short and in a batch process. (3) Data access time domain Discretization method, data access time domain Discrete method refers to the client/server structure, the use of various control methods to control the database or database object access time period. Mainly through the following ways: The reasonable arrangement of the background transaction execution time, the use of workflow to the background transaction management unified. Workflow in the management of tasks, on the one hand limit the number of threads of the same class of tasks (often limited to 1), to prevent excessive consumption of resources; On the other hand, reasonable scheduling of different tasks to execute timing, time, as far as possible to avoid multiple background tasks at the same time, in addition, avoid running background tasks during peak hours of reception. (4) Data storage space discretization method. The data storage space discretization method refers to the use of various means to spread the data logically in a table into a number of discrete spaces, in order to improve the access performance of the table. Mainly by the following methods: First, the large table by row or column decomposition into a number of small tables; Second, according to different user groups decomposition. (5) Use the lowest possible isolation level. The isolation level is the degree to which multi-user transactions are isolated to ensure the integrity and consistency of database data, SQL92 defines 4 isolation levels: uncommitted read, read-committed, Repeatable read, and serializable. If you choose too high an isolation level, such as serializable, although the system can achieve greater isolation to ensure the integrity and consistency of the data, but the conflict between the transactions and the chance of deadlock greatly increased, greatly affecting the system performance. (6) With a bound connection, a bound connection allows two or more transactional connections to share transactions and locks, and any one transaction connection to request a lock is the same as another transaction requesting a lock, so these transactions can be allowed to share data without locking conflicts.

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

Original Digest from http://www.cnblogs.com/wangjingblogs/archive/2012/01/12/2320561.html

[GO] database lock SQL lock