SQL timed task scheduling method and system

Abstract

The invention discloses an SQL (Structured Query Language) timed task scheduling method and system, and the method comprises the following steps: constructing a master database and a slave database, and receiving a timed task request; according to the timed task request, a Quartz. NET framework is adopted to construct a task scheduling strategy, and the timed task is allocated to the master database or the slave database to be executed based on the task scheduling strategy; in the process of constructing the task scheduling strategy, obtaining load conditions of the master database and the slave database, and dynamically adjusting the task scheduling strategy according to the load conditions; and monitoring the execution condition of the timed task. The method solves the problems that in an existing database timed task scheduling method, only a single database system is supported, wide compatibility to various database environments is lacked, a scheduling mechanism is triggered based on fixed time or time intervals, and dynamic adjustment is lacked according to database loads; therefore, not only is the flexibility of task execution limited, but also various emergencies possibly occurring in the task execution process are difficult to effectively deal with.

Description

Technical Field

[0001] The present invention relates to the technical field of timed task scheduling, and in particular to a SQL timed task scheduling method and system. Background Art

[0002] Among the existing database scheduled task scheduling methods, only a single database system, such as MySQL or SQL Server, is supported, and there is a lack of broad compatibility with multiple database environments. This makes it impossible to achieve unified management and scheduling of tasks in scenarios where multiple databases coexist. In addition, the existing scheduled task scheduling mechanism is often triggered based on fixed times or time intervals, and lacks the ability to dynamically adjust according to database load or business needs. This fixed scheduling mode not only limits the flexibility of task execution, but also makes it difficult to effectively respond to various emergencies and demand changes that may arise during task execution, thereby affecting the efficiency and effectiveness of task scheduling. For example, even if the database load is very high, the scheduled task will still be triggered according to the set time, which may cause the scheduled task to fail. Summary of the Invention

[0003] In response to the above-mentioned defects, the present invention proposes an SQL scheduled task scheduling method and system, which aims to solve the problems in existing database scheduled task scheduling methods, which mainly only support a single database system and lack broad compatibility with multiple database environments, and trigger the scheduling mechanism based on fixed time or time intervals, and lack the ability to dynamically adjust according to the database load. This not only limits the flexibility of task execution, but also makes it difficult to effectively deal with various emergencies that may arise during task execution.

[0004] To achieve this object, the present invention adopts the following technical solutions:

[0005] A method for scheduling SQL scheduled tasks, comprising the following steps:

[0006] Step S1: Build a master database and a slave database, and receive a scheduled task request, wherein the scheduled task request includes the type and execution frequency of the scheduled task;

[0007] Step S2: Based on the scheduled task request, the Quartz.NET framework is used to build a task scheduling strategy, and the scheduled task is assigned to the master database or the slave database for execution based on the task scheduling strategy;

[0008] In the process of building the task scheduling strategy, the load conditions of the master database and the slave database are obtained, and the task scheduling strategy is dynamically adjusted according to the load conditions of the master database and the slave database;

[0009] Step S3: Monitor the execution of the scheduled task.

[0010] Preferably, in step S1, the types of scheduled tasks include write tasks, read tasks and data synchronization tasks; in step S2, the scheduled tasks are assigned to the master database or the slave database for execution based on the task scheduling strategy, specifically including the following sub-steps: when the type of the scheduled task is a write task, the write task is assigned to the master database for execution; when the type of the scheduled task is a read task, the read task is assigned to the slave database for execution; when the type of the scheduled task is a data synchronization task, the data is synchronized from the master database to the slave database for execution.

[0011] Preferably, in step S2, the task scheduling strategy is dynamically adjusted according to the load conditions of the master database and the slave database, specifically including the following sub-steps: when the load of the master database and the slave database are both in normal state, the task scheduling strategy is specifically adjusted as follows: the write task is assigned to the master database for execution, and the read task is polled and assigned to all slave databases for execution; when the load of the master database is in overload state and the load of the slave database is in normal state, the task scheduling strategy is specifically adjusted as follows: all read tasks in the master database are assigned to the slave database for execution, and the execution of the write task is delayed; when the load of the master database and the slave database are both in overload state, the task scheduling strategy is specifically adjusted as follows: a new slave database is added on the basis of the original slave database, and all read tasks are evenly distributed to the corresponding slave databases for execution.

