A database buffer page level lock mode migration control method, device and medium

Abstract

The application discloses a database Buffer page level lock mode migration control method and device and medium, and relates to the technical field of database Buffer page level concurrency control. The method comprises the following steps: in response to an access request of a thread to a target buffer page, obtaining a page description structure, then determining access statistical data of the target Buffer page, and calculating a page level heat identification based on the access statistical data; in the case that the page level heat identification exceeds a preset hot spot threshold, migrating the lock mode from a first lock mode to a second lock mode; in the second lock mode, if the access request is read access, multiple read threads are concurrently entered, and the thread does not enter a global lock waiting queue; if the access request is write access, the thread sets a write barrier, waits for existing read threads to be emptied, and then executes an exclusive write operation; in the case that the current access is completed, the access statistical data is updated; and in the case that the access statistical data satisfies cooling conditions, the lock mode is restored from the second lock mode to the first lock mode through a CAS atomic operation.

Description

Technical Field

[0001] This application relates to the field of database buffer page-level concurrency control technology, and in particular to a database buffer page-level lock mode migration control method, device and medium. Background Technology

[0002] Existing database systems typically employ lightweight locking mechanisms (such as PostgreSQL's LWLock) based on shared and exclusive lock models for concurrent control of buffer pages. This approach controls access to buffer pages using lightweight locks, and its basic process includes: the backend process locates the target buffer page based on a query or update request; performs a pin operation on the target page to prevent it from being replaced; requests the corresponding Buffer Content Lock based on the operation type (shared lock for read operations, exclusive lock for write operations); if the lock is held by another process, it enters a waiting state; after acquiring the lock, it performs the page read or modification operation; and after access is complete, it releases the lock and removes the pin. The underlying infrastructure includes a Buffer Pool, BufferDesc, Buffer Mapping structure, Buffer Pinning mechanism, LWLock / Buffer Content Lock, and the backend process.

[0003] The aforementioned solutions have several technical drawbacks in high-concurrency scenarios involving hot pages. First, both read and write operations require competition for the same page-level lock structure. Frequent access to hot pages can easily lead to severe lock waits, resulting in decreased database throughput. Second, when write threads enter a waiting or exclusive access state, they may block subsequent read threads, causing increased read request latency, particularly pronounced in read-heavy, write-light scenarios. Furthermore, in NUMA multi-core environments, centralized lock states or centralized read counts can easily trigger cache line contention and atomic operation conflicts, limiting multi-core scalability. Additionally, existing solutions cannot automatically switch between different lock types. Summary of the Invention

[0004] This application provides a method, device, and medium to solve problems such as lock contention, write wait propagation, insufficient NUMA scalability, and lack of adaptive lock mode switching under high-concurrency access to hot pages.

[0005] In a first aspect, embodiments of this application provide a database buffer page-level lock mode migration control method, the method comprising: responding to a thread's access request to a target buffer page, obtaining the page description structure corresponding to the target buffer page; determining access statistics of the target buffer page based on the page description structure, and calculating a page-level hotness identifier corresponding to the target buffer page based on the access statistics; if the page-level hotness identifier exceeds a preset hotspot threshold, migrating the lock mode of the target buffer page from a first lock mode to a second lock mode through a CAS atomic operation; in the second lock mode, if the access request is a read access, multiple read threads enter concurrently, and the threads do not enter the global lock waiting queue; if the access request is a write access, the thread sets a write barrier, waits for existing read threads to clear the queue before performing an exclusive write operation; if the access is completed, updating the access statistics; if the updated access statistics meet the cooling conditions, restoring the lock mode of the target buffer page from the second lock mode to the first lock mode through a CAS atomic operation.

[0006] Secondly, embodiments of this application also provide a database buffer page-level lock mode migration control device, the device comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform a database buffer page-level lock mode migration control method as described in the first aspect above.

[0007] Thirdly, embodiments of this application also provide a computer storage medium storing computer-executable instructions, which, when executed, implement a database buffer page-level lock mode migration control method as described in the first aspect above.

[0008] The database buffer page-level lock mode migration control method, device, and medium provided in this application have the following beneficial effects: In this embodiment, a page-level heat flag is used as the trigger condition. When page heat increases, the lock mode is safely migrated from the first lock mode to the second lock mode via a CAS atomic operation. In the second lock mode, read threads can execute concurrently without entering the global lock wait queue, thereby eliminating the lock contention bottleneck and NUMA consistency overhead of hot pages and significantly improving multi-core scalability. Write threads perform exclusive write operations by setting write barriers and waiting for existing read threads to naturally empty, solving the write starvation problem caused by write wait propagation leading to increased read latency. When page heat decreases, the lock mode is restored to the lightweight first lock mode based on cooling conditions, preventing unnecessary management overhead caused by maintaining a complex lock structure for a long time. In addition, the entire migration process relies on CAS atomic operations to ensure state consistency and atomicity under high concurrency, avoiding race conditions and state overwriting caused by multi-threaded concurrent migration. In this way, adaptive, safe, and efficient dynamic switching of database buffer pool lock modes is achieved, significantly improving concurrent access performance in hot page scenarios and maintaining low overhead in ordinary page scenarios, comprehensively improving the overall throughput and resource utilization of the database system. Attached Figure Description

[0009] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings: Figure 1 A flowchart of a database buffer page-level lock morphological migration control method provided in this application embodiment; Figure 2 A flowchart illustrating hotspot page identification provided in this application embodiment; Figure 3 A flowchart illustrating lock mode migration provided in this application embodiment; Figure 4 A schematic diagram of a page-level state machine provided for an embodiment of this application; Figure 5 A flowchart illustrating a read path provided in an embodiment of this application; Figure 6 A NUMA Reader Slot structure diagram provided in this application embodiment; Figure 7 A flowchart for clearing a write barrier is provided as an embodiment of this application; Figure 8 A CAS / Epoch state transition flowchart is provided for an embodiment of this application; Figure 9 A flowchart of a database buffer page-level lock mode migration control method provided in the application scenario for embodiments of this application; Figure 10A schematic diagram of a database buffer page-level lock morphological migration control system provided in an embodiment of this application; Figure 11 This is a schematic diagram of the internal structure of a database buffer page-level lock mode migration control device provided in an embodiment of this application. Detailed Implementation

[0010] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0011] Database systems typically cache disk data pages through a buffer management module. Taking PostgreSQL as an example, it maintains a shared buffer, or Buffer Pool, in memory. When a query, update, insert, or delete operation needs to access a data page, the database backend process first searches for the corresponding buffer page in the Buffer Pool. If the target page already exists in memory, it is accessed directly; otherwise, the data page is read from disk and loaded into the Buffer Pool.

