A multi-threaded ETL method with fine-grained features

By using transaction coordinator and exception capture mechanism during ETL multi-threaded data entry, fine-grained transaction control is achieved, and the problems of transaction processing complexity and database connection exhaustion in the existing technology are solved, and system performance is improved. reliability.

CN118394826BActive Publication Date: 2024-11-15NORTHEASTERN UNIV CHINA
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410562839.8
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-05-08
Publication Date
2024-11-15
Estimated Expiration
2044-05-08

AI Technical Summary

Technical Problem

During the ETL multi-threaded data storage process, it is difficult for the prior art to achieve fine-grained transaction control, resulting in high transaction complexity, increased risk and exhaustion of database connections.

Method used

By setting up a transaction coordinator, the child thread allows nested execution of another transaction during execution, forming a transaction hierarchy, implementing fine-grained transaction control, and using exception catching and recovery mechanisms to handle exceptions.

Benefits of technology

Improves data consistency and integrity, simplifies transaction management, reduces the risk of database connection exhaustion, improves system performance and reliability, and supports more refined error handling and resource management.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118394826B_ABST
    Figure CN118394826B_ABST
Patent Text Reader

Abstract

The present invention discloses a multi-threaded ETL method with fine-grained characteristics, which belongs to the field of computer technology, and the steps include: initializing and managing the overall transaction; creating a child thread and processing the child thread; the child thread requests a nested transaction and processes the nested transaction; synchronizing and communicating between threads; setting an exception handling and recovery mechanism; and committing and rolling back the overall transaction. The present invention adopts the above method, and in the ETL multi-threaded data warehousing stage, the child thread is allowed to nestly execute another transaction during the execution process, thereby forming a hierarchical structure of transactions and more finely controlling the boundaries and granularity of transactions.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a multi-threaded ETL method with fine-grained characteristics. Background Art

[0002] In the modern ETL process, data processing is divided into three parts: data extraction, data cleaning, and data import. In the data import stage, using a multi-threading mechanism to increase the speed of warehousing is a common performance optimization method: multi-threading can significantly increase the import speed by splitting the task from the previous serial writing to parallel writing. However, the introduction of multi-threading has made transaction processing a new technical pain point. In addition, a sub-thread requires a database connection, and there is a problem of database connection exhaustion due to too many sub-threads. At the same time, a sub-thread starts an independent transaction, which makes it very complicated and difficult to use framework technology to proxy and manage transactions.

[0003] One of the key technologies to solve the above problems is to use thread tool classes to merge multi-threaded transactions into an overall transaction to implement transaction control. This solution has been widely adopted in actual environments. However, this solution can only perform single-level transaction management when controlling transactions, which means that a transaction is an indivisible unit of work, and the operations in a transaction are either completed in full or not completed in full. This limits its flexibility in handling complex business logic, especially since child threads can only participate in overall transactions, which may lead to excessive granularity of transactions, increasing the complexity and risk of transactions. Because the transaction granularity is too coarse, rolling back exceptions in the middle and later stages of data storage will consume a lot of time and cause a lot of unnecessary burden on the hardware. Summary of the invention

[0004] The purpose of the present invention is to provide a multi-threaded ETL method with fine-grained characteristics. In the ETL multi-threaded warehousing stage, a transaction coordinator is set, and the transaction coordinator is used to enable a child thread to nestly execute another transaction during the execution process, thereby forming a hierarchical structure of transactions and more finely controlling the boundaries and granularity of transactions.

[0005] To achieve the above object, the present invention provides a multi-threaded ETL method with fine-grained characteristics, the steps comprising:

[0006] S1. Initialize and manage the overall transaction;

[0007] S2, create a child thread and process the child thread;

[0008] S3, the child thread requests a nested transaction and processes the nested transaction;

[0009] S4, synchronization and communication between threads;

[0010] S5. Set up exception handling and recovery mechanisms;

[0011] S6. Commit the entire transaction.

[0012] Preferably, step S1 includes:

[0013] S11. In the main thread, create a transaction coordinator to coordinate and manage multi-threaded transactions;

[0014] S12, the transaction coordinator initializes the overall transaction and obtains the database connection;

[0015] S13. Share the database connection in the overall transaction and set it to manual commit mode;

[0016] S14. The transaction coordinator manages the status information of the entire transaction, including whether the transaction is started, completed, committed, or rolled back.

[0017] Preferably, step S2 includes:

[0018] S21. A child thread is created, and the main thread passes a reference to the transaction coordinator to the child thread;

[0019] S22. After receiving the transaction coordinator reference, the child thread obtains the context information of the current overall transaction, and prepares to execute its own database operations and participate in the transaction.

[0020] Preferably, in step S3, the child thread requests to start a nested transaction in the transaction coordinator. After receiving the request, the transaction coordinator uses the save point mechanism of the database to start the nested transaction. When the child thread performs a database operation in the nested transaction, the nested transaction is committed or rolled back according to the operation result.