[0012] Preferably, in step S1, before receiving the scheduled task request, the following steps are further included: creating a new scheduled task, modifying the configuration of an existing scheduled task, and deleting a scheduled task that is no longer needed.

[0013] Preferably, in step S3, the following step is further included: recording the execution log of the scheduled task, wherein the execution log of the scheduled task includes the start time, end time and execution result of the scheduled task.

[0014] Another aspect of the present application provides an SQL scheduled task scheduling system, the system comprising:

[0015] The first building module is used to build a master database and a slave database;

[0016] A receiving module, configured to receive a scheduled task request, wherein the scheduled task request includes a type and an execution frequency of the scheduled task;

[0017] The second building block is used to build a task scheduling strategy using the Quartz.NET framework based on the scheduled task request;

[0018] The task allocation module is used to allocate scheduled tasks to the master database or slave database for execution based on the task scheduling strategy;

[0019] The acquisition module is used to obtain the load status of the master database and the slave database;

[0020] The strategy adjustment module is used to dynamically adjust the task scheduling strategy according to the load of the master database and the slave database;

[0021] The monitoring module is used to monitor the execution of scheduled tasks.

[0022] Preferably, in the receiving module, the types of scheduled tasks include write tasks, read tasks and data synchronization tasks; the task allocation module includes: a first task allocation submodule, which is used to allocate the write task to the master database for execution when the type of the scheduled task is a write task; a second task allocation submodule, which is used to allocate the read task to the slave database for execution when the type of the scheduled task is a read task; and a third task allocation submodule, which is used to synchronize data from the master database to the slave database for execution when the type of the scheduled task is a data synchronization task.

[0023] Preferably, the policy adjustment module includes: a first policy adjustment submodule, which is used to adjust the task scheduling policy specifically as follows when the loads of the master database and the slave database are both in a normal state: assigning write tasks to the master database for execution, and assigning read tasks to all slave databases for execution in a round-robin manner; a second policy adjustment submodule, which is used to adjust the task scheduling policy specifically as follows when the load of the master database is in an overloaded state and the load of the slave database is in a normal state: assigning all read tasks in the master database to the slave database for execution, and delaying the execution of write tasks; a third policy adjustment submodule, which is used to adjust the task scheduling policy specifically as follows when the loads of the master database and the slave database are both in an overloaded state: adding a new slave database on the basis of the original slave database, and evenly assigning all read tasks to the corresponding slave databases for execution.

[0024] Preferably, it also includes: a creation module for creating a new scheduled task; a modification module for modifying the configuration of an existing scheduled task; and a deletion module for deleting scheduled tasks that are no longer needed.

[0025] Preferably, the system further comprises: a recording module for recording an execution log of a scheduled task, wherein the execution log of the scheduled task includes the start time, end time and execution result of the scheduled task.

[0026] The technical solutions provided by the embodiments of the present application may have the following beneficial effects:

[0027] This solution uses the Quartz.NET framework to schedule scheduled tasks. Compared to existing database scheduled task scheduling methods, the Quartz.NET framework provides more flexible, efficient, and reliable task scheduling capabilities, suitable for complex environments with multiple databases. Furthermore, by obtaining real-time load information on the master and slave databases and dynamically adjusting task scheduling strategies accordingly, this solution not only improves task execution flexibility but also ensures that task scheduling can be optimized based on database load, thereby improving the overall efficiency and stability of task scheduling. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] Figure 1 It is a flowchart of the steps of the SQL scheduled task scheduling method. DETAILED DESCRIPTION

[0029] The embodiments of the present invention are described in detail below. Examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals represent the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention and are not to be construed as limiting the present invention.