[0012] Because the Buffer Pool is a shared memory area accessed by multiple database sessions, the same Buffer page may be accessed concurrently by multiple backend processes. To ensure data consistency, database systems typically require concurrency control mechanisms for Buffer pages. These mechanisms generally include two types: shared access control and exclusive access control. Read operations typically acquire shared locks, allowing multiple read threads to access the same page concurrently; write operations typically acquire exclusive locks, requiring that other read and write accesses be excluded during the write operation to ensure the consistency of page content modifications.

[0013] In PostgreSQL, buffer page access typically involves structures or mechanisms such as BufferDesc, Buffer Tag, BufferContent Lock, Buffer Pin, and LWLock. Among these, LWLock is a commonly used lightweight locking mechanism in the PostgreSQL kernel, used to protect critical data structures in shared memory. In buffer page access scenarios, LWLock can be used to control read and write access to page content, preventing data inconsistency caused by multiple processes concurrently modifying the same page.

[0014] Under normal loads, traditional shared / exclusive locks can meet the page-level concurrency control requirements of databases. However, in high-concurrency OLTP scenarios, certain data pages are frequently accessed, such as hot index pages, frequently updated data pages, pages related to auto-incrementing primary keys, and pages related to transaction status. These pages are called hot buffer pages. Hot pages can cause a large number of processes to compete for the same page-level lock, resulting in lock waits, CPU spinning, and context switching, thus reducing the overall throughput of the database system.

[0015] Furthermore, modern database servers typically employ multi-core CPUs or NUMA architectures. In a NUMA architecture, the cost of accessing local memory differs between different CPU nodes and remote memory. If multiple CPU cores frequently modify the same shared counter or the same lock status field, it can cause frequent synchronization of the cache consistency protocol, leading to cache line bouncing and further amplifying hot page lock contention.

[0016] Therefore, in the field of database buffer page-level concurrency control, more refined page-level lock control mechanisms are needed for hot pages, high concurrency, and multi-core NUMA environments.

