White list state synchronous processing method and system combined with database transaction control

Abstract

The invention discloses a white list state synchronous processing method and system combined with database transaction control, and relates to the technical field of database transaction control and data synchronous processing. The method comprises the following steps: recording a white list state change event based on a pre-writing mechanism of a database transaction log; change events in a transaction log are classified and aggregated according to a white list and then are asynchronously written into a specified buffer area, when a transaction is submitted, the sequence of data in the buffer area is controlled through a lightweight lock mechanism to be refreshed to a physical disk, and the priority of a disk IO queue is dynamically adjusted according to the transaction isolation level to reduce concurrent conflicts; according to the white list state synchronization processing method and system combined with database transaction control, the transaction concurrent processing performance and the database response efficiency are remarkably improved, and the expansion capability and stability of the white list synchronization system in a complex business environment are also improved.

Description

Technical Field

[0001] This invention relates to the field of database transaction control and data synchronization processing technology, specifically to a whitelist status synchronization processing method and system that combines database transaction control. Background Technology

[0002] In existing technologies, the synchronization of whitelist status typically relies on the database's transaction control mechanism to ensure data consistency and reliability. When a whitelist change request is received, the system initiates a database transaction, performs a write operation, and commits the transaction upon completion, thereby ensuring the atomicity of the operation.

[0003] However, in high-concurrency scenarios, such transaction operations frequently trigger the database to write transaction logs to disk to meet persistence requirements. Especially in databases using a write-before-log mechanism, each transaction commit is accompanied by a forced disk flush (such as an fsync operation), resulting in a large number of physical disk I / O requests. As the number of concurrent transactions increases, these disk write operations will queue at the I / O level, creating severe I / O contention, thus affecting the database's response speed and limiting the overall performance of the whitelist synchronization system. Furthermore, existing database systems generally adopt a common sequential synchronous write strategy for transaction log writing, lacking optimization methods for high-concurrency, short-transaction scenarios. For example, in businesses where whitelist status changes frequently, many transaction operations have characteristics such as short lifecycles, small data volumes, and high real-time requirements, but existing mechanisms cannot effectively identify and prioritize such operations, nor do they provide customized transaction log processing paths. This approach not only fails to improve transaction processing efficiency but also causes non-critical logs to compete for disk resources with critical update operations, exacerbating the I / O bottleneck and thus restricting the concurrency performance and scalability of the entire whitelist status synchronization system. Summary of the Invention

[0004] The purpose of this invention is to provide a whitelist status synchronization processing method and system that combines database transaction control, thereby improving concurrency performance by optimizing the database transaction log writing path to reduce physical disk I / O contention.

[0005] To achieve the above objectives, the present invention provides the following technical solution: a whitelist status synchronization processing method combined with database transaction control, the method comprising: S1. Record whitelist status change events based on the write-ahead mechanism of the database transaction log; S2. After asynchronously writing the change events in the transaction log to the specified buffer, the events are aggregated according to the whitelist. S3. When a transaction is committed, a lightweight locking mechanism is used to control the sequential flushing of buffer data to the physical disk; S4. Dynamically adjust disk I / O queue priorities based on transaction isolation levels to reduce concurrency conflicts; Furthermore, during transaction execution, pre-commit intent information is generated based on change events, triggering a cross-transaction write-ahead collaboration protocol to establish state write intent negotiation in advance in the buffer layer; The method further includes: constructing a whitelist status-aware write-ahead scheduling engine, which dynamically optimizes the log writing order and target location during the write-ahead phase based on the historical transaction commit mode and the whitelist status activity, minimizing the aggregation conflicts of subsequent disk flushing.

[0006] Preferably, the write-ahead mechanism is based on the WAL log model and adds a unique event identifier and transaction identifier to each whitelist change event, supporting event-level tracing and rollback operations.

[0007] Preferably, the buffer has a multi-layer structure, including a transaction write buffer and an IO flush buffer. The classification and aggregation operation is first written to the write buffer of the corresponding whitelist partition, and then migrated to the flush buffer when the flush conditions are met.

[0008] Preferably, the lightweight locking mechanism is based on a starvation-free fair lock model and uses a timestamp sorting strategy to control the execution order of multiple concurrent transactions when writing to the buffer.

[0009] Preferably, the dynamic adjustment of the disk I / O queue priority is based on the following indicators: current number of concurrent transactions, transaction isolation level, buffer write latency, and historical flush success rate, and the priority index is obtained through dynamic weight evaluation.

[0010] Preferably, the pre-commit intent information includes the target scope of the whitelist change, the expected write time window, the transaction identifier, and the consistency level, and an intent graph is constructed in the buffer layer to coordinate write resources.

[0011] Preferably, the write-ahead coordination protocol includes a horizontal negotiation sub-protocol and a vertical write plan sub-protocol, wherein the horizontal negotiation is used to identify the concurrent intent of multiple transactions to the same whitelist target, and the vertical write plan is used to rearrange the write priority and merge duplicate events.