[0030] A method for scheduling SQL scheduled tasks, comprising the following steps:

[0031] Step S1: Build a master database and a slave database, and receive a scheduled task request, wherein the scheduled task request includes the type and execution frequency of the scheduled task;

[0032] Step S2: Based on the scheduled task request, the Quartz.NET framework is used to build a task scheduling strategy, and the scheduled task is assigned to the master database or the slave database for execution based on the task scheduling strategy;

[0033] In the process of building the task scheduling strategy, the load conditions of the master database and the slave database are obtained, and the task scheduling strategy is dynamically adjusted according to the load conditions of the master database and the slave database;

[0034] Step S3: Monitor the execution of the scheduled task.

[0035] This solution uses a SQL scheduled task scheduling method, such as Figure 1As shown, the first step is to build a master database and a slave database and receive scheduled task requests, where the scheduled task request includes the scheduled task type and execution frequency. In this embodiment, building the master database and slave database facilitates the subsequent assignment of different types of scheduled tasks to corresponding databases for execution, thereby avoiding lock contention caused by multiple tasks simultaneously accessing the same database. By receiving the scheduled task request, a data foundation is provided for the construction of the task scheduling strategy. Further explanation: in this embodiment, the scheduled task types are divided into read tasks, write tasks, and data synchronization tasks, and the execution frequency is one task every 10 seconds. The second step is to use the Quartz.NET framework to build a task scheduling strategy based on the scheduled task request and assign the scheduled task to the master database or slave database for execution based on the task scheduling strategy. During the task scheduling strategy construction process, the load status of the master database and slave database is obtained, and the task scheduling strategy is dynamically adjusted based on the load status of the master database and slave database. In this embodiment, the Quartz.NET framework is a powerful open source task scheduling framework that can be used to schedule scheduled tasks. By assigning scheduled tasks to the master database or slave database for execution based on the task scheduling strategy, distributed scheduling of scheduled tasks is achieved. The load of the master and slave databases is fully considered during the task scheduling strategy construction process, and the task scheduling strategy is dynamically adjusted accordingly to ensure that task scheduling can be optimized according to the actual resource situation and achieve reasonable resource allocation and utilization. The third step is to monitor the execution of scheduled tasks. In this embodiment, by monitoring the execution of scheduled tasks, problems that arise during task execution can be discovered and handled in a timely manner, ensuring reliable task execution.

[0036] This solution uses the Quartz.NET framework to schedule scheduled tasks. Compared to existing database scheduled task scheduling methods, the Quartz.NET framework provides more flexible, efficient, and reliable task scheduling capabilities, suitable for complex environments with multiple databases. Furthermore, by obtaining real-time load information on the master and slave databases and dynamically adjusting task scheduling strategies accordingly, this solution not only improves task execution flexibility but also ensures that task scheduling can be optimized based on database load, thereby improving the overall efficiency and stability of task scheduling.

[0037] Preferably, in step S1, the types of scheduled tasks include write tasks, read tasks and data synchronization tasks; in step S2, the scheduled tasks are assigned to the master database or the slave database for execution based on the task scheduling strategy, specifically including the following sub-steps: when the type of the scheduled task is a write task, the write task is assigned to the master database for execution; when the type of the scheduled task is a read task, the read task is assigned to the slave database for execution; when the type of the scheduled task is a data synchronization task, the data is synchronized from the master database to the slave database for execution.

[0038] In this embodiment, since the master database is primarily responsible for write operations, write tasks are assigned to the master database to ensure data consistency and integrity. Since the slave database is primarily responsible for read operations, assigning read tasks to the slave database can distribute the read load, reduce the burden on the master database, and allow the master database to focus more on processing write operations. The data synchronization task synchronizes data from the master database to the slave database, ensuring data consistency between the master and slave databases and enhancing the system's data redundancy and fault tolerance.