[0017] Existing database systems typically employ lightweight locking mechanisms (such as PostgreSQL's LWLock) based on shared and exclusive lock models for buffer page concurrency control. In high-concurrency access scenarios involving hot pages, all read and write threads compete for the same lock state, leading to increased lock waiting, spin retries, and CPU idling, ultimately reducing overall database throughput. Simultaneously, while write threads are waiting, they prevent subsequent read threads from entering, causing a rapid increase in read access latency, particularly pronounced in read-heavy, write-light scenarios. Furthermore, centralized Reader Counters in NUMA multi-core environments cause severe cache line bouncing and cache consistency overhead, further limiting multi-core scalability.

[0018] Existing page-level locking mechanisms employ a fixed lock structure, failing to dynamically adjust concurrency control strategies based on page access frequency. This results in hot pages continuously using inefficient lock models, leading to persistent contention. Furthermore, the current mechanism lacks efficient ReaderDrain control capabilities, exposing the Writer to lock queue accumulation and Starvation risks when acquiring exclusive access. More critically, existing technologies lack an atomic state transition mechanism capable of safely mitigating page-level lock configurations during runtime, hindering flexible switching between different lock control models and fundamentally limiting the system's dynamic adaptability to hot page access patterns.

[0019] To address the aforementioned problems, this application provides a database buffer page-level lock mode migration control method. The technical solution proposed in this application will be described in detail below with reference to the accompanying drawings.

[0020] Figure 1 This is a flowchart illustrating a database buffer page-level lock morphological migration control method provided in an embodiment of this application. Figure 1 As shown in the figure, the database buffer page-level lock mode migration control method provided in this application embodiment specifically includes the following steps: Step 101: In response to the thread's access request to the target buffer page, obtain the page description structure corresponding to the target buffer page.

[0021] Step 102: Based on the page description structure, determine the access statistics of the target Buffer page, and based on the access statistics, calculate the page-level popularity identifier corresponding to the target Buffer page.

[0022] In one possible implementation, the page-level heat index is generated based on at least one of the following metrics from the access statistics: read access count, write access count, cumulative lock wait time, cumulative write wait time, and number of spin retries.

[0023] Figure 2 A flowchart illustrating a method for identifying hotspot pages is shown. Figure 2 As shown, when a database thread accesses a target buffer page, it first obtains the page description structure corresponding to that target buffer page. The page description structure stores the access statistics fields of that page, including read access count, write access count, cumulative lock wait time, cumulative write wait time, spin retries, and last sample time.

[0024] The above statistical data is updated according to a preset sampling period or an access-triggered method. The access-triggered method means that instead of scanning all buffer pages at a fixed time period, the statistical fields corresponding to a specific buffer page are updated when the database thread actually accesses that page.

[0025] For example, when transaction T1 accesses buffer page P100, if T1 performs a query operation on P100, the read access count is incremented by 1; the access path is recorded based on the current lock state. If T1 performs an UPDATE operation on P100, the write access count is incremented by 1; if waiting occurs during the acquisition of the page lock, the lock wait time is accumulated. If multiple threads continuously access P100, the access count continues to increase; the lock wait time and spin count increase; it is determined that there may be hotspot contention on this page. In this way, statistics are updated only when a buffer page is accessed, avoiding the additional overhead of periodically scanning all buffer pages.

[0026] Subsequently, a page-level popularity indicator can be generated based on the aforementioned statistical data. The page-level popularity indicator is calculated by combining multiple access contention metrics, rather than solely relying on the number of accesses. For example, the page-level popularity indicator can be determined by access frequency, write access ratio, lock wait time, and spin retries.

[0027] For example, the formula for page-level popularity indicators can be defined as follows: HeatScore=A×ReadCount+B×WriteCount+C×LockWaitTime+D×SpinRetryCount+E×WriteWaitTime.

[0028] Where ReadCount is the number of read accesses per unit time, WriteCount is the number of write accesses per unit time, LockWaitTime is the cumulative lock wait time, SpinRetryCount is the number of lock spin retries, WriteWaitTime is the write request wait time, and A, B, C, D, and E are weights.

[0029] In practical applications, if a page experiences a high number of read and write accesses within a given time period, and the lock wait time continues to increase, it indicates that the page is experiencing hotspot contention. If the calculated hotspot indicator exceeds a preset hotspot threshold, the buffer page is marked as a hotspot page and enters the lock mode transition judgment process. If the threshold is not exceeded, the page continues to use the normal shared / exclusive lock mode.

[0030] For example, Buffer page A has 10,000 read accesses / s, 3,000 write accesses / s, a lock wait time of 500ms, and 10,000 spins, resulting in a HeatScore of 8500. The hotspot threshold is 5000. If Buffer page A exceeds this threshold, it is considered a hotspot page, triggering a lock mode transition.

[0031] It should be noted that the preset threshold is not arbitrarily fixed, but is determined comprehensively based on the database's normal load baseline, hotspot contention characteristics, and lock migration costs. Baseline sampling of buffer page access data can be performed during the initialization phase or early operation, and the hotspot page determination threshold can be determined based on the sampling results.

[0032] Specifically, the following methods can be used to determine the hotspot value: Within a preset baseline period, collect the number of read accesses, write accesses, lock wait times, spin retries, and write wait times for each buffer page; normalize the different metrics and calculate the hotspot value for each page; statistically analyze the distribution of the hotspot value for normal pages; set values ​​that are higher than the distribution of the hotspot value for normal pages as the hotspot threshold; and dynamically adjust this threshold based on the performance gains after migration during operation.

[0033] It should be noted that the above hotspot indicator is calculated based on access frequency, write access ratio, lock wait time and spin retries. Therefore, the threshold should be applied to the overall popularity value, rather than just to a single indicator.

[0034] In practical applications, the threshold can be determined based on historical baselines. Assuming that buffer pages are sampled over a continuous 10-minute period, and the overall popularity value of ordinary pages is mainly distributed with an average popularity value of 1200 and a standard deviation of 600, the hotspot threshold can be calculated using the following formula: Hotspot Threshold = Average Popularity Value + 3 × Standard Deviation. Therefore, the hotspot threshold = 1200 + 3 × 600 = 3000. When the overall popularity value of a buffer page exceeds 3000 and continues to reach a preset number of times or duration, the page is identified as a hotspot page. For example, if buffer page P1 has a popularity value of 1800 and continues to use a normal shared / exclusive lock, and buffer page P2 has a popularity value of 4200, it can be marked as a hotspot page and enter the lock mode transition judgment.

[0035] Alternatively, all buffer page popularity values ​​during the baseline sampling period can be sorted from smallest to largest, and the 99th percentile value can be used as the hotspot threshold. For example, if 10,000 page popularity values ​​are collected during the baseline period, and the 99th percentile popularity value is 5,000, then the hotspot threshold can be set to 5,000. When a page's popularity value exceeds 5,000 for three consecutive sampling periods, it is marked as a hotspot page. This method allows only a small subset of pages with the most access competition to be identified as hotspot pages, preventing a large number of ordinary pages from entering the ROWEX state.

[0036] In practical applications, to avoid page popularity fluctuating around a threshold, leading to frequent switching between the normal lock and ROWEX modes, it is recommended to set different entry and exit thresholds. For example, an entry threshold of 5000 and an exit threshold of 3000. If the popularity value is greater than 5000 for three consecutive periods, the NORMAL / HOT mode migrates to ROWEX; if the popularity value is less than 3000 for five consecutive periods, ROWEX enters COOLING and reverts to NORMAL. This reduces the overhead caused by frequent lock mode transitions. The state machine in the document already has a COOLING state for reverting to the normal lock mode after page popularity decreases; therefore, using dual thresholds matches the existing state design.

[0037] In practical applications, the threshold can be dynamically adjusted. For example, if the initial hotspot threshold is set to 5000, and after running for a period of time a large number of pages enter ROWEX but the lock wait time does not decrease significantly while migration overhead increases, it indicates that the threshold is too low and can be adjusted to 7000. If it is found that the lock wait time of some pages continues to increase but never reaches the hotspot threshold, it indicates that the threshold is too high and can be adjusted to 4000. Therefore, the preset threshold can be determined by the initial baseline and dynamically corrected by the performance feedback tuning module based on the decrease in lock wait time, the increase in throughput, and migration overhead.

[0038] While cumulative write wait time and most recent sampling time are not direct inputs for popularity calculation, they can be used to assist in judging changes in the status of hot pages and dynamically adjusting strategies. Specifically, cumulative write wait time can be used to determine whether there is write contention on hot pages. For example, if a buffer page has 100,000 read accesses, 100 write accesses, and a low lock wait time, this page may only be a read hotspot. Conversely, if the read access count is 50,000, the write access count is 5,000, and the write wait time continues to increase, it indicates significant write contention, requiring a higher migration priority. Therefore, cumulative write wait time can serve as a basis for judging hotspot types, a WriterDrain trigger condition, and a basis for performance feedback adjustments.

[0039] The most recent sampling time can be used to calculate the access frequency per unit time, determine whether hotspots persist, and implement hotspot cooling and rollback. For example, if a buffer page was accessed 1 million times 10 minutes ago and 10 times in the last minute, it should not remain in the ROWEX state. Therefore, the most recent sampling time can be used to determine the lifecycle of hotspots.

[0040] The above solution enables dynamic identification of hot pages at runtime, avoiding the use of complex lock patterns for all pages, thereby reducing the overhead of accessing ordinary pages.

[0041] Step 103: If the page-level heat index exceeds the preset hot spot threshold, determine the target Buffer page as a hot spot page, and use a CAS atomic operation to migrate the lock state of the target Buffer page from the first lock state to the second lock state.

[0042] In one possible implementation, the first lock type is a normal lock type, and the second lock type is a read-optimized write exclusive lock type.

[0043] That is, the first lock type can be a normal shared / exclusive lock type, and the second lock type can be a ROWEX lock type.

[0044] In one possible implementation, if the page-level heat index does not exceed the hotspot threshold, the first lock pattern is used to complete the access to the target buffer page.

[0045] Step 104: In the second lock mode, if the access request is a read access, multiple read threads enter concurrently, and the threads do not enter the global lock waiting queue; if the access request is a write access, the thread sets a write barrier and waits for the existing read threads to clear before performing an exclusive write operation.

[0046] Step 105: Once this visit is complete, update the visit statistics.

[0047] Step 106: If the updated access statistics meet the cooling conditions, restore the lock state of the target Buffer page from the second lock state to the first lock state through a CAS atomic operation.

[0048] In one possible implementation, the cooling condition is that the page-level hotness indicator is lower than a preset degradation threshold for N consecutive sampling periods, and no write access occurs for the N consecutive sampling periods, where N is an integer greater than 1, and the degradation threshold is lower than the hotspot threshold.

[0049] In practical applications, N is a preset number of consecutive sampling periods used to eliminate misjudgments caused by instantaneous fluctuations (for example, N=3 means that the condition is met for 3 consecutive sampling periods before triggering a rollback). The downgrade threshold is a preset value lower than the aforementioned hotspot threshold to prevent the lock mode from frequently switching back and forth in the critical area (jittering). No write access occurred during this period because if there was write access, it means that the page still has write pressure and it is not advisable to downgrade back to a normal lock.

[0050] For example, assuming the hotspot threshold is 100, the degradation threshold is 50, and N=3: when page A's hotspot reaches 120, an upgrade is triggered, and the lock mode migrates to ROWEX. After a period of time, page A's hotspot gradually decreases. In sampling period 1, the hotspot is 60 (above the degradation threshold of 50, not met); in sampling period 2, the hotspot is 45 (below the degradation threshold of 50, but not for 3 consecutive times); in sampling period 3, the hotspot is 40 (below the degradation threshold for the 2nd consecutive time); and in sampling period 4, the hotspot is 35 (below the degradation threshold for the 3rd consecutive time, and there were no write accesses during this period), triggering a cooling-off rollback.

[0051] In practical applications, hotspot thresholds and rollback strategies can be dynamically adjusted based on operational performance.

[0052] For example, the initial hotspot threshold is configured as follows: HeatScore > 5000 triggers ROWEX. During operation, it was found that (1) a large number of pages entered ROWEX, but the performance improvement was not significant, indicating that the threshold was too low. The threshold was adjusted from 5000 to 8000 to reduce invalid migrations; (2) access to hotspot pages continued to increase, and the lock wait was still high, indicating that the threshold was too high. The threshold was adjusted from 5000 to 3000 to trigger ROWEX in advance. The rollback strategy can also be modified according to the situation. For example, if the ROWEX state lasts for 5 minutes and the number of accesses decreases by 90%, then the normal lock mode is restored.

[0053] Figure 3 A flowchart illustrating a lock mode transition process is provided. During the transition, it is first determined whether the target buffer page has been marked as a hot page. If the page is not a hot page, no transition occurs, and the normal shared / exclusive lock mode continues. If the page is a hot page and is currently still in normal lock mode, a transition flag is set using a CAS atomic operation.

[0054] Setting a migration flag prevents multiple threads from simultaneously initiating lock-mode migrations to the same buffer page. Only the thread that successfully completes the CAS operation can perform the actual migration; other threads, upon discovering that the migration flag has been set, will either wait or retry according to their current state.

[0055] During the migration process, it is necessary to wait for any accesses currently in the normal lock state to complete to prevent normal lock accesses and ROWEX accesses from acting on the same page simultaneously. Once the normal lock access is complete, the page state can be changed to ROWEX lock state, and NUMA Reader Slot can be enabled.

[0056] This process ensures concurrent safety during lock mode switching, preventing page access state chaos during high-concurrency access.

[0057] In one possible implementation, the method further includes: setting a page-level state machine for the target buffer page, the page-level state machine including at least the following states: The NORMAL state indicates that the target Buffer page is using the first lock mode. The HOT state indicates that the page heat index of the target Buffer page exceeds the heat threshold but has not yet completed the lock mode migration; The ROWEX state indicates that the target buffer page is in the second locking mode; The WRITER_PENDING state indicates that a write thread has arrived and set a write barrier. The DRAINING state indicates that it is waiting for an existing read thread to exit; The EXCLUSIVE state indicates that a write thread has acquired exclusive access. The COOLING state indicates that the target buffer page is ready to fall back to the first lock state.

[0058] like Figure 4 As shown, a page-level state machine can be set for each buffer page. This state machine includes at least the following states: NORMAL, HOT, ROWEX, WRITER_PENDING, DRAINING, EXCLUSIVE, and COOLING. NORMAL represents a normal page state, where the page uses a normal shared / exclusive lock. HOT indicates a hotspot identification state, meaning the page's access level has exceeded a threshold, but the lock state transition has not yet been completed. ROWEX represents a read-optimized write-exclusive state, where read threads can enter the read path through the NUMA local read counter structure. WRITER_PENDING indicates that a write thread has arrived and set a write barrier. DRAINING indicates that it is waiting for an existing read thread to exit. EXCLUSIVE indicates that a write thread has obtained exclusive access and can modify the page. COOLING indicates that the page's access level has decreased, and it is preparing to fall back from the ROWEX state to the normal lock state.

[0059] The states are transitioned between each other via CAS atomic operations. The constraints for state transitions include page heat threshold, arrival of the write thread, whether the Reader Slot is zeroed out, whether the write operation is completed, and whether the cooling conditions are met.

[0060] The aforementioned state machine enables the buffer page lock to have a clear lifecycle management capability, avoiding uncertainty in the lock mode switching process.

[0061] In one possible implementation, under the second lock mode, the read access process includes: The thread reads the write barrier status of the target buffer page; If no write barrier exists, the thread enters the local Reader Slot of the corresponding NUMA node to perform a read operation, and exits the local Reader Slot after the read is completed; If a write barrier exists, the thread enters a waiting path or falls back to a normal lock waiting strategy.

[0062] In the above possible implementations, the local Reader Slot is an independent read count slot maintained for each NUMA node; the thread only modifies the read count slot corresponding to its NUMA node; the write thread summarizes and checks whether the read count slots of all NUMA nodes have returned to zero during the emptying phase.

[0063] Read path flowchart as follows Figure 5 As shown, when a read thread accesses a buffer page, it first reads the current lock state of that page. If the page is not in ROWEX state, the read thread accesses it according to the existing normal shared lock process. If the page is in ROWEX format, the read thread further checks for write barriers. If no write barrier exists, the read thread enters the local NUMA Reader Slot and performs a page read operation. After the read is complete, the read thread exits the local Reader Slot. A NUMA node is a processor grouping unit in a computer system that has local memory and local cache; when performing a read operation, the thread determines its NUMA node based on the currently running CPU core and accesses the local Reader Slot corresponding to that NUMA node.

[0064] If a write barrier exists, it means that a write thread has arrived and is preparing to enter an exclusive state. At this time, a new read thread cannot continue into the ROWEX fast path; it can enter a waiting path or fall back to the normal lock waiting strategy.

[0065] This allows hot pages to have a fast read path even without write barriers, reducing the number of times read threads enter the global lock queue.

[0066] The structure diagram of the NUMA Reader Slot is as follows: Figure 6 As shown, in ROWEX lock mode, instead of using a single global ReaderCounter, an independent Reader Slot, i.e. a local read counter slot, is set for each NUMA node.

[0067] When a read thread accesses a hot page, its read count is incremented in the local Reader Slot based on the CPU or NUMA node where the read thread is located. When the read thread exits, the corresponding local Reader Slot is decremented by one.

[0068] When a write thread arrives, it does not need to wait for a single global counter. Instead, it checks sequentially whether all NUMA ReaderSlots are zero. When all read counters are zero, it means that no read thread is currently accessing the page, and the write thread can enter an exclusive state.

[0069] This architecture distributes the competition for the same global counter among multiple CPUs to local counter slots on different NUMA nodes, reducing cache line contention and cross-node consistency synchronization overhead.

[0070] In one possible implementation, the steps for a write thread to access the data include: Set a write barrier flag to announce the write intent; Prevent new read requests from entering the read fast path; Wait for all read threads already in the fast read path to complete and exit; After all read threads have exhausted their memory, the page state is transitioned to a write-exclusive state via a CAS atomic operation, granting write threads exclusive access to the page.

[0071] like Figure 7 As shown, when a write thread accesses a buffer page, it first checks if the page is under a ROWEX lock. If the page is not under a ROWEX lock, the write access is performed using a normal exclusive lock. If the page is under a ROWEX lock, the write thread sets a write barrier state using a CAS atomic operation. Once the write barrier state is successfully set, new read threads are immediately prevented from entering the Reader Fast Path. Read threads already in a Reader Slot are not forcibly interrupted but are allowed to complete their read access and exit naturally. The write thread then periodically checks if all NUMA Reader Slots are zero. If any Reader Slot is not zero, it means there are still read threads accessing the page, and the write thread continues to wait. If all Reader Slots are zero, the page state transitions to EXCLUSIVE, the write thread gains exclusive access, and performs page modification operations. After the write operation is complete, the write barrier state is cleared, and the read thread's fast path is restored. This avoids new read threads entering indefinitely, causing the write thread to wait in a long time, while also avoiding directly interrupting existing read threads, thus improving the determinism of obtaining exclusive access for write operations.

[0072] In one possible implementation, the page description structure contains control information related to the second lock mode; the control information is embedded in the page description structure, or is associated by the page description structure with an independent extended control structure via a pointer. The control information includes at least: CAS atomic status word, used to jointly represent the lock type, page-level running state and Epoch version number of the current page; The lock mode transition flag is used to indicate whether a thread is currently performing a lock mode transition; The access statistics field is used to store the access statistics data; The NUMA local read counter array includes multiple read counter slots corresponding to different NUMA nodes; The Writer Drain control fields include at least the write barrier flag, the write thread wait flag, and the Reader drain status.

[0073] In the above possible implementations, the Epoch version number is used to identify the migration round of the page-level state; the thread compares and updates the CAS atomic state word through CAS operation to avoid state overwriting or inconsistency caused by multiple threads migrating concurrently.

[0074] In practical applications, control information can be set in the BufferDesc structure of the database Buffer page description, for example, for ROWEX lock mode control information. See Table 1 for details.

[0075] Table 1 Buffer Page Description Structure

[0076] Control information can be directly embedded in BufferDesc, or it can be linked to a separate extended control structure via pointers from BufferDesc. Specifically, ROWEX lock mode control information includes at least the CAS atomic status word, lock mode transition flag, hotspot access statistics field, NUMA local read count slot array, and Writer Drain control field.

[0077] The CAS atomic state word is used to jointly represent the lock type, page-level running state, and Epoch version number of the current page. Lock types include normal shared / exclusive lock types and ROWEX lock types; page-level running states include NORMAL, HOT, ROWEX, WRITER_PENDING, DRAINING, EXCLUSIVE, and COOLING; the Epoch version number is used to identify the transition round of the page-level state. Threads compare and update the atomic state word through CAS operations to avoid state overwriting or inconsistency caused by concurrent migrations from multiple threads.

[0078] The lock mode transition flag is used to indicate whether a thread is currently performing a lock mode transition. Only the thread that successfully sets the transition flag performs the actual transition; other threads wait or reread the state based on their current state.

[0079] The access statistics field can store access statistics, including the number of read accesses, the number of write accesses, the cumulative lock wait time, the cumulative write wait time, the number of spin retries, the most recent sampling time, and the page-level hotness value, which are used to identify whether the target buffer page meets the hot page determination criteria.

[0080] The NUMA local read counter array includes multiple read counter slots corresponding to different NUMA nodes. When a read thread enters the ROWEX fast read path, the corresponding read counter slot is added based on its NUMA node; the corresponding read counter slot is decremented after the read access is completed.

[0081] The Writer Drain control fields include at least a write barrier flag, a write thread wait flag, and a Reader empty status. When a write thread arrives, it sets a write barrier to prevent new read threads from entering the ROWEX fast read path and waits for all NUMA local read counters to return to zero. After all read counters return to zero, the page state is transitioned to the EXCLUSIVE state via CAS, and the write thread gains exclusive access to the page.

[0082] The CAS / Epoch state transition flowchart is as follows: Figure 8 As shown, page-level state transitions are accomplished through a combination of CAS atomic operations and the Epoch version number.

[0083] The Epoch version number refers to the state version number, used to identify the cycle in which the current page state has changed. Before performing a state transition, a thread first reads the current page state and the Epoch version. Then, it checks whether the target transition conditions are met. For example, the NORMAL state can transition to HOT after its popularity exceeds a threshold; the ROWEX state can transition to WRITER_PENDING after a write thread arrives; and the DRAINING state can transition to EXCLUSIVE after all Reader Slots have been cleared.

[0084] If the migration conditions are met, the thread performs a CAS operation to change the current state from the old state to the target state. If the CAS operation succeeds, it means that the current thread has successfully completed the state migration and updates the Epoch version number. If the CAS operation fails, it means that the state has been modified by another thread, and the current thread needs to reread the state and Epoch and re-determine whether to continue the migration based on the new state.

[0085] By combining CAS with Epoch, we can avoid the problem of state overwriting or inconsistency caused by multiple threads modifying the page state simultaneously.

[0086] In this embodiment, a page-level heat flag is used as the trigger condition. When page heat increases, the lock mode is safely migrated from the first lock mode to the second lock mode via a CAS atomic operation. In the second lock mode, read threads can execute concurrently without entering the global lock wait queue, thereby eliminating the lock contention bottleneck and NUMA consistency overhead of hot pages and significantly improving multi-core scalability. Write threads perform exclusive write operations by setting write barriers and waiting for existing read threads to naturally empty, solving the write starvation problem caused by write wait propagation leading to increased read latency. When page heat decreases, the lock mode is restored to the lightweight first lock mode based on cooling conditions, preventing unnecessary management overhead caused by maintaining a complex lock structure for a long time. In addition, the entire migration process relies on CAS atomic operations to ensure state consistency and atomicity under high concurrency, avoiding race conditions and state overwriting caused by multi-threaded concurrent migration. In this way, adaptive, safe, and efficient dynamic switching of database buffer pool lock modes is achieved, significantly improving concurrent access performance in hot page scenarios and maintaining low overhead in ordinary page scenarios, comprehensively improving the overall throughput and resource utilization of the database system.

[0087] Figure 9 This diagram illustrates a flowchart of a database buffer page-level lock mode migration control method in an application scenario, as shown below. Figure 9 As shown, the process begins with a database thread requesting access to a Buffer page. First, the target page and its corresponding page description structure are located. Then, access statistics for that page are collected, and a page-level hotness indicator is calculated. If the target page is not a hot page, the system uses a normal shared / exclusive lock to complete the access. If the target page is determined to be a hot page, it further determines whether a lock mode transition is needed. If a transition is required, it is performed using a CAS atomic operation to transition to a ROWEX lock mode.

[0088] Upon entering ROWEX lock mode, different paths are executed depending on the type of access. If it is a read access, the Reader Fast Path is executed, which means entering the corresponding NUMA local Reader Slot and reading the page. If it is a write access, the Writer Drain process is executed, which means setting a write barrier, preventing new reads from entering, waiting for existing read threads to be cleared, and then entering a write exclusive state.

[0089] After a page is accessed, update the feedback statistics. If the page access popularity decreases and the cooling-off conditions are met, revert to the normal lock mode; otherwise, maintain the current lock mode.

[0090] The following example illustrates the flow of the access process (the horizontal lines indicate the direction of time).

[0091] Read thread R1: Enter Reader Slot ─── Read page ─── Exit Reader Slot; Read thread R2: Enter Reader Slot ─── Read page ─── Exit Reader Slot; Write thread W1: Set write barrier ─ Wait for Reader to empty ─ Write exclusive ─ Release write barrier; Read thread R3: Detect write barrier ─── Wait ─ Enter after write is complete.

[0092] In ROWEX lock mode, read threads R1 and R2 have entered the Reader Slot and are reading pages. Subsequently, write thread W1 arrives and sets a write barrier. After the write barrier is set, the new read thread R3 cannot enter the Reader Fast Path and instead waits for the write barrier to be released.

[0093] R1 and R2, already in the Reader Slot, are not forcibly interrupted; instead, they exit naturally after completing the read operation. Once all Reader Slots are zeroed out, write thread W1 acquires exclusive write privileges and performs the write operation. After the write operation is complete, the system releases the write barrier, allowing read thread R3 to enter the read path.

[0094] This timing sequence illustrates how to ensure that write operations are eventually executed while avoiding the forced blocking of existing read threads and preventing new read threads from entering indefinitely.

[0095] Figure 10 A schematic diagram of a database buffer page-level lock morphological migration control system is shown; as follows: Figure 10 As shown, the system includes a database execution thread, a buffer page access entry point, a page access sampling module, a hot page identification module, a lock mode migration module, a NUMA local read counting module, a write barrier emptying control module, a CAS state migration module, and a performance feedback tuning module.

[0096] Before accessing a buffer page, the database execution thread first locates the target page and its page description structure through the buffer page access entry. The page access sampling module records the number of read accesses, write accesses, lock wait time, spin count, and write wait time for that page. The hot page identification module calculates a page-level popularity indicator based on the above sampling data. When this popularity indicator exceeds a preset threshold, the lock mode migration module switches the page from a normal shared / exclusive lock mode to a ROWEX lock mode.

[0097] In ROWEX lock mode, read threads no longer enter the global lock waiting queue, but instead enter the local read count structure of the corresponding NUMA node. When a write thread arrives, the write barrier emptying control module sets the write barrier state, preventing new read threads from entering, and waits for all NUMA local read counts to return to zero. The state switching process is completed by the CAS state transition module to ensure the atomicity and consistency of state switching in a concurrent environment. The performance feedback tuning module dynamically adjusts the hotspot threshold and rollback strategy based on the running results.

[0098] The following two examples further illustrate the specific steps of the database buffer page-level lock morphological migration control method.

[0099] Example 1 is an optimization of access to hot index pages in a high-concurrency order system. In a certain online transaction database, the order status index page is frequently accessed by multiple transactions due to a large number of order status updates.

[0100] Specifically, the buffer page is Order_Status_Index_Page_100, and the access patterns are as follows: read requests: 80,000 times / s, write requests: 15,000 times / s, and lock wait: continuously increasing. Sampling revealed that HeatScore > threshold; therefore, the buffer page lock pattern was migrated.

[0101] After migration: Read threads directly enter the NUMA Reader Slot and no longer compete for the global shared lock.

[0102] Write thread: Set a Writer Drain Barrier to prevent new reads from entering, while waiting for an existing Reader to exit.

[0103] Finally, Reader Slot=0, enter EXCLUSIVE, and perform page modification. After completion, the ROWEX state is restored. This method reduces contention for hot index page locks and improves high-concurrency transaction processing capabilities.

[0104] Another example is the optimization of hotspot buffer pages in a NUMA server environment, where the server is configured as follows: CPU: 4 NUMA nodes, database connections: 2000 concurrent connections, and a certain buffer page is being accessed by a large number of queries.

[0105] In the traditional approach, all Readers update the same Reader Counter, leading to Cache LineBouncing and increased CPU coherence overhead.

[0106] This scheme uses NUMA Node0, Reader Slot0, NUMA Node1, Reader Slot1, NUMA Node2, Reader Slot2, NUMA Node3, and Reader Slot3; different CPU nodes access the local Reader Slot.

[0107] Write thread execution: Check all Reader Slots, Slot0=0, Slot1=0, Slot2=0, Slot3=0. After confirming there is no Reader, exclusive modification begins. This method reduces synchronization overhead across NUMA nodes and improves multi-core scalability.

[0108] The above are embodiments of the method proposed in this application. Based on the same inventive concept, embodiments of this application also provide a device, the structure of which is as follows: Figure 11 As shown.

[0109] Figure 11 This is a schematic diagram of the internal structure of a device provided in an embodiment of this application. Figure 11 As shown, the device includes: At least one processor 1101; And a memory 1102 that is communicatively connected to at least one processor; The memory 1102 stores instructions that can be executed by at least one processor. The instructions are executed by at least one processor 1101 to enable at least one processor 1101 to: execute the above-described database buffer page-level lock mode migration control method.

[0110] Some embodiments of this application provide corresponding to Figure 1 A non-volatile computer storage medium stores computer-executable instructions, which are configured to execute the aforementioned database buffer page-level lock mode migration control method.

[0111] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments for IoT devices and media are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0112] The systems, media, and methods provided in this application are one-to-one correspondences. Therefore, the systems and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the systems and media will not be repeated here.

