{"id":394104,"date":"2024-06-29T11:24:30","date_gmt":"2024-06-29T11:24:30","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=394104"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=394104","title":{"rendered":"Locks in PostgreSQL: 4. Locks in memory<\/span>"},"content":{"rendered":"
<\/div>\n
\n
\n
\n
To remind you, we’ve already talked about relation-level locks<\/a>, row-level locks<\/a>, locks on other objects<\/a> (including predicate locks) and interrelationships of different types of locks.<\/p>\n

The following discussion of locks in RAM<\/strong> finishes this series of articles. We will consider spinlocks, lightweight locks and buffer pins, as well as events monitoring tools and sampling.<\/p>\n


<\/a> <\/p>\n

Spinlocks<\/h1>\n

Unlike normal, \u00abheavy-weight\u00bb, locks, to protect structures in the shared memory, more lightweight and less expensive (in overhead costs) locks are used.<\/p>\n

The simplest of them are spinlocks<\/em>. They are meant to be acquired for very short time intervals (a few processor instructions), and they protect separate memory areas from simultaneous changes.<\/p>\n

Spinlocks are implemented based on atomic processor instructions, such as compare-and-swap. They support the only, exclusive, mode. If a lock is acquired, a waiting process performs busy waiting\u00a0\u2014 the command is repeated (\u00abspins\u00bb in a loop, hence the name) until it is a success. This makes sense since spinlocks are used in the cases where the probability of a conflict is estimated as very low.<\/p>\n

Spinlocks do not enable detection of deadlocks (PostgreSQL developers take care of this) and provide no monitoring tools. Essentially, the only thing we can do with spinlocks is to be aware of their existence.<\/p>\n

Lightweight locks<\/h1>\n

So-called lightweight locks<\/em> (lwlocks) come next.<\/p>\n

They get acquired for a short time that is needed to work with the data structure (such as a hash table or a list of pointers). As a rule, a lightweight lock is held briefly, but sometimes lightweight locks protect input\/output operations, so in general, the time might also be considerable.<\/p>\n

Two modes are supported: exclusive (for data modifications) and shared (only for reading). There is actually no wait queue: if a few processes wait for release of a lock, one of them will get the access in a more or less random fashion. In high-concurrency and large-load systems, this can be troublesome (for example, see this discussion<\/a>).<\/p>\n

There are no techniques to check for deadlocks, so this is left to the responsibility of developers of the core. However, lightweight locks have monitoring tools, so, unlike spinlocks, we can \u00absee\u00bb them (I will show a bit later how to do this).<\/p>\n

Buffer pin<\/h1>\n

Yet another type of locks, which we already touched upon in the article on the buffer cache<\/a>, is a buffer pin<\/em>.<\/p>\n

Different operations, including data modifications, can be performed with a pinned buffer, but under the condition that the changes are not visible to other processes due to multiversion concurrency control. That\u00a0is, we can, for instance, add a new row to the page, but cannot replace a page in the buffer with another one.<\/p>\n

If a buffer pin hinders a process, as a rule, the latter just skips this buffer and chooses a different one. But in some cases, where exactly this buffer is needed, the process queues and \u00abfalls asleep\u00bb; the system will wake it up when the buffer is unpinned.<\/p>\n

Monitoring can access waits related to buffer pins.<\/p>\n

Example: buffer cache<\/h1>\n

<\/p>\n

Now, in order to get some (although incomplete!) insight into how and where locks are used, let’s consider the buffer cache as an example.<\/p>\n

To access a hash table that contains links to buffers, a process must acquire a lightweight buffer mapping lock<\/em> in a shared mode, and if the table needs to be updated, in an exclusive mode. To reduce the granularity, this lock is structured as a tranche<\/em> that consists of 128 separate locks, each protecting its own part of the hash table.<\/p>\n

The process gets access to the buffer header using a spinlock. Certain operations (such as a counter increment) can also be performed without explicit locking, by means of atomic processor instructions.<\/p>\n

In order to read the contents of a buffer, a buffer content lock<\/em> is needed. It is usually acquired only for the time needed to read pointers to tuples, and after that, a buffer pin provides sufficient protection. To change the contents of a buffer, this lock must be acquired in an exclusive mode.<\/p>\n

When a buffer is read from disk (or written to disk), an IO in progress<\/em> lock is also acquired, which indicates to other processes that the page is being read (or written)\u00a0\u2014 they can queue up if they need to do something with this page.<\/p>\n

Pointers to free buffers and to the next victim are protected by one buffer strategy lock<\/em> spinlock.<\/p>\n

Example: WAL buffers<\/h1>\n


WAL buffers provide another example.<\/p>\n

The WAL cache also uses a hash table that contains mapping of pages to buffers. Unlike for the buffer cache, this hash table is protected by the only lightweight WALBufMappingLock<\/code> lock since the size of the WAL cache is smaller (usually 1\/32 of the buffer cache) and access to the buffers is more regular.<\/p>\n

Writes of pages to disk are protected by the WALWriteLock<\/code> lock, so that only one process can perform this operation at a time.<\/p>\n

To create a WAL record, the process must first allocate space in a WAL page. To do this, it acquires an insert position lock<\/em> spinlock. When the space is allocated, the process copies the contents of its record to the space allocated. Copying can be performed by several processes simultaneously, therefore, the record is protected by a tranche of 8 lightweight wal insert lock<\/em> locks (the process must acquire any<\/em> of them).<\/p>\n

The figure does not show all WAL-related locks, but this and previous examples must give an idea of how locks in RAM are used.<\/p>\n

Wait events monitoring<\/h1>\n

Starting with PostgreSQL\u00a09.6, the pg_stat_activity<\/code> view has built-in events monitoring tools. When a process (system or backend) cannot do its job and waits for something, we can see this wait in the view: the wait_event_type<\/code> column shows the wait type and the wait_event<\/code> column shows the name of a specific wait.<\/p>\n

Note that the view shows only waits that are properly handled in the source code. If the view does not show a wait, in general, this does not mean with 100 percent probability that the process really waits for nothing.<\/p>\n

Unfortunately, the only available information on waits is the current<\/em> information. No accumulated statistics are maintained. The only way to get the picture of waits in time is sampling<\/em> the state of the view at a certain interval. No built-in tools are provided to this end, but we can use extensions, such as pg_wait_sampling<\/a>.<\/p>\n

We need to take into account the probabilistic nature of sampling. To get a more or less credible picture, the number of measurements must be pretty large. Low-frequency sampling may fail to provide a credible picture, while use of higher frequencies will increase overhead costs. For the same reason, sampling is useless to analyze short-lived sessions.<\/p>\n

All the waits can be divided into several types.<\/p>\n

Waits for the locks discussed make up a large category:<\/p>\n