[0039] Preferably, in step S2, the task scheduling strategy is dynamically adjusted according to the load conditions of the master database and the slave database, specifically including the following sub-steps: when the load of the master database and the slave database are both in normal state, the task scheduling strategy is specifically adjusted as follows: the write task is assigned to the master database for execution, and the read task is polled and assigned to all slave databases for execution; when the load of the master database is in overload state and the load of the slave database is in normal state, the task scheduling strategy is specifically adjusted as follows: all read tasks in the master database are assigned to the slave database for execution, and the execution of the write task is delayed; when the load of the master database and the slave database are both in overload state, the task scheduling strategy is specifically adjusted as follows: a new slave database is added on the basis of the original slave database, and all read tasks are evenly distributed to the corresponding slave databases for execution.

[0040] In this embodiment, when the loads of the master database and the slave database are both in a normal state, the write task is executed in the master database, and the read task is polled and distributed to all the slave databases for execution, which helps to avoid idle single-point resources. At the same time, the polling and distribution of read tasks to all the slave databases helps to evenly distribute read requests and prevent a certain slave database from being overused. When the load of the master database is in an overloaded state and the load of the slave database is in a normal state, since there are a small number of read tasks to be executed in the master database, it is necessary to allocate these small number of read tasks to the slave database for execution and delay the execution of the write task, thereby reducing the pressure on the master database and avoiding the collapse of the master database. When the loads of the master database and the slave database are both in an overloaded state, by adding a slave database and evenly distributing the read tasks, dynamic expansion of resources is achieved and the overall processing capacity of the system is improved.

[0041] Preferably, in step S1, before receiving a scheduled task request, the following steps are further included: creating a new scheduled task, modifying the configuration of an existing scheduled task, and deleting a scheduled task that is no longer needed. In this embodiment, when user demand leads to a task configuration change, the user can directly create, modify, or delete a scheduled task through the Web API interface, which can effectively shorten the demand response cycle.

[0042] Preferably, in step S3, the following steps are further included: recording an execution log of the scheduled task, wherein the execution log of the scheduled task includes the start time, end time, and execution result of the scheduled task. In this embodiment, by recording the execution log of the scheduled task, a valuable basis is provided for the maintenance and troubleshooting of the scheduling system. Once the scheduled task fails to execute, an alarm mechanism will be triggered to notify the administrator to handle it.

[0043] Another aspect of the present application provides an SQL scheduled task scheduling system, the system comprising:

[0044] The first building module is used to build a master database and a slave database;

[0045] A receiving module, configured to receive a scheduled task request, wherein the scheduled task request includes a type and an execution frequency of the scheduled task;

[0046] The second building block is used to build a task scheduling strategy using the Quartz.NET framework based on the scheduled task request;

[0047] The task allocation module is used to allocate scheduled tasks to the master database or slave database for execution based on the task scheduling strategy;

[0048] The acquisition module is used to obtain the load status of the master database and the slave database;

[0049] The strategy adjustment module is used to dynamically adjust the task scheduling strategy according to the load of the master database and the slave database;

[0050] The monitoring module is used to monitor the execution of scheduled tasks.

[0051] The present solution is a SQL scheduled task scheduling system that implements the scheduling of scheduled tasks through the mutual cooperation of a first construction module, a receiving module, a second construction module, a task allocation module, an acquisition module, a policy adjustment module, and a monitoring module. Compared with the existing database scheduled task scheduling method, the present solution uses the Quartz.NET framework to be responsible for the scheduling of scheduled tasks. The Quartz.NET framework provides a more flexible, efficient, and reliable task scheduling capability, which can be applied to complex environments with multiple databases. In addition, by executing the acquisition module and the policy adjustment module in the present solution, not only can the flexibility of task execution be improved, but also it can ensure that task scheduling can be optimized according to the database load, thereby improving the overall efficiency and stability of task scheduling.