[0012] Preferably, the whitelist status-aware pre-write scheduling engine periodically counts the activity change trends of different whitelist partitions, uses an exponentially weighted moving average algorithm to predict areas of high change density in the short term, and prioritizes scheduling the write order of high-density areas.

[0013] Preferably, the write-ahead scheduling engine further integrates an event popularity scoring mechanism, which calculates a popularity value for each whitelisted event based on access frequency, change frequency, and the time of the most recent modification, and sorts the IO writes in conjunction with a popularity priority queue.

[0014] A whitelist status synchronization processing system combined with database transaction control is used to implement the steps of the whitelist status synchronization processing method combined with database transaction control. The system includes: The logging module is used to record whitelist status change events based on the write-ahead mechanism of the database transaction log; The event aggregation module is used to aggregate change events in the transaction log according to a whitelist and asynchronously write them to a specified buffer. The sequential flush control module is used to control the data in the buffer to be flushed to the physical disk in a predetermined order through a lightweight locking mechanism when a transaction is committed. The IO priority scheduling module is used to dynamically adjust the priority of the disk IO queue based on the transaction isolation level in order to reduce conflicts during concurrent operations.

[0015] As can be seen from the above technical solution, the present invention has the following beneficial effects: This invention proposes an innovative solution to the problems of severe disk I / O contention, high transaction commit latency, and lack of optimized log write paths in high-concurrency environments, based on a whitelist-based state synchronization processing method and system that integrates database transaction control. By introducing an event recording method based on a transaction log write-ahead mechanism and asynchronously writing change events to a buffer after categorizing and aggregating them according to a whitelist, the instantaneous disk write pressure is significantly reduced. Furthermore, a lightweight locking mechanism controls the sequential flushing of the buffer during the transaction commit phase, achieving controllable flushing order and reducing write conflicts. Simultaneously, this invention proposes for the first time a cross-transaction write-ahead collaboration protocol. By generating pre-commit intent information during transaction execution, a state write negotiation mechanism is established in advance, dispersing and avoiding disk write conflicts at the source. In addition, a whitelist-based state-aware write-ahead scheduling engine is constructed to dynamically optimize the log write order and target location based on historical transaction patterns and state activity, resulting in more concentrated writing of high-frequency areas and delayed processing of low-conflict areas, thereby minimizing disk I / O aggregation conflicts. In summary, this invention not only significantly improves transaction concurrency processing performance and database response efficiency, but also enhances the scalability and stability of the whitelist synchronization system in complex business environments. Attached Figure Description

[0016] Figure 1 This is a flowchart of the method of the present invention; Figure 2 This is a system connection diagram of the present invention. Detailed Implementation

[0017] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0018] like Figure 1 As shown, the present invention provides a technical solution: a whitelist status synchronization processing method combined with database transaction control, the method comprising: S1. Record whitelist status change events based on the write-ahead mechanism of the database transaction log; S2. After asynchronously writing the change events in the transaction log to the specified buffer, the events are aggregated according to the whitelist. S3. When a transaction is committed, a lightweight locking mechanism is used to control the sequential flushing of buffer data to the physical disk; S4. Dynamically adjust disk I / O queue priorities based on transaction isolation levels to reduce concurrency conflicts; Furthermore, during transaction execution, pre-commit intent information is generated based on change events, triggering a cross-transaction write-ahead collaboration protocol to establish state write intent negotiation in advance in the buffer layer; The method further includes: constructing a whitelist status-aware write-ahead scheduling engine, which dynamically optimizes the log writing order and target location during the write-ahead phase based on the historical transaction commit mode and the whitelist status activity, minimizing the aggregation conflicts of subsequent disk flushing.

[0019] This implementation first invokes the write-ahead log mechanism provided by the database system kernel during the execution of database transactions. This mechanism captures change operations related to the whitelist status in real time during the transaction execution phase, such as additions, updates, and deletions. Each type of change event is written as a structured event entry to the currently active transaction log cache. When generating change event logs, the system assigns each log record a precise transaction identifier, operation type, operation object identifier, operation timestamp, and whitelist category identifier. The transaction identifier uses a globally incrementing transaction number generated internally by the database. The operation type is represented by an explicit enumeration value, such as 1 for addition, 2 for modification, and 3 for deletion. The operation object identifier is the unique primary key number of the target whitelist entry. The operation timestamp is the current system time value in milliseconds. The whitelist category identifier is statically mapped according to the whitelist categories defined in the system configuration file, typically a positive integer, such as 1 for IP whitelist and 2 for URL whitelist.

[0020] Subsequently, the system periodically scans the transaction log cache through a separate background thread, aggregating and categorizing change events based on a whitelist classification identifier. The number of change events processed by this thread each time is configurable, for example, 100 per batch. The processing logic uses a FIFO (First-In, First-Out) approach to sequentially read the log cache, and uses a hash table to classify and store events belonging to the same whitelist category. After classification, each event sequence is encapsulated into a buffer data block. The buffer is managed using a memory page structure, with each page being 4096 bytes in size, and multiple data blocks are written into the page until it is full.

