Transaction Control Example

Application Scenario

1. You want all data fetched from the current source to be loaded within one atomic transaction during data writing to the target database in a single execution of scheduled synchronization.

• Success scenario: All data is successfully written. The transaction is committed, updating the target table.

• Failure scenario: If any error occurs, the entire transaction rolls back, leaving the target table completely unchanged.

2. After the scheduled synchronization task completes, statistical metrics of that write operation can be written to a custom table for subsequent data validation purposes.

Implementation Method

Starting from FineDataLink V4.2.7.4, this can be implemented by using the Transaction Control function in the Data Synchronization node or the DB Table Output operator.

The example in this section applies to the following scenario: You want to synchronize data to Oracle databases with the support of failure rollback, and want the maximum timestamp of the current load to be recorded into a designated table upon successful synchronization for subsequent incremental synchronization or data verification.

Data Reading

1. Create a scheduled task.

2. Drag in a Data Synchronization node and configure it to read data from the source table, as shown in the following figure.

Click Data Preview to view the fetched data.

Configuring the Pre-/Post-execution SQL Statements for Data Writing

1. Configure the target table on the Data Destination and Mapping tab page, as shown in the following figure.

2. Click the Write Method tab, set Write Method and Primary Key Mapping, and configure the pre-execution and post-execution SQL statements in Transaction Control, as shown in the following figure.

In this example, since you want the maximum timestamp of the current load to be recorded into a designated table upon successful synchronization, set the post-execution SQL statement as follows:

INSERT INTO "HR"."DUANDIAN"(TIME)
SELECT MAX(Contract_Signing_Time) AS TIME FROM @temp_tbl_nm_full;

Note:

1. The SQL statement means to fetch the maximum contract signing time from the intermediate table in the current execution and write it into the TIME field of the specified self-provided table DUANDIAN.

2. The intermediate table has the same fields as the target table, serving as an intermediate storage table for the rollback of the written data. Use @temp_tbl_nm_full to reference the intermediate table.

Effect Display

1. Run the task. If the execution is successful, the specified data will be written to the target database, as shown in the following figure.

2. The maximum timestamp will also be recorded in the DUANDIAN table, as shown in the following figure.

3. If the execution fails, the entire batch of data will not be written to the specified database, and the rollback will be performed.