[0052] Preferably, in the receiving module, the types of scheduled tasks include write tasks, read tasks and data synchronization tasks; the task allocation module includes: a first task allocation submodule, which is used to allocate the write task to the master database for execution when the type of the scheduled task is a write task; a second task allocation submodule, which is used to allocate the read task to the slave database for execution when the type of the scheduled task is a read task; and a third task allocation submodule, which is used to synchronize data from the master database to the slave database for execution when the type of the scheduled task is a data synchronization task.

[0053] In this embodiment, the first task allocation submodule is provided to ensure data consistency and integrity. The second task allocation submodule is provided to distribute the read load, reducing the burden on the master database and allowing the master database to focus more on processing write operations. The third task allocation submodule is provided to ensure data consistency between the master and slave databases, enhancing the system's data redundancy and fault tolerance.

[0054] Preferably, the policy adjustment module includes: a first policy adjustment submodule, which is used to adjust the task scheduling policy specifically as follows when the loads of the master database and the slave database are both in a normal state: assigning write tasks to the master database for execution, and assigning read tasks to all slave databases for execution in a round-robin manner; a second policy adjustment submodule, which is used to adjust the task scheduling policy specifically as follows when the load of the master database is in an overloaded state and the load of the slave database is in a normal state: assigning all read tasks in the master database to the slave database for execution, and delaying the execution of write tasks; a third policy adjustment submodule, which is used to adjust the task scheduling policy specifically as follows when the loads of the master database and the slave database are both in an overloaded state: adding a new slave database on the basis of the original slave database, and evenly assigning all read tasks to the corresponding slave databases for execution.

[0055] In this embodiment, the provision of a first policy adjustment submodule helps prevent single-point resource idleness. Furthermore, round-robin allocation of read tasks to all slave databases helps evenly distribute read requests, preventing overuse of any particular slave database. The provision of a second policy adjustment submodule helps alleviate pressure on the master database and prevent it from crashing. The provision of a third policy adjustment submodule enables dynamic resource expansion, improving the overall system processing capability.

[0056] Preferably, the system further includes: a creation module for creating new scheduled tasks; a modification module for modifying the configuration of existing scheduled tasks; and a deletion module for deleting scheduled tasks that are no longer needed. In this embodiment, when user demand leads to a change in task configuration, the creation module, modification module, and deletion module can be set to effectively shorten the demand response cycle.

[0057] Preferably, the system further comprises a recording module for recording an execution log of a scheduled task, wherein the execution log of the scheduled task includes the start time, end time, and execution result of the scheduled task. In this embodiment, by providing the recording module, a valuable basis is provided for maintenance and troubleshooting of the scheduling system.

[0058] In addition, the functional units in the various embodiments of the present invention may be integrated into a single processing module, each unit may exist physically separately, or two or more units may be integrated into a single module. The aforementioned integrated modules may be implemented in the form of hardware or in the form of software functional modules. If the integrated modules are implemented in the form of software functional modules and sold or used as independent products, they may also be stored in a computer-readable storage medium.

[0059] Although the embodiments of the present invention have been shown and described above, it will be understood that the above embodiments are exemplary and are not to be construed as limitations on the present invention. A person skilled in the art may change, modify, replace and modify the above embodiments within the scope of the present invention.

Claims

1. A SQL scheduled task scheduling method, characterized by: The following steps are involved: Step S1: Build a master database and a slave database, and receive a scheduled task request, wherein the scheduled task request includes the type and execution frequency of the scheduled task; Step S2: Based on the scheduled task request, the Quartz.NET framework is used to build a task scheduling strategy, and the scheduled task is assigned to the master database or the slave database for execution based on the task scheduling strategy; In the process of building the task scheduling strategy, the load conditions of the master database and the slave database are obtained, and the task scheduling strategy is dynamically adjusted according to the load conditions of the master database and the slave database; Step S3: Monitor the execution of the scheduled task.

2. The SQL scheduled task scheduling method according to claim 1, characterized in that: In step S1, the types of scheduled tasks include write tasks, read tasks, and data synchronization tasks; In step S2, the scheduled task is assigned to the master database or the slave database for execution based on the task scheduling policy, which specifically includes the following sub-steps: When the scheduled task type is a write task, the write task will be assigned to the primary database for execution; When the scheduled task type is a read task, the read task will be assigned to the slave database for execution; When the scheduled task type is a data synchronization task, the data is synchronized from the master database to the slave database for execution.