[0021] During the transaction commit phase, the system invokes a lightweight locking mechanism to protect the buffer flush operation, ensuring that the buffer data is flushed to the physical disk in a strict commit order. The lightweight locking mechanism allocates an independent commit sequence number queue for each transaction and controls the write order of the buffer in combination with the commit log linked list structure in memory. The specific steps include: (1) marking the current transaction as committed, (2) calling the buffer write interface to flush the classified data blocks to the log disk segment in order, and (3) releasing the write lock flag corresponding to the transaction after the flush is completed.

[0022] Furthermore, to reduce concurrency conflicts arising from disk write operations between different transactions, the system dynamically adjusts the priority of the disk I / O queue based on the transaction isolation level. There are four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. During initialization, the system assigns a fixed priority value to each level; for example, Serializable is 1, Repeatable Read is 2, Read Committed is 3, and Read Uncommitted is 4. When write operation requests are queued, the scheduler reorders the write tasks in the I / O queue according to the transaction isolation level, placing write tasks with lower priority values ​​first. This ensures that higher isolation level transactions are written to disk first, thereby reducing concurrent resource contention caused by lower priority transactions blocking higher-priority transactions.

[0023] During transaction execution, the system automatically generates pre-commit intent information based on change events. This information structure includes: the whitelist category corresponding to the current change event, the change type, the expected write buffer page number, and the write-ahead offset. The system initiates a cross-transaction write-ahead coordination protocol, broadcasting the current transaction's intent to other executing transactions of the same category, coordinating these transactions to reserve write space within the same buffer page. This coordination process is implemented by maintaining a buffer page reservation mapping table in shared memory, categorized by whitelist. If multiple transactions intend to write to the same page, the system will pre-allocate space and update the write offset table. This mapping table supports up to 64 transaction write intents simultaneously per page and is maintained using a circular array structure.

[0024] To further improve data integration efficiency during the write phase, the system constructs a whitelist status-aware write-ahead scheduling engine. During initialization, the scheduling engine loads the most recent 1000 transaction commit records and analyzes the activity metrics for each whitelist status. Activity is measured by the number of change events for each whitelist category per unit time; for example, whitelist categories with more than 500 change records per second are considered high-activity types. The system uses a sliding window mechanism to calculate the average number of change events within a 5-second window. The activity calculation results are used to dynamically adjust the log write strategy: for high-activity categories, the scheduling engine prioritizes writing their events to pre-allocated contiguous disk segments to improve the aggregation efficiency of subsequent flushes; for low-activity categories, the scheduling engine adopts a distributed write strategy to avoid concentrated write hotspots. The entire implementation process involves multiple parameter configurations: (1) The number of events processed in each event aggregation, with a default value of 100, which can be configured to any value between 50 and 1000 depending on the system memory status; (2) The buffer page size is fixed at 4096 bytes, aligned with the operating system page structure to ensure write efficiency; (3) The isolation level priority value is a fixed mapping and does not change dynamically with the system status; (4) The maximum length of the IO queue is determined according to the database engine configuration, with a typical value of 1024; (5) The high activity threshold is 500 change events per unit time. This value is determined by analyzing the fluctuation range of historical logs, and the recommended value range is between 300 and 800 per second; (6) The analysis window width in the scheduling engine is 5 seconds, and the sliding interval is 1 second to ensure a balance between the real-time performance and stability of data statistics.

[0025] The write-ahead mechanism is based on the WAL log model and adds a unique event identifier and transaction identifier to each whitelist change event, supporting event-level tracing and rollback operations.

[0026] This implementation further refines the write-ahead mechanism, explicitly adopting the WAL log model as the log management framework. When the database system performs a whitelist status change operation, the system first creates a log entry in the memory log buffer. This entry includes the operation type, operation target, timestamp, and whitelist classification information. Based on this, to achieve more granular operation tracking and status recovery, the system adds two key identifiers to each change event: a unique event identifier and a transaction identifier. The event identifier is constructed by concatenating the current system timestamp with an incrementing event counter, ensuring that events generated in the system at any given time are unique. A typical format is "millisecond-level timestamp + event counter," such as "1627384951234-0007." The transaction identifier is a globally assigned transaction ID, usually an auto-incrementing positive integer, assigned by the transaction manager each time a transaction starts. The WAL log model requires that all change operations must be fully recorded in the log file before being actually written to the database page. Therefore, after each whitelist change event, the system first writes the complete log entry containing the unique identifier and transaction identifier to the WAL log cache and triggers a write-ahead operation. The system leverages the sequential write characteristic of the WAL log to ensure write efficiency, while using the identifier field to accurately locate each event. When event-level tracing is required, the system can quickly locate a specific entry in the log file using the event identifier, read its content before and after the change, and the corresponding transaction context information. If the upper-layer system requests a rollback operation on a transaction, the system traces back to find all change events involved in the transaction based on the transaction identifier, and then reverse-engineers the whitelist state using the reverse operation data in the WAL log, including deleting newly added entries, restoring modified content, or re-inserting deleted entries, ensuring that the system state is consistent with that before the transaction rollback.

