Spatial management
The lock is a primary means to prevent other transactions from accessing the specified resource control to implement concurrent control. In order to improve the performance of the system, speed up the processing speed of the transaction, shorten the waiting time of the transaction, and minimize the locked resource. In order to control the locked resources, you should first understand the spatial management of the system. In the SQL Server system, the smallest space management unit is a page, one page has 8K. All data, logs, indexes are stored on the page. In addition, using the page has a limit, this is the row of data in the table must be on the same page and cannot be crosspage. The spatial management unit on the page is cluster, a cluster is 8 consecutive pages. The minimum occupancy unit of the table and index is cluster. The database is composed of one or more tables or indexes, that is, consisting of a plurality of clusters. The spatial management structure of the SQL Server system is shown in Figure 3.
Figure 3 SQL Server Space Management
Resource that can be locked
To optimize the concurrency of the system, different resources should be locked according to the size of the transaction and the extent of the system activity. That is, it can lock the relatively large resource, or the relatively small resource can also be locked. These requirements have been perfect in the SQL Server system. In SQL Server 7.0, there are many resources that can be locked. These can be locked, pages, clusters, tables, and databases, their corresponding locks, page-level locks, cluster locks, tables Level lock and database level lock. In the structure shown in FIG. 4, the data row is placed on the page, and the page is placed on the cluster, and a table has several clusters, and several tables form a database. In these resources that can be locked, the most basic resources are rows, pages, and tables, and clusters and databases are specially lockable resources.
Figure 4 Table, page, line structure diagram
The line is the minimum space that can be locked. In SQL Server 7.0, a row-level lock is implemented. Row-level locks refer to transactions to lock a row or several row data during the process of manipulating data, and other transactions cannot handle data for these rows at the same time. Row-level locks are least least, so during the process of transaction, other transactions are allowed to continue manipulating the same table or other data on the same page, greatly reducing other transactions waiting processing time, and improving the system's concurrency. The page lock is the best lock because the row-level lock is not possible to have a waste of data that is occupied and not used. In Fig. 5, the elliptical represents the data occupied by the row-level lock, and other data other than the elliptical can still be used by other transactions. The row-level lock is an important feature of SQL Server 7.0, which introduces changes in the data storage engine.
Figure 5 row lock
The page lock refers to the transaction processing, and each time you lock a page, the data on this page cannot be manipulated by other transactions. Before SQL Server 7.0, the page level lock is used. Page-locking resources are more data resources that are locked by the row-level lock. In the page lock, even if a transaction only manipulates a line of data on the page, then other data rows on this page cannot be used by other transactions. Therefore, when using the page level, the data is wasted, that is, data is occupied on the same page, but there is no use. In this phenomenon, the waste of data does not exceed the data line on one page. In Fig. 6, the circular zone represents a page-level lock, in which only one transaction can use data in the circular zone, and other transactions can only use data other than the circular zone.
Figure 6
Cluster locks are a special type of lock that can only be used in some special situations. Cluster locks refer to a cluster of transactions, which cannot be occupied by other transactions at the same time. For example, when creating a database and creation table, the system is used to use this type of lock when the physical space is assigned. The system is allocated in the cluster. When the system allocates space, use the cluster lock to prevent other transactions from using the same cluster. This type of cluster lock is no longer used after the system completes the allocation space. In particular, the cluster-level lock is not used when transaction to the data operation is involved. The structure of the cluster lock is shown in Figure 7. The elliptical region represents data occupied by cluster-level locks, and other transactions can only use other clusters other than the cluster. Figure 7 cluster lock
Table-level locks are also a very important lock. The table-level lock refers to the entire table where the transaction is operated in the manipulation of a table, and other transactions cannot access other data in the table. When the data volume of the transaction is relatively large, a table-level lock is usually used. The table-level lock is characterized by a relatively small system resource, but it takes up more data resources. Compared to the row lock and page lock, the system resources used by the table-level lock, such as more memory, but the occupied data resources are the largest. In the table-level lock, there may be a lot of waste of data, because the table-level lock locks the entire table, and other transactions cannot operate other data in the table. In this way, it will extend other transactions waiting to be processed, reduce the concurrency performance of the system. Schematic diagram of the table-level lock As shown in Figure 8, the elliptical represents a table-level lock.
Figure 8 table-level lock
The database-level lock refers to locking the entire database to prevent any user or transaction from accessing the locked database. The database-level lock is a very special lock, which is just 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 to the database, you need to set the database as a single user mode so that the system can prevent other users from performing various operations on the database. The structure diagram of the database-level lock is shown in Figure 9. Strictly speaking, the database-level lock is not a lock, but a single user mode mechanism for a similar lock. However, this single user mode mechanism is very similar to the mechanism, so this single user mode can also be referred to as a database-level lock.
Figure 9 Database level lock
Lock type and its compatibility
There are two basic forms of locking resources. One form is a shared lock required by the read operation, and another form is a row of locking requirements. In addition to these two basic types, there are also some special cases of locks, such as intent, modify the lock, and mode locks. In these various types of locks, some types of locks can be compatible, and some types of locks are not compatible.
The shared lock allows parallel transactions to read the same resource, and the transaction cannot modify the data accessed. When using a shared lock to lock resources, transaction access data for data is not allowed. When the transaction reads the data after reading the data, immediately release the resource occupied. Generally, when using the SELECT statement access data, the system automatically uses a shared lock lock to the accessible data. For those transactions that modify data, such as using INSERT, UPDATE, and DELETE statements, the system automatically places it on the modified transaction. The lock is only allowed to access a resource in the same time, and other transactions cannot be accessed on resources with row. On resources with rows it, you cannot place shared locks, which means that you can generate shared locks to access these resources. Only after the end of the transaction is generated, the resource that locks the lock can be used by other transactions.
In addition to the basic locks above, SQL Server can also use some other types of locks according to different situations. These special types of locks include the intent lock, modify the lock and mode lock.
The system makes the conflict between the minimum locks. It is a hierarchical structure that is intended to establish a lock mechanism. This structure is based on the low to high from low to high. The lock indicates that the system wants to share the shared lock or the lock lock on the low level of resources. For example, the intent lock placed on a table level indicates that a transaction can be placed on the page or on the table or on the line. Setting the shared lock to prevent another modified transaction in the table in the table level to place the transaction on the table containing the page. The intent lock can improve performance because the system only needs to check the intent lock on the table level, determine if a transaction can safely get a lock in which one is safe, without checking every line lock or page lock on the table. Determine if a transaction can lock the entire table. Three types of intent locks, exclusive chart sharing lock, intended to lock and use the shared lock of it. The intention of sharing lock means reading a small-level resource, put the shared lock on these single resources. The intention to lock it to indicate the intention of modifying a low-level transaction, lock the drain it on these single resources. The intention to the lock includes the intention to share the lock, it is intended to share the supercoming supercoming. Use the shared lock of the intention to represent the intent of the transaction that allows parallel to read the top resource, modify some low-level resources, and lock it to these single resources. For example, a shared lock on the table puts the shared lock to the shared lock on the table, allowing parallel to read, and lock it to the page that will be modified, put it on the modified row . Each table can only have a shared lock that is intended to use because the table-level shared lock prevents any modifications to the table. The shared lock that is intended to row it is a combination of shared locks and intentions. Use the modified lock when the system will modify a page. Before the system modifies this page, the system automatically rises this modified page lock to the row of the page lock and prevents a conflict between the lock. When the page is first read, the modified lock is obtained at the beginning of the modified operation. Modifying the lock is compatible with the shared lock. If the page is modified, the modified lock rises to the row lock.
The mode lock ensures that the structure mode cannot be deleted or modified when the table or index is referred to by another session. The SQL Server system provides two types of mode locks: mode stabilization locks and mode modified locks. Mode Stable Lock ensures that the locked resource cannot be deleted, the mode modified lock ensures that other sessions cannot refer to the resources being modified.
Some locks are compatible, such as between the shared locks and modified locks. Some locks are incompatible, such as row lock and shared locks. Table 1 below lists compatibility between the various locks provided by the SQL Server system.
Table 1 Compatibility between the various locks provided by the SQL Server system
Lock Name Is SU IX Six X intents shared lock (IS) compatible compatible compatible compatible compatible shared lock (S) compatible compatible compatible non-compatible non-compatible modification lock (U) compatible compatible is not compatible and incompatible is not compatible It is not compatible and incompatible is not compatible. Not compatible is not compatible
In addition, in addition to the compatibility between the locks listed in Table 1, for the mode lock, the mode modified lock is incompatible for all locks, and the mode stabilization lock is a full lock other than the mode modified lock. It is compatible.