[0021] Preferably, in step S4, synchronization and communication are performed between sub-threads and between sub-threads and the main thread. The transaction coordinator provides synchronization primitives, including locks and conditional variables, to coordinate the order of operations between threads. After the sub-thread completes the nested transaction, the transaction coordinator sends a completion signal to the main thread. After the main thread receives all sub-thread signals, it commits or rolls back according to the results.

[0022] Preferably, step S5 includes:

[0023] S51, capturing the exception that occurs when the child thread executes the nested transaction, and handling the exception using a fine-grained transaction method;

[0024] S52: When the exception can be handled, the child thread attempts to resume the execution of the transaction;

[0025] S53. When an exception occurs that cannot be handled, the transaction coordinator is notified and exception information is provided. The transaction coordinator rolls back the erroneous nested transaction or the entire transaction based on the received exception information and business logic.

[0026] Preferably, in step S6, when all child threads successfully complete the nested transaction and there are no unhandled exceptions, the main thread commits the entire transaction through the transaction coordinator.

[0027] Therefore, the present invention adopts the above-mentioned multi-threaded ETL method with fine-grained characteristics, which has the following beneficial effects:

[0028] (1) A transaction coordinator is set up. By using the transaction coordinator, exception capture and processing mechanism, nested transaction and other technologies, multi-threaded transactions are effectively merged into an overall transaction, and the nested transaction function is supported in the child thread, so as to achieve fine-grained control of transactions in the ELT multi-threaded data storage process, which not only improves the consistency and integrity of data, but also simplifies the complexity of transaction management and improves the performance and reliability of the system;

[0029] (2) In complex business logic, different subtasks or subprocesses are divided into independent nested transactions to achieve more refined error handling. When a subtask has a problem, only the transaction of the subtask is rolled back without affecting the execution of other subtasks. At the same time, fine-grained transaction control is achieved using limited database connections. Compared with managing transactions individually by sub-threads, this can effectively avoid the problem of database connection exhaustion caused by too many sub-threads, and has better resource management capabilities.

[0030] (3) The fine-grained transaction function makes exception handling simpler and more centralized. When an operation in a child thread is abnormal, the transaction of the child thread can be simply rolled back and an exception can be thrown to the upper layer. The upper layer logic decides how to handle the exception. This avoids extending exception handling across multiple threads and reduces code complexity and maintenance costs.

[0031] The technical solution of the present invention is further described in detail below through the accompanying drawings and embodiments. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Figure 1 This is a multi-threaded ETL technology flow chart with fine-grained transaction characteristics according to an embodiment of the present invention;

[0033] Figure 2 This is a visualization statistical chart of data storage according to an embodiment of the present invention. DETAILED DESCRIPTION

[0034] Example

[0035] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments.

[0036] Reference Figure 1 , a multi-threaded ETL approach with fine-grained features, including:

[0037] S1. Initialize and manage the overall transaction.

[0038] In the main thread, create a thread tool class - transaction coordinator to coordinate and manage the execution of multi-threaded transactions. The transaction coordinator is responsible for initializing the framework of an overall transaction and obtaining a database connection. In the overall transaction, the database connection is shared and set to manual commit mode. The manual commit mode can finely control the commit or rollback of the transaction during the transaction.

[0039] The transaction coordinator manages the status information of the entire transaction, including but not limited to whether the transaction has started, completed, successfully committed, or rolled back for some reason.

[0040] S2. Create a child thread and process the child thread.

[0041] When a child thread is created in the main thread to perform concurrent tasks, the main thread passes the reference of the transaction coordinator to the child thread. After receiving the reference of the transaction coordinator, the child thread obtains the context information of the current overall transaction, including the transaction status, database connection, etc., and prepares to perform its own database operations and participate in the transaction.

[0042] S3. The child thread requests a nested transaction and processes the nested transaction.

[0043] When the child thread performs complex business logic, the operations performed are atomic. When the child thread needs to perform a series of relatively independent database operations, the child thread requests to start a nested transaction in the transaction coordinator. After receiving the request, the transaction coordinator uses the database's save point mechanism to start the nested transaction. The save point mechanism allows a mark to be set at a certain point in the transaction. If the subsequent transaction operation fails, the mark can also be used to roll back to the record point instead of rolling back the entire transaction. When the child thread performs database operations in a nested transaction, the nested transaction is committed or rolled back according to the operation results. When the nested transaction is successfully completed, its changes are permanently saved to the database; if the nested transaction fails, its changes will be undone and the database will return to the state before the nested transaction started.

[0044] S4. Synchronization and communication between threads.