[0027] The buffer has a multi-layer structure, including a transaction write buffer and an IO flush buffer. The classification and aggregation operation is first written to the write buffer of the corresponding whitelist partition, and then migrated to the flush buffer when the flush conditions are met.

[0028] This implementation introduces a two-layer buffer structure to further optimize the processing efficiency and flushing performance of whitelist status change data. The system buffer structure is divided into two layers: the first layer is the transaction write buffer, and the second layer is the IO flush buffer. The transaction write buffer is a high-speed cache area in memory. Each whitelist category has an independent partition, which is divided during system initialization according to the number of whitelist categories specified in the configuration file. For example, if four whitelist categories are configured, the write buffer is divided into four partitions. Each partition is a contiguous block of memory, managed using a circular array structure. Each array element is a standardized change event record unit with a fixed size of 256 bytes. Each partition supports the storage of up to 4096 event units, with a capacity of 1 megabyte.

[0029] When the classification and aggregation thread extracts change events from the write-ahead log, it selects the target write partition according to the whitelist classification identifier in the event and writes the event data to the tail of the corresponding transaction write buffer. A lock-free write strategy is adopted during writing. Each partition maintains independent write pointers and read pointers. The write pointers write data sequentially to prevent competition between threads. When a transaction is committed, all its related events are marked as "writable". The system periodically performs buffer detection operations, checking every 100 milliseconds whether there is data in each partition that meets the flush conditions. The criteria for judging the flush conditions include: (1) the number of "writable" data entries in the partition reaches 512; (2) the interval between the current partition's write pointer and read pointer exceeds 1024; (3) the time since the last flush operation exceeds 500 milliseconds. If any condition is met, migration is triggered.

[0030] During the migration process, the system copies data that meets the criteria from the write buffer to the I / O flush buffer. The flush buffer is a dedicated buffer pool for I / O write threads, managed using a paginated structure. Each page is 8192 bytes in size and supports batch writing of 32 event units per page. The migration process employs a "batch copy + state clearing" method: after batch copying data from the write buffer to the flush buffer, the state of the corresponding data items in the original area is reset to "free," and a reclamation signal is sent to the write pointer, triggering the buffer space reclamation mechanism. Data in the I / O flush buffer is then uniformly sorted according to its whitelist and written to the log area on the physical disk by asynchronous I / O threads. The entire migration process is controlled by a flush scheduler, which dynamically adjusts the flush frequency based on the current system I / O load to ensure a balance between disk writing and memory reclamation.

[0031] The lightweight locking mechanism is based on a starvation-free fair lock model and uses a timestamp sorting strategy to control the execution order of multiple concurrent transactions when writing to the buffer.

[0032] This implementation further optimizes the access control mechanism for concurrent transactions to the buffer based on the aforementioned multi-layer buffer structure. Specifically, it adopts a starvation-free fair lock model as a lightweight lock control method, combined with a timestamp sorting strategy, to achieve ordered access of multiple concurrent transactions when writing to the shared buffer. When each transaction is generated, the system assigns a start timestamp accurate to milliseconds as the global sorting basis for that transaction. This timestamp is generated by the transaction manager when it receives a transaction start request, calling the system time function, and is permanently bound to the transaction context.

[0033] Whenever a transaction needs to write the categorized and aggregated whitelist change events to the write buffer during execution, the transaction must first register its write intent with the fair lock manager. The fair lock manager maintains a min-heap-based data structure to store the timestamps and transaction IDs of all transactions awaiting write access, ordered by timestamp. The manager only allows the top-of-the-heap transaction (i.e., the transaction with the earliest timestamp) to gain write access and blocks write requests from other transactions. After the transaction completes its write operation and releases the lock, the fair lock manager dequeues the top-of-the-heap transaction and reactivates the write access for the next transaction, thus ensuring that all transactions write to the buffer fairly and orderly according to their timestamps.

[0034] To avoid deadlocks or long transaction wait times in high-concurrency scenarios, the system introduces a starvation-free mechanism. This mechanism periodically scans the wait time of all suspended transactions in the wait queue. If a transaction's wait time exceeds a set threshold (default value 1000 milliseconds), it is forcibly promoted to the highest priority and inserted at the top of the heap to break potential latency backlogs and ensure that every transaction can obtain write access within a limited time. During the write process, the transaction write thread only locks the buffer corresponding to its target whitelist partition, avoiding performance bottlenecks caused by global buffer locks.

[0035] The dynamic adjustment of disk I / O queue priority is based on the following metrics: current number of concurrent transactions, transaction isolation level, buffer write latency, and historical flush success rate. The priority index is obtained through dynamic evaluation with weights.

[0036] This implementation further refines the disk I / O scheduling strategy, employing a multi-metric joint weight model to dynamically evaluate the priority of each write task in the I / O queue, thereby determining its scheduling order in the disk I / O queue. The system collects its current operating environment and performance metrics in real time when each transaction enters the write phase, mainly including four core metrics: current concurrent transactions, transaction isolation level, buffer write latency, and historical flush success rate.