3. The SQL scheduled task scheduling method according to claim 2, characterized in that: In step S2, the task scheduling strategy is dynamically adjusted according to the load of the master database and the slave database, which specifically includes the following sub-steps: When the load on both the master and slave databases is normal, the task scheduling strategy is adjusted as follows: write tasks are assigned to the master database for execution, and read tasks are assigned to all slave databases in a round-robin fashion. When the master database is overloaded and the slave database is normally loaded, the task scheduling strategy is adjusted as follows: all read tasks in the master database are assigned to the slave database for execution, and write tasks are delayed. When both the master and slave databases are overloaded, the task scheduling strategy is adjusted as follows: a new slave database is added based on the original slave database, and all read tasks are evenly distributed to the corresponding slave database for execution.

4. The SQL scheduled task scheduling method according to claim 1, characterized in that: In step S1, before receiving a scheduled task request, the following steps are also included: creating a new scheduled task, modifying the configuration of an existing scheduled task, and deleting a scheduled task that is no longer needed.

5. The SQL scheduled task scheduling method according to claim 1, characterized in that: In step S3, the following steps are also included: recording the execution log of the scheduled task, wherein the execution log of the scheduled task includes the start time, end time and execution result of the scheduled task.

6. A SQL scheduled task scheduling system, using the SQL scheduled task scheduling method according to any one of claims 1 to 5, characterized in that: The system comprises: The first building module is used to build a master database and a slave database; A receiving module, configured to receive a scheduled task request, wherein the scheduled task request includes a type and an execution frequency of the scheduled task; The second building block is used to build a task scheduling strategy using the Quartz.NET framework based on the scheduled task request; The task allocation module is used to allocate scheduled tasks to the master database or slave database for execution based on the task scheduling strategy; The acquisition module is used to obtain the load status of the master database and the slave database; The strategy adjustment module is used to dynamically adjust the task scheduling strategy according to the load of the master database and the slave database; The monitoring module is used to monitor the execution of scheduled tasks.

7. The SQL scheduled task scheduling system according to claim 6, characterized in that: In the receiving module, the types of scheduled tasks include write tasks, read tasks and data synchronization tasks; The task allocation module includes: The first task allocation submodule is used to allocate the write task to the main database for execution when the type of the scheduled task is a write task; The second task allocation submodule is used to allocate the read task to the slave database for execution when the scheduled task is a read task; The third task allocation submodule is used to synchronize data from the master database to the slave database for execution when the type of the scheduled task is a data synchronization task.

8. The SQL scheduled task scheduling system according to claim 7, characterized in that: The policy adjustment module includes: The first strategy adjustment submodule is used to adjust the task scheduling strategy as follows when the load of the master database and the slave database are both in normal state: assign the write task to the master database for execution, and assign the read task polling to all slave databases for execution; The second strategy adjustment submodule is used to adjust the task scheduling strategy as follows when the load of the master database is overloaded and the load of the slave database is normal: all read tasks in the master database are assigned to the slave database for execution, and write tasks are delayed; The third strategy adjustment submodule is used to adjust the task scheduling strategy as follows when the loads of the master database and the slave database are both overloaded: a new slave database is added based on the original slave database, and all read tasks are evenly distributed to the corresponding slave database for execution.

9. The SQL scheduled task scheduling system according to claim 6, characterized in that: Also includes: Create a module to create a new scheduled task; Modification module, used to modify the configuration of existing scheduled tasks; The delete module is used to delete scheduled tasks that are no longer needed.

10. The SQL scheduled task scheduling system according to claim 6, characterized in that: Also includes: The recording module is used to record the execution log of the scheduled task, wherein the execution log of the scheduled task includes the start time, end time and execution result of the scheduled task.

Images