[0045] In order to ensure the atomicity of the entire transaction, the sub-threads and the main thread synchronize and communicate with each other. The transaction coordinator provides various synchronization primitives, including locks and condition variables. These synchronization primitives coordinate the operation order between threads to prevent data competition and inconsistency. After the sub-thread completes the nested transaction, it sends a completion signal to the main thread through the transaction coordinator. After the main thread receives all the sub-thread signals, it will summarize all the signals and commit or roll back according to the results. When all the signals are correct, the entire transaction is committed.

[0046] S5. Set up exception handling and recovery mechanism.

[0047] In complex business logic and high-concurrency scenarios, exceptions are inevitable. It is necessary to capture exceptions that occur when child threads execute nested transactions. According to the impact of various exceptions on the overall data, fine-grained transaction methods are used to handle exceptions. After capturing the exception, first roll back to the record position and restart the nested transaction. When the exception can be handled, the child thread attempts to resume the execution of the transaction; when the exception cannot be handled, notify the transaction coordinator and provide exception information. The transaction coordinator determines the impact of the nested transaction based on the received exception information and business logic. When the nested transaction does not affect the remaining nested transactions, roll back the erroneous nested transaction to a specific mark point, skip the nested transaction, and commit the remaining transactions after execution. When the nested transaction affects the remaining nested transactions, the overall transaction is rolled back.

[0048] S6. Commit the entire transaction.

[0049] When all child threads successfully complete the nested transaction and there are no unhandled exceptions, the main thread commits the overall transaction through the transaction coordinator.

[0050] Reference Figure 2 , Figure 2 To visualize the data in the warehousing process, it is convenient to observe the execution status of the transaction in real time.

[0051] Therefore, the present invention adopts a multi-threaded ETL method with fine-grained characteristics. In the ETL multi-threaded warehousing stage, by using transaction coordinator, exception capture and processing mechanism, nested transaction and other technologies, multi-threaded transactions are effectively merged into an overall transaction, and nested transaction processing functions are supported in child threads, thereby realizing fine-grained control of transactions in the ELT multi-threaded data warehousing process, which not only improves the consistency and integrity of data, but also simplifies the complexity of transaction management, and improves the performance and reliability of the system.

[0052] Finally, it should be noted that the above embodiments are only used to illustrate the technical solution of the present invention rather than to limit it. Although the present invention has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that they can still modify or replace the technical solution of the present invention with equivalents, and these modifications or equivalent replacements cannot cause the modified technical solution to deviate from the spirit and scope of the technical solution of the present invention.

Claims

1. A multi-threaded ETL method with fine-grained characteristics, characterized in that the steps include: S1. Initialize and manage the overall transaction, including: S11. In the main thread, create a transaction coordinator to coordinate and manage multi-threaded transactions; S12, the transaction coordinator initializes the overall transaction and obtains the database connection; S13. Share the database connection in the overall transaction and set it to manual commit mode; S14. The transaction coordinator manages the status information of the entire transaction, including whether the transaction is started, completed, committed, or rolled back; S2, create a child thread and process the child thread; S3. The child thread requests a nested transaction and processes the nested transaction, including: The child thread requests to start a nested transaction in the transaction coordinator. After receiving the request, the transaction coordinator uses the save point mechanism of the database to start the nested transaction. When the child thread performs database operations in the nested transaction, the nested transaction is committed or rolled back according to the operation results. S4. Synchronization and communication between threads, including: Synchronize and communicate between child threads and between child threads and the main thread. The transaction coordinator provides synchronization primitives, including locks and conditional variables, to coordinate the operation sequence between threads. After the child thread completes the nested transaction, it sends a completion signal to the main thread through the transaction coordinator. After the main thread receives all child thread signals, it commits or rolls back according to the results. S5. Set up exception handling and recovery mechanisms; S6. Commit the entire transaction.

2. A multi-threaded ETL method with fine-grained characteristics according to claim 1, characterized in that: Step S2 includes: S21. A child thread is created, and the main thread passes a reference to the transaction coordinator to the child thread; S22. After receiving the transaction coordinator reference, the child thread obtains the context information of the current overall transaction, and prepares to execute its own database operations and participate in the transaction.

3. A multi-threaded ETL method with fine-grained characteristics according to claim 2, characterized in that: Step S5 includes: S51, capturing the exception that occurs when the child thread executes the nested transaction, and handling the exception using a fine-grained transaction method; S52: When the exception can be handled, the child thread attempts to resume the execution of the transaction; S53. When an exception occurs that cannot be handled, the transaction coordinator is notified and exception information is provided. The transaction coordinator rolls back the erroneous nested transaction or the entire transaction based on the received exception information and business logic.

4. A multi-threaded ETL method with fine-grained characteristics according to claim 3, characterized in that: In step S6, when all child threads successfully complete the nested transaction and there are no unhandled exceptions, the main thread commits the entire transaction through the transaction coordinator.