[0037] The current concurrent transaction count refers to the total number of transactions in the execution state in the system, which is updated in real time by the transaction scheduler. The transaction isolation level is the isolation strategy specified by the client or system at the start of the transaction. The system sets fixed values ​​for four isolation levels: Serializable (1), Repeatable Read (2), Read Committed (3), and Read Uncommitted (4). Smaller values ​​indicate higher requirements. Buffer write latency refers to the time interval between the transaction change event being written to the buffer and being migrated to the flush buffer. This value is calculated by the system using timestamp differences and is measured in milliseconds. Historical flush success rate refers to the ratio of the number of successful flushes within a specified time window to the total number of attempts for the whitelist category to which the transaction belongs. The default window size is set to the most recent 10 minutes, and the statistics are calculated by sliding once every 10 seconds.

[0038] The system uses a linear weighted model to calculate the priority index of each transaction's write task. The specific calculation method is as follows: The current number of concurrent transactions, transaction isolation level, write latency, and historical flush success rate are used as four input parameters, each multiplied by a preset weight coefficient and then summed. The default weight coefficients are set as follows: concurrent transactions = 0.2, isolation level = 0.4, write latency = 0.3, and flush success rate = 0.1. The smaller the priority index, the higher the scheduling priority. For example, if the current transaction has 50 concurrent transactions, an isolation level of 2, a write latency of 120 milliseconds, and a flush success rate of 0.9, then the priority index of this transaction is: 50 multiplied by 0.2, plus 2 multiplied by 0.4, plus 120 multiplied by 0.3, plus 0.9 multiplied by 0.1, resulting in 10 plus 0.8 plus 36 plus 0.09, ultimately totaling 46.89.

[0039] The system uses a minimum priority index sorting algorithm in the disk I / O scheduler, placing tasks with the lowest priority index at the front of the queue to ensure they are written to disk first. The I / O scheduler is implemented using a min-heap, and automatically rearranges the queue whenever a new task is enqueued or an existing task is dequeued after completion. The scheduler re-evaluates the priority index of all tasks in the queue every 50 milliseconds to ensure that priority changes caused by environmental changes during queuing are promptly reflected in the queue order.

[0040] The pre-commit intent information includes the target scope of the whitelist change, the expected write time window, the transaction identifier, and the consistency level, and an intent graph is built at the buffer layer to coordinate write resources.

[0041] This implementation further refines the "pre-commit intent information" structure and its collaborative mechanism at the buffer level in the cross-transaction write-ahead collaboration protocol. During the transaction execution phase, when a whitelist status change requirement is detected, the system immediately generates a corresponding pre-commit intent message. This message contains four key fields: the target scope of the whitelist change, the expected write time window, the transaction identifier, and the consistency level.

[0042] The target scope of the whitelist change is generated by the system based on the specific whitelist entries involved in the change event. Its granularity can be a single record, a specified IP range, a set of URLs, or a user group. For example, a change operation involving 10 consecutive IP addresses will be identified as "IP range change, starting address 192.168.1.1, ending address 192.168.1.10". The expected write time window field indicates the time interval within which the transaction plans to complete the write operation. It is dynamically predicted by the transaction controller based on the current execution progress and the system's flush cycle. The default time unit is milliseconds, and a common configuration is "start time point is the current system time plus 50 milliseconds, end time point is the current time plus 200 milliseconds". The transaction identifier is a globally unique identifier ID, uniformly generated and assigned by the transaction scheduler. The consistency level is the write consistency requirement determined by the system based on the current transaction context, divided into strong consistency, medium consistency, and eventual consistency, mapped to fixed values ​​1, 2, and 3 respectively. The smaller the value, the higher the consistency level.

[0043] The system writes all pre-commit intent information generated by transactions into an in-memory "intent table," which is a combination of a hash table and a linked list index. Each whitelist category has an independent intent sub-table. Each intent record in the intent table is arranged according to its target range and expected write time window, sorted by start time, and includes write status flags and conflict flag fields. When multiple transaction intents have overlapping target ranges, overlapping time windows, or conflicting consistency levels, the system immediately marks the potential conflict state in the intent table. Subsequently, the write-ahead scheduling engine reads the intent table information and coordinates the actual buffer write resource allocation stage based on the intent order and conflict situation. For example, it arranges serial write order for transactions with overlapping target ranges and the same consistency level; sets exclusive write locks for conflicting transactions; and allows parallel writes for transactions with interleaved time windows and low consistency requirements, thereby ensuring reasonable resource scheduling and that write behavior meets consistency constraints.

[0044] The write-ahead coordination protocol includes a horizontal negotiation sub-protocol and a vertical write plan sub-protocol. The horizontal negotiation is used to identify the concurrent intent of multiple transactions to the same whitelist target, and the vertical write plan is used to rearrange write priorities and merge duplicate events.

[0045] This implementation further expands the internal structure of the cross-transaction write-ahead coordination protocol by explicitly dividing it into two parts: a horizontal negotiation sub-protocol and a vertical write plan sub-protocol, in order to improve the system's ability to coordinate conflicts between concurrent writes of multiple transactions and to optimize the write plan.

