Database Transactions and Locks (iv)

Data | database


Space Management





locks are a major means of preventing other transactions from accessing the specified resource control and implementing concurrency control. In order to improve the performance of the system, speed up transaction processing and shorten the waiting time of the transaction, the locked resources should be minimized. In order to control the locked resources, we should first understand the space management of the system. In the SQL Server 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 a cluster, and a cluster is 8 consecutive pages. The minimum occupancy unit of a table and index is a cluster. A database consists of one or more tables or indexes, which consist of multiple clusters. The schematic diagram of the space management structure of the SQL Server system is shown in Figure 3.














Figure 3 SQL Server space management





resources that can be locked





in order to optimize the concurrency of the system, different resources should be locked according to the size of the transaction and the degree of system activity. That is, you can lock up larger resources, or you can lock down relatively small resources. In the SQL Server system, these requirements have been implemented in a relatively complete manner. In SQL Server 7.0, there are a number of resources that can be locked, which are rows, pages, clusters, tables, and databases, respectively, row-level locks, page-level locks, cluster-level locks, table-level locks, and database-level locks. In the structure shown in Figure 4, the data rows are stored on the page, the pages are stored on a cluster, a table has several clusters, and several tables form the database. Of these resources that can be locked, the most basic resources are rows, pages, and tables, and the clusters and databases are special resources that can be locked.














Figure 4 Table, page, row structure





A row is the smallest space that can be locked. In SQL Server 7.0, row-level locks are implemented. Row-level locking is when a transaction locks a row or rows of data in the process of manipulating data, and other transactions cannot simultaneously process data for those rows. Row-level locks occupy the least amount of data resources, 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 locks are an optimal lock because row-level locks are not likely to cause a waste of data being both occupied and unused. In Figure 5, the ellipse represents the data that the row-level lock occupies, and other data other than the ellipse can still be used by other transactions. Row-level locks are an important feature of SQL Server 7.0, and their introduction causes changes in the data storage engine.














Figure 5 Row-level lock





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, each time a page is locked. 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. In Figure 6, a circular region represents a page-level lock in which only one transaction can use data from a circular region, and other transactions can only use data outside the circle.














Figure 6 Page-level lock





A cluster 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 cluster 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 allocates space according to the cluster. When the system allocates space, a cluster lock is used to prevent other transactions from using the same cluster at the same time. Once the system has finished allocating space, this type of cluster lock is no longer used. In particular, cluster locks are not used when transactions involving data operations are involved. The structure of the cluster lock is shown in Figure 7. An oval region represents data that is occupied by a cluster-level lock, and other transactions can only use clusters other than that cluster.











FIG. 7 Cluster-level lock





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. This increases the time that other transactions are waiting to be processed and reduces the concurrency performance of the system. The schematic diagram of a table-level lock is shown in Figure 8, and the ellipse represents a table-level lock.











Figure 8 Table-level lock





database-level locking means locking 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 during the recovery operation of a 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. The schematic diagram of the database-level lock is shown in Figure 9. Strictly speaking, database-level lock is not a kind of lock, but a kind of single user mode mechanism similar to lock. However, this single user mode mechanism is very similar to the lock mechanism, so it is possible to call this Single-user mode a database-level lock.














Figure 9 Database-level lock




Type of
lock and its compatibility




There are two basic forms of
locking resources, one in the form of shared locks required for read operations, and another in the exclusive lock required for write operations. In addition to the two basic types, there are special case locks, such as intent locks, modify locks, and schema locks. In these various types of locks, some types of locks are compatible and some types of locks are incompatible.




A
share lock allows a parallel transaction to read the same resource, and the transaction cannot modify the access data. When a shared lock is used to lock a resource, transaction access data is not allowed to modify data. When the transaction reading the data finishes reading the data, immediately releases the resource that is occupied. Generally, when you use a SELECT statement to access data, the system automatically uses a shared lock lock on the data that you access. For transactions that modify data, such as the Insert, UPDATE, and DELETE statements, the system automatically places an exclusive lock on the modified transaction. Exclusive locks allow only one transaction to access one resource at a time, and no other transaction can be accessed on a resource that has exclusive locks. On resources with exclusive locks, shared locks cannot be placed, meaning that transactions that can generate shared locks are not allowed to access those resources. Exclusive lock-locked resources can be used by other transactions only after the transaction that generated the exclusive lock ends.





