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

This article will explain in detail whether there are PAGE locks and Advisory Locks in the PostgreSQ database. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The cause of the matter is that I wrote a MVCC article comparing MYSQL and PG, which mentioned that PG does not have PAGE LOCK, and some students pointed it out that PG has PAGE LOCK.

In the end, is there any PG that PAGE LOCK personally feels to find out and correct any mistakes? I also thank the "can" students who correct me.

First of all, the most intuitive way is the official document.

Https://www.postgresql.org/docs/12/explicit-locking.html

However, some students may have suggested that they did not see the separate project page level locks listed in PG 9.3 and previous versions, but actually carefully found that the previous version of page level locks was in the project Row-level locks, and it was passed in one sentence from the early 9.x writing to the current PG12.

Compared with the detailed description and functional description of PAGE LOCKS in some databases (compared to SQL SERVER), PG's description of PAGE LOCKS is a bit simple.

Since the idea of exploring secrets from the document cannot be achieved, we can only see if there is any harvest in the source code for the definition and handling of locks in the lock.h file. Open the file.

Related examples of using locktage_page locks can be found in src/backend/access/gin/ginfast.c:

LockPage (index,GIN_METAPAGE_BLKNO, ExclusiveLock)

Find the relevant program segments that use locktage_page in the

There should be other PAGE locks, but limited by time and experience, I did not continue to look for them. If there are any other locks, please do not hesitate to comment, thank you.

In fact, in the previous article, I gave a sentence that PG does not have a PAGE lock, which is indeed incorrect. I also talk about what I understand the meaning of PAGE lock and why I said that sentence.

Let's take a look at the relationship between SQL SERVER's PAGE LOCK and ROW LOCK and TABLE LOCK with a diagram.

The difference between SQL SERVER and other databases lies in the dynamic locking of resources, that is, he may have used the ROW lock at the beginning, but due to the increase in the complexity of the overall operation in the later stage, he directly upgraded the ROW lock to a page lock. At present, I have not heard a confirmation of the name Lock escalation in other databases.

In addition, SQL SERVER can be used for enabled and disabled in row and page locks, which is flexible compared to other databases.

So it is possible that from the previous understanding of some of the concept and use of page locks, PG page locks can not be arbitrarily controlled, from the application developer point of view does not need to be deeply understood.

In contrast to SQL SERVER, if programmers are dealing with some applications, it is recommended to consider the problem of lock escalation, because lock escalation will inevitably lead to more lock conflicts.

Looking back at PG's Advisory Locks, here are two examples to illustrate the role of advisory locks. Because other databases do not have such settings (if there is, please correct, at least without MYSQL, SQL SERVER is not available)

1 SESSION, let's find two SESSION SESSION 1 SESSION 2

SESSION 1

SESSION 2

From the above example, we can understand the specific functions of Advisory Locks. First of all, when MVCC can not solve the application's sequence of data processing and data uniqueness, it is used in the application through Advisory Locks, and some special requirements are achieved through SESSION level or transcation level through Advisory Locks.

As in the above example, SESSION1 inserts data, but SESSION 2 needs to delete data with ID = 1, but the question is how to determine that data already exists in the data table in SESSION1. If it is another database, it may need other means, such as setting state bits in the table to constantly judge, but in fact, this creates locks, and any operation on the tables in the database will produce locks. This leads to the contention of database resources. In the case of frequently obtaining or modifying the state, BLOCK will be generated and there may even be deadlocks. PG is friendly to the application in this respect, it will not physically consume any resources to the database through Advisory Locks, but in the application, set Advisory Locks to judge the state of each other in different SESSION or TRANSACTION, and take relative actions.

The following is an example of a transaction, when concurrent transaction 2, there is no way to know whether transaction1 inserts ID = 4 into the TEST table in the end, so pg_try_advisory_xact_lock is used to prevent transactoin 2 from making errors, which is also a way to use advisory_lock.

Transaction 1

Transaction 2

The reason why no one mentioned this institute before, it is estimated that the operation and maintenance DBA does not care, because it is related to software design, at least to understand the logic of the business and program design, and programmers can fully grasp the PG, it is estimated that fewer people than DBA, most of them are shallow taste, so learning PG must dig deep into the "three regardless" to provide more services and ways of use for program design.

This is the end of the article on "whether there are PAGE locks and Advisory Locks in PostgreSQ database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out 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.