[0046] The execution logic of the horizontal negotiation sub-protocol is automatically triggered after a transaction generates a pre-commit intent and registers it in the intent graph. The system first scans all currently active transaction intents in the intent table to identify whether multiple transactions have overlapping or identical change requests for the same whitelist target range. The judgment logic performs precise matching based on the address range, entry primary key, or tag identifier in the target range field. If the matching result confirms that two or more transactions involve the same whitelist object, the system determines that concurrent intents exist. At this point, the horizontal negotiation sub-protocol records these transaction intents in a negotiation queue and establishes a conflict coordination unit for that target range. The coordination unit calculates the priority order based on the consistency level, commit timestamp, and expected write time window of each transaction, marking the priority transaction as the primary write transaction, and marking the remaining transactions as subordinate or waiting. Only the primary write transaction is allowed to continue the write preparation process; subordinate transactions must wait for the primary write to complete or merge writes according to the system policy.

[0047] The vertical write plan sub-protocol starts immediately after the horizontal negotiation is completed, and is mainly responsible for the reordering and optimization of the write plan queue. The system extracts all advanceable main write transactions from the intent graph and imports their intent events into the write scheduling buffer pool. The vertical protocol performs the following three operations in sequence: (1) reorder the write tasks according to the transaction consistency level, flush latency and historical success rate, and prioritize the execution of tasks with strong consistency, high latency and low success rate; (2) check whether there are duplicate write events for the same whitelist object in the task list. If there are multiple change requests with the same content, they are automatically merged into one event record; (3) update the final write order and generate a plan table and submit it to the flush scheduler, which controls the specific execution order of buffer pages and disk I / O. The merged events adopt the "nearest coverage" strategy, that is, retain the latest valid change content and mark the remaining entries for deletion to save write resources.

[0048] This write-ahead coordination protocol forms a closed-loop control throughout the write preparation process, ensuring that each transaction has completed concurrency conflict checking, priority rearrangement, and event merging before entering the write channel, thus guaranteeing the orderliness, consistency, and resource efficiency of data writing.

[0049] The whitelist-state-aware write-ahead scheduling engine periodically analyzes the activity trends of different whitelist partitions, uses an exponentially weighted moving average algorithm to predict areas of high change density in the short term, and prioritizes the write order of high-density areas.

[0050] This implementation further enhances the intelligent scheduling capabilities of the whitelist-state-aware write-ahead scheduling engine. The system divides all whitelists in the database into multiple independent partitions according to predefined classification criteria, with each partition possessing an independent activity statistics unit. Activity refers to the number of change events recorded in the whitelist partition within a unit of time. The system obtains this data by periodically scanning the classification distribution of aggregated events in the buffer. The default statistical period is set to every second, and the statistical window is the past 30 seconds, maintaining records for 30 consecutive time points.

[0051] To more accurately capture short-term trends in whitelist status changes, the system employs an exponentially weighted moving average algorithm for activity smoothing. This algorithm applies a decaying weight to the historical activity sequence of each partition. The calculation formula is: the current predicted activity value equals the current period's activity value multiplied by a weight coefficient, plus the previous period's predicted value multiplied by the remaining weight coefficient. The default weight coefficient is set to 0.3, meaning the current data has a weight of 30%, and historical data has a weight of 70%. For example, if a whitelist partition detects 200 change events in the current statistical period, and the predicted value in the previous period was 180, then the updated predicted value is: 200 multiplied by 0.3 plus 180 multiplied by 0.7, resulting in 60 plus 126, ultimately totaling 186.

[0052] After sorting the predicted activity values ​​of all partitions, the system constructs a short-term change density distribution map based on the prediction results. The scheduling engine then prioritizes the N partitions with the highest predicted values ​​(N being the number of concurrent flushing threads the system can handle, e.g., 4) as the priority targets for this round of flushing. The buffer pages of the selected high-density regions are immediately marked as "priority flushing" and submitted to the I / O scheduler for high-priority disk write tasks. This mechanism ensures that data in the most active regions is persisted first in scenarios with frequent whitelist status changes, reducing the risk of buffer overflows and improving the real-time performance and stability of hot data processing.

[0053] The system repeatedly executes the activity statistics and prediction update process every second, and the scheduling results can dynamically respond to changes in business behavior, achieving near real-time adjustment of scheduling strategies. In addition, the scheduling engine also records the execution feedback of each round of scheduling, including the flushing time of each partition, IO success rate, etc., to further optimize the priority prediction model and resource allocation strategy for future cycles.

[0054] The write-ahead scheduling engine further integrates an event popularity scoring mechanism, which calculates the popularity value for each whitelisted event based on access frequency, change frequency, and the time of the most recent modification, and sorts the IO writes in conjunction with the popularity priority queue.

[0055] This implementation further enhances the event-level sorting mechanism of the write-ahead scheduling engine by introducing an event popularity scoring mechanism to perform fine-grained priority management of whitelist status change events, thereby improving the write response efficiency of hot events. Each whitelist change event, after being recorded in the transaction log by the system, immediately enters the scheduling engine's popularity analysis module. The system maintains three key indicators for each event: access frequency, change frequency, and last modification time.