[0113] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0114] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0115] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0116] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0117] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0118] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0119] Computer-readable media include both permanent and non-permanent, removable and non-removable media that can store information by any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0120] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0121] The above description is merely an embodiment of this application and is not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of the claims of this application.

Claims

1. A database buffer page-level lock morphological migration control method, characterized in that, The method includes: In response to a thread's access request to a target buffer page, obtain the page description structure corresponding to the target buffer page; Based on the page description structure, the access statistics of the target Buffer page are determined, and based on the access statistics, the page-level popularity identifier corresponding to the target Buffer page is calculated; If the page-level heat index exceeds a preset hotspot threshold, the lock state of the target Buffer page is transferred from the first lock state to the second lock state through a CAS atomic operation. In the second lock mode, if the access request is a read access, multiple read threads enter concurrently, and the threads do not enter the global lock waiting queue; if the access request is a write access, the thread sets a write barrier and waits for the existing read threads to clear before performing an exclusive write operation. Update the access statistics once this access is complete; If the updated access statistics meet the cooling conditions, the lock state of the target Buffer page is restored from the second lock state to the first lock state through a CAS atomic operation.

2. The method according to claim 1, characterized in that, The first lock type is a normal lock type, and the second lock type is a read-optimized write exclusive lock type.

3. The method according to claim 1, characterized in that, The page-level popularity indicator is generated based on at least one of the following metrics from the access statistics: read access count, write access count, cumulative lock wait time, cumulative write wait time, and number of spin retries.

