Beijing Normal University Liu Yongming
Lock is a very important concept in the network database, which is mainly used to ensure database integrity and consistency in multi-user environments. The basic theory of the locks used in various large databases is consistent, but there is a difference in specific implementation. At present, most database management systems have more or less self-adjustment, self-management, so many users do not actually clear the theory of locks and the specific implementations of locks in the database used.
Microsoft SQL Server (SQL Server) has been widely used as a small and medium-sized database management system, which has more emphasized by the system to manage the lock. When the user has SQL request, the system analysis request is automatically added to the appropriate lock between the lock condition and system performance, and the system is automatically optimized during operation, and dynamically locking. For a general user, the automatic lock management mechanism through the system can basically meet the usage requirements, but if there is special requirements for data security, database integrity and consistency, you must control the lock and unlock of the database yourself. This needs to be understood. SQL Server lock mechanism, master database locking methods.
Locking multi-grainity and lock upgrade
The lock in the database refers to a software mechanism to indicate an user (i.e., the process session, the following) has occupied a certain resource, preventing other users from making the data modification or cause database data. Non-integrity and non - consistency. The so-called resources herein mainly refer to the data line, index, and data tables that the user can operate. Depending on the resource, the concept of multigranular is locked, that is, the level of resources that can be locked. Resource grain size that is capable of locking in SQL Server includes: database, table, region, page, key value (referring to row data with index), line identifier (RID, single line data in the table).
An important use of multitulus lock is used to support concurrent operation and ensure data integrity. SQL Server automatically adds a suitable lock to the database after analyzing the user's request. Suppose a user only operates a part of the row data in a table, the system may only add a few row lock (RID) or page locks, which can support multiple users as possible. However, if the user's transaction is frequently operated in a table, it will lead to many records of the table, and the number of locks in the database system will increase sharply, which increases the system. Load, affect system performance. Therefore, in the database system, lock escalation is generally supported. The so-called lock reliance refers to the particle size of the lock, replaces the plurality of low-grain-based locks into a small number of higher particle size locks to reduce the system load. When SQL Server is more locked in a transaction, when the lock upgrade threshold is reached, the system automatically upgrades the row-level lock and page lock to a table-level lock. It is particularly worth noting that in SQL Server, the lock upgrade threshold and the lock upgrade are automatically determined by the system, and no user settings are required.
Lock mode and compatibility
When you lock in the database, in addition to locking different resource locks, you can also use different levels of locking mode, that is, multiple modes, SQL Server interlocking mode includes:
1. Shared lock
In SQL Server, shared locks are used for all read-only data operations. The shared lock is non-exclusive, allowing multiple concurrent transactions to read their locked resources. By default, after the data is read, SQL Server releases the shared lock immediately. For example, when performing a query "Select * from my_table", first lock the first page, after reading, release the lock to the first page, and lock the second page. In this way, the first page that is unlocked is modified during the read operation. However, the transaction isolation level connection option settings and lock settings in the Select statement can change this default setting for SQL Server. For example, "Select * from my_table holdlock" requires the lock of the table until the query is released until the query is complete. 2. Modify lock
Modifying the initialization phase of the modified operation is used to lock resources that may be modified, which avoids the use of the deadlock caused by the shared lock. Because when using shared lock, modify data is divided into two steps, first get a shared lock, read data, and then upgrade the shared lock to exclusive lock, then perform modifications. This way, if there are two or more transactions, these transactions are upgraded to exclusive locks when modifying data at the same time. At this time, these transactions will not release the shared lock but wait for the other party to release, which causes a deadlock. If a data applies for modifying the lock before modifying, the deadlock can be avoided when the data is modified. Modifying locks and shared locks are compatible, that is, after a resource is locked with a shared lock, allowing to lock again.
3. Exclusive lock
Exclusive locks are reserved for modifying data. It does not be able to read the resources that it locked. Exclusive locks cannot be compatible with other locks.
4. Structural lock
The structural lock is divided into structural modification lock (SCH-M) and structural stabilization lock (SCH-S). SQL Server uses SCH-S lock when the SQL Server is locked with SCH-M locks and compiles queries.
5. Intent
Intentional Lock Description SQL Server has the intention of sharing locks or exclusive locks at the low level of resources. For example, a shared intent of the table-level means that the transaction intentions will release exclusive locks to the page or lines in the table. The intent lock can also be divided into shared intent locks, exclusive intent locks and shared exclusive intent. Sharing intent lock illustrates the transaction intent to place shared locks on the low resource locked by the shared intent lock. Exclusive intentional lock indicates that the transaction is to place exclusive locks on the low resource locked by the shared intent lock to modify the data. The shared exclusive lock indicates that the transaction allows other transactions to use shared locks to read the top resource and intend to place exclusive locks on the low level of the resource.
6. Batch modification lock
Use the bulk modified lock when copying the data in batches. Batch modified locks can be set by Tablock prompts for tables or using the "Table Lock on Bulk Load" option of the system stored procedure sp_tableOption.
In addition, the SQL Server command statement operation affects the method of locking, and the combination of statements can also produce different locks. Details are as follows:
Lock conflict and its prevention
In the database system, the deadlock refers to multiple users (processes) to lock a resource, and try to request locking the other party has locked resources, which produces a lock request ring, causing multiple users (processes). Wait for the other party to release the status of the locked resource.
In SQL Server, the system can automatically search and process deadlock problems. The system identifies all process sessions waiting to be locked in each search. If the logo in the next search is still waiting, SQL Server begins to recurrent deadlock searches.
(Upload DP21) When the search detects the lock request loop, the system will end a priority of the lowest priority according to the deadlock priority of each process session. Since then, the system rolls up the transaction and issues 1205 to the process. Number error message. In this way, other transactions may continue to run. Deadlock priority setting statement is: set deadlock_priority {low | normal}
Where LOW indicates that the process session is low, and the transaction of the process can be interrupted when there is a deadlock. In addition, the process can set the longest wait time of the process in the lock request state by setting the Lock_Timeout option. The statement of this setting:
Set lock_timeout {TIMEOUT_PERIOD}
Where Timeout_Period is in milliseconds.
Understand the concept of deadlocks, you can use some of the following methods in your application to avoid dead locks:
(1) Rationalize the order of viewing.
(2) Try to avoid user intervention in the transaction, try to make a transaction to handle tasks.
(3) use dirty reading technology. Dirty reading is not to lock the visited table, and the lock conflict is avoided. In the client / server application environment, some transactions often do not allow read dirty data, but under specific conditions, we can read it with daughter.
(4) Data Access Time Domain Discrete Method. Data Access Time Domain Discrete Act refers to a variety of control means to control an object access time period in a database or database in a client / server structure. Mainly implemented in the following way: reasonable arrangement of background transactions, unified management of background transactions. When the workflow is administrative task, on the one hand, the number of threads (often limited to 1) is limited to prevent excessive occupation of resources; on the other hand reasonably arrange different tasks to perform timing, time, try to avoid multiple background tasks simultaneously In addition, avoid running the background task at the peak time of the front desk trading.
(5) Data storage spatial discrete method. Data storage spatial discrete method refers to a variety of means, which will logically disperse data in a table to a number of discrete spaces to improve access performance of the table. Mainly achieved by the following method: First, the large table is decomposed into several small tables by row or column; second, according to different user groups.
(6) Use the lowest possible isolation level. The isolation level refers to the extent to ensure the integrity and consistency of database data, and SQL92 defines four isolation levels: not submitted, read, readable and serial. If you select too high isolation level, such as serial, although the system can ensure greater isolation and consistency due to better isolation, each transaction between conflicts and deadlocks have increased, It greatly affects system performance.
(7) Use Bound Connections. Bound connections allows two or more transactions to share transactions and locks, and any transaction connection To apply a lock as if another transaction is to apply, it can allow these transactions to share data without locking conflicts.
(8) Consider using optimism locking or makes the transaction first get a monopoly lock. One of the most common deadlocks occurs in the series number generator, which is usually written like this:
Begin TRAN
Select new_id from Keytab Holdlock
Update keytab set new_id = new_id L
Commit TRAN
If there are two users run this transaction at the same time, they will share lock and keep it. When two users try to get exclusive locks of the Keytab table, they will enter the deadlock. In order to avoid the occurrence of this, the above transaction should be rewritten into the following form: Begin TRAN
Update keytab set new_id = new_id L
SELECT New_ID from KeyTab
Commit TRAN
After rewritten in this way, only one transaction can get keytab's exclusive lock, and other processes must wait until the completion of the first transaction, which has increased the execution time, but avoids deadlock.
If you are required to have readable capabilities in a transaction, consider writing a transaction in this way to get exclusive locks of resources, and then read data. For example, if a transaction needs to retrieve the average price of all the books in the Titles table and ensure that the results will not change before Update is applied, the optimizer assigns an exclusive table lock. Consider the following SQL code:
Begin TRAN
Update titles set title_idid = title_id.
WHERE 1 = 2
Selectavg (Price) "> $ 15
Begin
/ * Perform Some Additional Processing * /
end
Update Titles set price = price * 1.10
WHERE PRICE <(SELECT AVG (Price) from titles)
Commit TRAN
In this business, it is important that there is no other process to modify the price of any line in the table, or the value retrieved at the end of the transaction is different from the transaction. The WHERE clause here looks strange, but no matter whether you believe or, this is the most perfect and effective WHERE clause that has been encountered so far, although the calculated results are always False. When an optimizer processes this query, because it can't find any valid SARG, its query plan will force the use of exclusive locks to perform table scans. When this transaction is executed, the WHERE clause get a false value immediately, so it does not perform actual scanning, but this process still has a exclusive table lock.
Because this process now has an exclusive table lock, it can guarantee that no other transactions will modify any data lines, which can be repeatedly read, and avoid the potential deadlock caused by Holdlock. However, to avoid dead locks, it is impossible to do not pay the price. While using table locks to reduce deadlocks as much as possible, the pairing of the table lock is also added. Therefore, before implementing this method, you need to wear: Avoiding whether the deadlock is more important than allowing concurrency to the form.
Hand-locking
SQL Server system is recommended to automatically manage the lock, which analyzes the user's SQL statement requirements, automatically adds a suitable lock for the request, and the system will automatically lock up. As mentioned earlier, the upgrade threshold is automatically configured by the system and does not require user configuration.
In practical applications, sometimes it is possible to lock a table for the database for the application correctly and maintains the consistency of data. For example, in a transaction operation in an application, you need to do statistical operations based on a number, in order to ensure the consistency and correctness of the statistics time, from the first table of statistics to all the tables, others Applications or transactions can no longer write data for these tables. At this time, the application wishes to lock this in the first data table from statistics or at the beginning of the transaction. A table, which requires a manual lock (also known as an explicit lock) technology. The SQL Server's SQL statement (SELECT, INSERT, DELETE, UPDATE) supports explicit locking. These four statements are similar in explicitly locking syntax, and below is given as an example of the SELECT statement:
SELECT
From
[With
]
among them,
Refers to a lock type that needs to be added to the table when the statement is executed.
The specified lock type is as follows:
1. Holdlock: Keep the shared lock on the table until the end of the entire transaction, not the addition of the plock added immediately after the statement is executed.
2. NOLOCK: Do not add shared locks and row locks, when this option takes effect, you may read data or "dirty data" that is not submitted, this option is only applied to the SELECT statement.
3. PAGLOCK: Specifies the page lock (otherwise it usually may add a table lock).
4. Readcommitted: Sets the transaction to read submit the isolation level.
5. ReadPast: Skip the locked data line, this option will make the transaction to read the data rows that have been locked by other transactions, not blocking until other transaction release locks, readpast is only applied to the read committed isolation level SELECT statement operation in the transaction operation.
6. ReadunCommitted: Is equivalent to NOLOCK.
7. REPEATABLEREAD: Sets the transaction to reusable interval level.
8. Rowlock: Specifies the use of a row lock.
9. Serializable: Sets the transaction as a serialized isolation level.
10. Tablock: Specifies the use of a table-level lock instead of using a chart or page-level lock, SQL Server is released after the statement is executed, and if Holdlock is specified, the lock has been kept until this transaction ends.
11. Tablockx: Specifies the use of a row lock on the table, which prevents other transactions from read or updates the data of this table until this statement or the entire transaction ends.
12. UPDLOCK: Specifies to set the modified lock when data in the table is not set, which is kept until this statement or the entire transaction ends, using the UPDLock's role is to allow users to first read data (and do not block other users) Read data) and guarantees that this data is not modified by other users during this time when it is later updated.