In addition to the basic locks above, SQL Server can also use some other types of locks, depending on the situation. These special types of locks include intent locks, modify locks, and schema locks.





The system uses intent locks to minimize conflicts between locks. An intent lock establishes a hierarchical structure of locking mechanisms, which are row-level, page-level, and table-level lock layers, from low to high, according to the locked resource range. Intent locks indicate that the system wants to acquire shared or exclusive locks on lower-level resources. For example, an intent lock placed at the table level indicates that a transaction can place a shared lock on a page or row in a table. Setting a shared lock at the table level prevents another transaction that modifies pages in the table from being placed on the table that contains the page. Intent locks can improve performance because the system only needs to check the intent lock at the table level to determine the table on which a transaction can safely acquire a lock, without having to check every row or page lock on the table to determine whether a transaction could lock the entire table. There are three types of intent locks, that is, intent to share locks, intent to exclusive locks, and shared locks that use intent to exclusive. Intent to share a lock represents the intention of reading a transaction for a low-level resource, placing the shared lock on these individual resources. Intent to exclusive locks represents the intention to modify low-level transactions, placing the exclusive lock on these individual resources. The intent exclusive lock includes an intent to share a lock, which is a superset of the intent to share the lock. A shared lock with intent is used to indicate the intent to allow concurrent reading of the transaction at the top-level resource, and to modify some low-level resources to lock the intent on these individual resources. For example, a shared lock on a table that uses intent to row it places a shared lock on a table, allows parallel reads, and locks the intent on the page that will be modified, placing the exclusive lock on the modified line. Each table can have only one shared lock that uses intent at a time because table-level shared locks prevent any modifications to the table. A shared lock that uses intent to row it is a combination of shared and intent exclusive locks.





Use the Modify lock when the system will modify a page. Before the system modifies the page, the system automatically rises the change page lock to the exclusive page lock to prevent conflicts between the locks. When the page is first read, the modify lock is obtained at the beginning of the modification operation. Modifying a lock is compatible with a shared lock. If the page is modified, modify the lock to rise to the exclusive lock.




The
mode lock guarantees that when a table or index is referenced by another session, it cannot be deleted or modified in its structure mode. The SQL Server system provides two types of schema locks: schema-stabilized locks and schema-modification locks. Pattern-stabilized locks ensure that locked resources cannot be deleted, and schema modification locks ensure that other sessions cannot refer to the resource being modified.





Some locks are compatible, such as sharing locks and modifying locks. Some locks are incompatible, such as between exclusive locks and shared locks. Table 1 below lists the compatibility between the various locks provided by the SQL Server system.





Table 1 compatibility between various locks provided by SQL Server System





Lock Name





is





S





U





IX





SIX





X





Intent Shared Lock (IS)





compatible





compatible





compatible





compatible





compatible





Incompatible





shared Lock (S)





compatible





compatible





compatible





Incompatible





Incompatible





Incompatible





Modify Lock (U)





compatible





compatible





Incompatible





Incompatible





Incompatible





Incompatible





Intent Exclusive Lock (IX)





compatible





Incompatible





Incompatible





compatible





Incompatible





Incompatible





the shared lock (SIX) that is intended to be exclusive





compatible





Incompatible





Incompatible





Incompatible





Incompatible





Incompatible





exclusive Lock (X)





Incompatible





Incompatible





Incompatible





Incompatible





Incompatible





Incompatible





In addition, in addition to the compatibility of the locks listed in Table 1, schema-modification locks are incompatible with all locks for pattern locks, and schema-stabilized locks are compatible for all locks except schema-modifying locks.