4. The method according to claim 1, characterized in that, The method further includes: setting a page-level state machine for the target Buffer page, wherein the page-level state machine includes at least the following states: The NORMAL state indicates that the target Buffer page is using the first lock mode. The HOT state indicates that the page heat index of the target Buffer page exceeds the heat threshold but has not yet completed the lock mode migration; The ROWEX state indicates that the target buffer page is in the second locking mode; The WRITER_PENDING state indicates that a write thread has arrived and set a write barrier. The DRAINING state indicates that it is waiting for an existing read thread to exit; The EXCLUSIVE state indicates that a write thread has acquired exclusive access. The COOLING state indicates that the target buffer page is ready to fall back to the first lock state.

5. The method according to claim 1, characterized in that, The page description structure contains control information related to the second lock mode; the control information is embedded in the page description structure, or is associated with an independent extended control structure by the page description structure through a pointer; the control information includes at least: CAS atomic status word, used to jointly represent the lock type, page-level running status and Epoch version number of the current buffer page; The lock mode transition flag is used to indicate whether a thread is currently performing a lock mode transition; The access statistics field is used to store the access statistics data; The NUMA local read counter array includes multiple read counter slots corresponding to different NUMA nodes; The Writer Drain control fields include at least the write barrier flag, the write thread wait flag, and the Reader drain status.

