Shulou(Shulou.com)06/01 Report--

This article will explain in detail how the investigation about SQL SERVER lock upgrade is, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

In the process of analyzing SQL SERVER deadlocks a few days ago, I checked that there were some inexplicable deadlocks, two transactions that didn't match at all, locked together. WHY, in fact, SQL SERVER is a wonderful and unique lock upgrade technology in the database world. I don't think you have heard of the lock upgrade problems mentioned in MYSQL, ORACLE, PG, MONGODB databases.

When it comes to the problem of upgrading the lock to the quota, we need to mention why the lock is upgraded and where the lock is upgraded from. So there is this text, something about lock upgrade.

First of all, when we talk about locks, we first stand at the starting line of discussion, that is, locks occur at the memory level, and the beginning and end of locks are accompanied by the beginning and end of "transactions." After reaching the above consensus, we will begin some of the following discussions and studies.

When the transaction manager receives a commit request, it sends a prepare command to all resource managers involved in the transaction. Each resource manager then performs all the operations necessary to make the transaction persistent and flushes all buffers that hold the transaction log image to disk. When each resource manager completes the preparation phase, it returns the success or failure of the preparation to the transaction manager.

If the transaction manager receives a successful preparation from all resource managers, it sends a commit command to each resource manager. The resource manager can then complete the submission. If all resource managers report a successful commit, the transaction manager sends a success notification to the application. If any resource manager reports a failed preparation, the transaction manager sends a rollback command to each resource manager and indicates that the commit failed to the application.

What are the possible factors for what kind of lock SQL SERVER will choose when?

Here are some samples of resources in SQL SERVER that can be locked

RID does not establish a row lock in the ROW IDKEY index for identifying rows in the clustered index (HEAP TABLE) to protect the page page of the key range PAGE8KB in a serializable transaction, as a locking unit EXTENT consecutive eight pages as a locked unit HoBT heap or b-tree. Protects locks on b-trees (indexes) or heap data pages that do not have a clustered index in the table. TABLE table

Question: why are there so many types of locks? doesn't MYSQL innodb just have row lock?

As a commercial database, at the beginning of its design, SQL SERVER considered the following diagram (assuming) that using low-level locks (such as row locks) can reduce the probability that two transactions request locks on the same data block at the same time, thus increasing concurrency, but using low-level locks also increases the number of locks and the resources needed to manage locks. Conversely, using table or page locks can reduce overhead, but at the cost of reducing concurrency.

The SQL Server database engine uses a dynamic locking strategy to determine the most cost-effective locks. The database engine automatically determines which locks are most appropriate when executing the query based on the characteristics of the schema and query. For example, to reduce locking overhead, the optimizer can select page-level locks in the index when performing an index scan. The benefit of this is also obvious. If I have multiple rows in a PAGE and all need to be changed, the system will lock the PAGE according to the resources that need to be indexed, rather than every row in a page, because to consider the management, application, and release of each lock, all require relevant CPU resources, memory resources, if the concurrency is not affected. The effective control of the granularity of the lock is helpful to access and modify the information of the system.

And SQL SERVER can also be created in the table, or the use of lock release can be automatically upgraded settings, you can open the table lock upgrade, or disable it.

Having to talk about the history of SQL SERVER locks here, before SQL SERVER 7. 0 (probably not when I was born, a long time ago), SQL SERVER did not support ROW locks, but only supported PAGE LOCK, and one page was 2KB. In SQL SERVER 7. 0, they changed SQL SERVER to 8KB's PAGE and began to support ROW LOCK. So why on earth can't SQL SERVER be made into a database that only supports row locks like MYSQL, and why commercial databases with a single database still have advantages (note that this is a question)

The following is the structure of a row lock

A lock is a 64-or 128-byte memory structure (for 32-bit or 64-bit machines, respectively), and each process that holds or requests a lock has an additional 32 or 64 bytes. If you need to use locks for each row and scan 1 million lines, you need to exceed the RAM of 64MB to hold locks for the process.

The number of locks held by a statement on an object exceeds a threshold. For example, the current threshold is 5000 locks, which will trigger esclation. If the locks are distributed on multiple objects in the same statement, lock escalation will not occur-for example, 3000 locks in one index and 3000 locks in another index. On the other hand, if the lock resource takes up more than 40% of the enabled memory, the lock will be upgraded.

Whether the lock upgrade is good or not, the answer is hehe, I think you know what I mean. Why

When lock escalation is triggered, if there are conflicting locks, more X locks will be added first (I think you should understand this process), and if there are concurrent X locks on the same table or partition held by different processes, the lock upgrade attempt will fail. Each time a transaction acquires more locks on the same object, SQL Server continues to attempt to upgrade the lock, releasing all row locks on the SQL Server index or heap table upon success.

You can think of this lock upgrade from top to bottom description, the trigger is accompanied by a large transaction, occupy more memory, the system is trapped in a possible busy state, then the lock upgrade may be successful, may fail, at the same time, the lock level will change from ROW to PAGE or a broader lock, and the system will change from SHARE to ONLY You use it. If you access your resources for other matters, you don't care if they live or die.

This is not the worst, lock upgrade will lead to more deadlocks, and it is inexplicable, it seems that two fundamentally unrelated transactions will also be locked together. I think at this time if your leader comes to ask you why there are so many inexplicable deadlocks, you can make clear with him for good reason.

On how the SQL SERVER lock upgrade investigation is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.