Analysis: How to quickly master the SQL Server Lock Mechanism

Generally, users can use the system's automatic lock management mechanism to meet usage requirements. However, if there are special requirements for data security, database integrity, and consistency, you need to understand the SQL Server lock mechanism and master the database lock methods.

The basic theories of locks used by various large databases are consistent, but their implementations vary. SQL Server More emphasis is placed on the system to manage locks. When a user has an SQL request, the system analysis request automatically adds an appropriate lock to the database between the locking condition and the system performance. At the same time, the system often performs optimization during operation, implement Dynamic locking. Generally, users can use the system's automatic lock management mechanism to meet usage requirements. However, if there are special requirements for data security, database integrity, and consistency, you need to understand the SQL Server lock mechanism and master the database lock methods.

 

Locks are a very important concept in databases. They are mainly used to ensure database integrity and consistency in multi-user environments. We know that data inconsistency occurs when multiple users can manipulate data in the same database at the same time. That is, if the transaction is not locked and multiple users access a database at the same time, the transaction may use the same data at the same time. Problem . These problems include loss of updates, dirty reads, non-repeated reads, and Phantom reads:

 

1. When two or more transactions select the same row and update the row based on the originally selected value, the update will be lost. Every transaction does not know the existence of other transactions. The last update will overwrite the updates made by other firms, which will lead to data loss. For example, two editors make an electronic copy of the same document. Each editor independently changes its copy and saves the modified copy to overwrite the original document. Finally, the edited member who saves the change copy overwrites the changes made by the first edited member. This problem can be avoided if the second editor can make changes only after the first editor is complete.

 

2. dirty read means that when a transaction is accessing data and modifying the data has not been committed to the database, another transaction also accesses the data, then the data is used. Because the data has not been committed, the data read by another transaction is dirty data, and the operations performed based on the dirty data may be incorrect. For example, an editor is changing an electronic document. During the change process, another editor copied the document (this copy contains all changes made so far) and distributed it to the expected users. After that, the first editor thought that the current change was incorrect, so he deleted the edit and saved the document. Documents distributed to users include edited content that no longer exists, and such edited content should never be considered as existent. This issue can be avoided if no one can read the modified document before the first editor determines the final change.

 

3. Non-repeated read refers to reading the same data multiple times in a transaction. When the transaction is not completed, another transaction also accesses the same data. Therefore, the data read twice in the first transaction may be different because of the modification of the second transaction. In this way, the data read twice in a transaction is different, so it is called non-repeated read. For example, an editor reads the same document twice, but the author overwrites the document between the two reads. The document has been changed when the editor reads the document for the second time. The original reads cannot be repeated. This issue can be avoided if the editor can read the document only after the author has completed writing.

 

4. Phantom read refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all the data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still data rows in the table that have not been modified, just like an illusion. For example, an editor changes the document submitted by the author, but when the production department merges the changes into the primary copy of the document, the author has added unedited new materials to this document. This issue can be avoided if no one can add new materials to the document before the editors and production departments process the original document.

 

Therefore, the locking method is used to process concurrent access by multiple users. Lock is a major means to prevent other transactions from accessing specified resource control and implement concurrency control. When a user locks an object in the database, other users cannot access the object. The impact of locking on concurrent access is reflected in the lock granularity. To control locked resources, you should first understand the system space management. In SQL Server 2000, the smallest unit of space management is a page, with a page of 8 K. All data, logs, and indexes are stored on the page. In addition, there is a restriction on the use of pages. This means that a row of data in the table must be on the same page and cannot be cross-page. The space management unit on the page is the disk area, and a disk area is eight consecutive pages. The smallest unit used by tables and indexes is the disk area. A database consists of one or more tables or indexes, that is, multiple disk partitions. The lock placed on a table restricts concurrent access to the entire table; the lock placed on the disk area limits access to the entire disk area; the lock placed on the data page limits access to the entire data page; the lock placed on the row only limits concurrent access to the row.

 

SQL Server 2000 has multi-granularity locking, allowing a transaction to lock different types of resources. To minimize the cost of locking, SQL Server automatically locks resources at the appropriate task level. Locking at a small granularity (such as rows) can increase concurrency but requires a large overhead, because if many rows are locked, more locks need to be controlled. Locking at a large granularity (such as a table) is expensive in terms of concurrency, because locking the entire table limits access to any part of the table by other transactions, but requires a low overhead, because the number of locks to be maintained is small. SQL Server can lock rows, pages, extended disk areas, tables, databases, and other resources.

 

Rows are the smallest space that can be locked, and row-level locks consume the least amount of data resources. Therefore, during transaction processing, other transactions are allowed to continue to manipulate other data in the same table or on the same page, it greatly reduces the waiting time of other transactions and improves the concurrency of the system.