6. The method according to claim 5, characterized in that, The Epoch version number is used to identify the migration round of the page-level state; the thread compares and updates the CAS atomic state word through CAS operation to avoid state overwriting or inconsistency caused by multiple threads migrating concurrently.

7. The method according to claim 1, characterized in that, In the second lock mode, the read access process includes: The thread reads the write barrier status of the target buffer page; If no write barrier exists, the thread enters the local Reader Slot of the NUMA node corresponding to the thread to perform a read operation, and exits the local Reader Slot after the read is completed. If a write barrier exists, the thread enters a waiting path or falls back to a normal lock waiting strategy.

8. The method according to claim 7, characterized in that, The local Reader Slot is an independent read counter slot maintained for each NUMA node; the thread only modifies the read counter slot of the NUMA node corresponding to the thread. During the emptying phase, the write thread summarizes and checks whether the read count slots of all NUMA nodes have returned to zero.

9. A database buffer page-level lock mode migration control device, characterized in that, The device includes: At least one processor; And, a memory communicatively connected to the at least one processor; The memory stores instructions that can be executed by the at least one processor, which are executed by the at least one processor to enable the at least one processor to perform a database buffer page-level lock mode migration control method as described in any one of claims 1-8.

10. A computer storage medium storing computer-executable instructions, characterized in that, When the computer-executable instructions are executed, a database buffer page-level lock mode migration control method as described in any one of claims 1-8 is implemented.

Images