[0056] Access frequency is obtained from the event hit log statistics provided by the whitelist management module. The system counts the number of times the corresponding whitelist entry for the event was accessed within the past 60-second time window, in units of times. Change frequency is the number of times the whitelist record has been modified in the past 30 seconds, calculated by the transaction log system after scanning relevant event identifiers, in units of times. The most recent modification time records the latest modification time of the whitelist entry, calculated as the difference between the last modification time and the current system time, in milliseconds. The system calculates the event popularity value using the following scoring model: Popularity value equals access frequency multiplied by a weight of 0.5, plus change frequency multiplied by a weight of 0.3, plus the reciprocal of the time since the last modification multiplied by a weight of 0.2. The reciprocal of the time is converted to an impact factor per kilomite in milliseconds. For example, if the time since the last modification is 5000 milliseconds, the reciprocal impact factor is 0.2.

[0057] For example, if an event is accessed 100 times in 60 seconds, changed 3 times in 30 seconds, and the last modification occurred 2000 milliseconds ago, its popularity value is: 100 multiplied by 0.5, plus 3 multiplied by 0.3, plus 0.5 multiplied by 0.2, which equals 50 plus 0.9 plus 0.1, resulting in a popularity value of 51. After calculating the popularity value for all events awaiting flushing in the buffer, the system sorts them from highest to lowest popularity and writes the results to a popularity priority queue. This queue is a max-heap structure, with the event with the highest popularity value at the top of the heap. The scheduling engine sequentially extracts events from the queue and forms flushing batches, prioritizing the submission of high-population events into the IO write process, thus ensuring that hot data is written to disk first.

[0058] The system refreshes the popularity score every 200 milliseconds and dynamically adjusts the priority queue structure based on the latest score. To prevent certain high-frequency events from occupying the refresh channel for an extended period due to excessively high popularity, the system introduces a time decay mechanism into the popularity value. Each refresh multiplies all popularity values ​​by a decay coefficient of 0.95, ensuring that older events gradually lose priority and preventing new hot topics from being suppressed.

[0059] like Figure 2 As shown, a whitelist status synchronization processing system combined with database transaction control is also provided to implement the steps of the whitelist status synchronization processing method combined with database transaction control. The system includes: The logging module is used to record whitelist status change events based on the write-ahead mechanism of the database transaction log; The event aggregation module is used to aggregate change events in the transaction log according to a whitelist and asynchronously write them to a specified buffer. The sequential flush control module is used to control the data in the buffer to be flushed to the physical disk in a predetermined order through a lightweight locking mechanism when a transaction is committed. The IO priority scheduling module is used to dynamically adjust the priority of the disk IO queue based on the transaction isolation level in order to reduce conflicts during concurrent operations.

[0060] This implementation provides a whitelist status synchronization processing system that combines database transaction control, enabling efficient and controllable processing of whitelist change operations. The overall system structure consists of four core functional modules: a log recording module, an event aggregation module, a sequential flushing control module, and an IO priority scheduling module.

[0061] The logging module is deployed within the database transaction execution engine. It is responsible for intercepting all operation requests involving whitelist status changes in real time and recording them as structured log entries based on the database write-ahead log (WAL) mechanism. Upon capturing a change operation, the module immediately extracts elements such as operation type, target object identifier, operation timestamp, and transaction identifier, encapsulates them into a standard event structure, writes it to the in-memory log buffer, and marks it as "pending aggregation." The logging module supports concurrent writes and uses a segmented memory structure for partitioned storage, ensuring real-time recording capabilities in high-throughput scenarios.

[0062] The event aggregation module is driven by an independent thread, periodically scanning the log recording module for events to be aggregated. This module categorizes and aggregates events based on whitelist classification fields, and the classification results are written to the transaction write cache of the corresponding whitelist partition. Each type of event is added to its respective memory cache queue in FIFO order, and automatically migrated to the IO flush buffer when the cache is full or the refresh condition is met. The aggregation process employs a multi-channel asynchronous write strategy, with the classification cache queues running independently, effectively improving the performance of concurrent processing of multiple whitelist categories.

[0063] The sequential flush control module is automatically activated during the transaction commit phase, with lightweight lock control as its core mechanism. The system assigns an independent write permission flag to each buffer partition. Before writing, transactions must register their write intent and enter the buffer write lock queue in order of timestamp or transaction number. This module controls transaction acquisition of write locks based on a fair and starvation-free model, ensuring that the commit order matches the flush order. After acquiring the lock, a transaction triggers the data transfer from the buffer to disk. After flushing is complete, the write lock is released, and the next transaction continues the operation.

[0064] The IO priority scheduling module runs on top of the disk write scheduler, continuously receiving write requests and dynamically adjusting scheduling priorities based on transaction isolation levels. The system records the isolation level during the transaction initiation phase, setting serialization priority to 1, repeatable reads to 2, read committed to 3, and read uncommitted to 4. The scheduling module assigns a corresponding priority value to each write request and enqueues them in the disk IO queue in ascending order of priority, ensuring that highly consistent transactions are written to disk first, reducing concurrency conflicts. The scheduler periodically evaluates the queue status, reorders requests every 50 milliseconds, and adjusts the scheduling granularity based on the current system load, ensuring a dynamic balance between write efficiency and consistency requirements.

[0065] This implementation method systematically constructs four core modules: log recording, classification and aggregation, sequential writing, and priority scheduling. It comprehensively covers the entire process of whitelist status change transaction processing, building a closed-loop control chain from operation capture to disk storage. Each module has clearly defined responsibilities and a high degree of decoupling, giving the system excellent scalability and maintainability. In high-concurrency database scenarios, this system can significantly improve the processing efficiency of whitelist changes, the level of consistency assurance, and transaction scheduling capabilities, making it suitable for business systems with extremely high requirements for data consistency and real-time performance.

[0066] While embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions, and variations may be made to these embodiments without departing from the principles and spirit of the invention, and that the scope of the invention is defined by the appended claims and their equivalents.

Claims

1. A whitelist status synchronization method combined with database transaction control, characterized in that, The method includes: S1. Record whitelist status change events based on the write-ahead mechanism of the database transaction log; S2. After asynchronously writing the change events in the transaction log to the specified buffer, the events are aggregated according to the whitelist. S3. When a transaction is committed, a lightweight locking mechanism is used to control the sequential flushing of buffer data to the physical disk; S4. Dynamically adjust disk I / O queue priorities based on transaction isolation levels to reduce concurrency conflicts; Furthermore, during transaction execution, pre-commit intent information is generated based on change events, triggering a cross-transaction write-ahead collaboration protocol to establish state write intent negotiation in advance in the buffer layer; The method further includes: constructing a whitelist status-aware write-ahead scheduling engine, which dynamically optimizes the log writing order and target location during the write-ahead phase based on the historical transaction commit mode and the whitelist status activity, minimizing the aggregation conflicts of subsequent disk flushing.

2. The whitelist status synchronization processing method combined with database transaction control according to claim 1, characterized in that: The write-ahead mechanism is based on the WAL log model and adds a unique event identifier and transaction identifier to each whitelist change event, supporting event-level tracing and rollback operations.

3. The whitelist status synchronization processing method combined with database transaction control according to claim 1, characterized in that: The buffer has a multi-layer structure, including a transaction write buffer and an IO flush buffer. The classification and aggregation operation is first written to the write buffer of the corresponding whitelist partition, and then migrated to the flush buffer when the flush conditions are met.

4. The whitelist status synchronization processing method combined with database transaction control according to claim 1, characterized in that: The lightweight locking mechanism is based on a starvation-free fair lock model and uses a timestamp sorting strategy to control the execution order of multiple concurrent transactions when writing to the buffer.

5. The whitelist status synchronization processing method combined with database transaction control according to claim 1, characterized in that: The dynamic adjustment of the disk I / O queue priority is based on the following indicators: current number of concurrent transactions, transaction isolation level, buffer write latency, and historical flush success rate, and the priority index is obtained through dynamic evaluation of weights.

6. The whitelist status synchronization processing method combined with database transaction control according to claim 1, characterized in that: The pre-commit intent information includes the target scope of the whitelist change, the expected write time window, the transaction identifier, and the consistency level, and an intent graph is built in the buffer layer to coordinate write resources.

7. The whitelist status synchronization processing method combined with database transaction control according to claim 1, characterized in that: The write-ahead coordination protocol includes a horizontal negotiation sub-protocol and a vertical write plan sub-protocol, wherein the horizontal negotiation is used to identify the concurrent intent of multiple transactions to the same whitelist target, and the vertical write plan is used to rearrange the write priority and merge duplicate events.

8. The whitelist status synchronization processing method combined with database transaction control according to claim 1, characterized in that: The whitelist status-aware write-ahead scheduling engine periodically analyzes the activity change trends of different whitelist partitions, uses an exponentially weighted moving average algorithm to predict areas of high change density in the short term, and prioritizes the writing order of high-density areas.

9. The whitelist status synchronization processing method combined with database transaction control according to claim 1, characterized in that: The write-ahead scheduling engine further integrates an event popularity scoring mechanism, which calculates the popularity value for each whitelisted event based on access frequency, change frequency, and the time of the most recent modification, and sorts the IO writes in conjunction with the popularity priority queue.

10. A whitelist status synchronization processing system combined with database transaction control, used to implement the steps of the whitelist status synchronization processing method combined with database transaction control as described in any one of claims 1-9, characterized in that, The system includes: The logging module is used to record whitelist status change events based on the write-ahead mechanism of the database transaction log; The event aggregation module is used to aggregate change events in the transaction log according to a whitelist and asynchronously write them to a specified buffer. The sequential flush control module is used to control the data in the buffer to be flushed to the physical disk in a predetermined order through a lightweight locking mechanism when a transaction is committed. The IO priority scheduling module is used to dynamically adjust the priority of the disk IO queue based on the transaction isolation level in order to reduce conflicts during concurrent operations.

Images