Transaction Rollback: Scenarios
When you map data to a database, you may encounter various database-related errors (e.g., the database account may not have enough privileges to perform a specific database action). To prevent such errors from aborting the execution of the mapping, you can configure transaction rollback settings, which will enable you to revert changes. You can enable transaction rollback at database-component level, at table-action level, and at stored-procedure level.
This topic describes some of the possible scenarios of transaction rollbacks. All the scenarios in this topic feature a source XML file called Authors.xml and a target database called BookCatalog.sqlite (mapping below). The Authors and Books tables have a foreign-key relationship. The Author table is the parent of the Books table.
In all the scenarios, the same database table actions have been configured:
•The combination of the Update If and Insert Rest actions in the Authors table. The Update If condition is set on the Author column.
•The combination of the Update If and Insert Rest actions in the Books table. The Update If condition is set on the Title column.
Besides, tracing has been enabled at component level (set to Always), in the Authors table (Use component settings), and in the Books table (Use component settings). Transaction rollback settings will vary depending on the scenario.
Broad outline of scenarios
The scenarios described in this topic are outlined below:
•Scenario 1: If an Author record is faulty, it will be rolled back with all its child records; if only a Book record is faulty, only this record will be rolled back.
•Scenario 2: If an Author record is faulty, it will be rolled back with all its child records.
•Scenario 3: If a Book record is faulty, only this record will be rolled back.
•Scenario 4: If a Book record cannot be inserted, its parent record will be rolled back (similar to Scenario 2).
•Scenario 5: If there is a database-related error, all the changes made to the database will be rolled back.
Scenario 1: Roll back current Author and current Book
In the first scenario, we enable transaction handling at table-action level: The Rollback current transaction and continue option is set in both tables of the database. The combination of these settings means that (i) if there is a faulty Author record, neither this record nor its child Book records will be inserted into the database, and then the processing of the next record will start; (ii) if there is a faulty Book record, but its parent Author record is valid, only the faulty Book record will be rolled back, and the processing of the next record will start.
Authors.xml
The Authors.xml file contains information about authors and their books. One of the Author records lacks information about the author's country. The source file also has one Book record that has no information about the publisher.
BookCatalog.sqlite
We have set a NOT NULL constraint on the Country column in the Authors table and the Publisher column in the Books table. Mapping null values to these columns will cause an error.
Output
After running the SQL script in the Output pane, we get the following dialog:
The Database Transaction Exception dialog informs us about a database-related error and the reason for this error. In this example, a null value could not be inserted into the Country column of the Authors table (red rectangular above). This dialog box also allows choosing the next action. By default, the action configured in the Database Table Actions dialog is selected. It is also possible to apply the same setting to all subsequent errors or to roll back the faulty transaction and stop.
After confirming the selection, we are notified about another error that has occurred in the Books table (screenshot below). This error occurred because the mapping tried inserting absent Publisher information from one of our Book records into the Publisher field of the Books table. Since the error occurred in the child table, it is possible to roll back the current transaction (Rollback this transaction and stop/continue) or the changes up to the top transaction (Rollback top and stop/continue). The top transaction in this context means the parent record (Author) of the faulty child record (Book).
If there are multiple levels in the hierarchy, a rollback will be done for each level until the rollback reaches the topmost level at which transaction handling has been enabled. Depending on your needs, you can choose to continue or stop the execution of the mapping. The Rollback top and continue/stop option is available only when there are nested transactions. For more information about rolling back top transactions, see Scenario 4 below.
In this example, we leave the transaction rollback option shown in the screenshot above unchanged. After the processing of all the records has finished, we can see the generated SQL statements in the Output pane, the trace report in XML format, and the data that is now available in the database.
Generated SQL statements
An extract of the generated SQL script is illustrated below. The failed insert operation has been rolled back to the SAVEPOINT. The SAVEPOINT command represents a point in a transaction, to which the transaction will be rolled back. The SAVEPOINT command allows undoing the changes made after the SAVEPOINT and restore the transaction to the state at the time of the SAVEPOINT.
Trace report
An extract of the trace report is provided below. The trace report shows the fields and their values that were traced (e.g., <Price>8.6</Price>), provides information about the performed actions (e.g., insert) and the error (see the trace:error element).
<Books>
<trace:values>
<Title>Cockroaches</Title>
<AuthorID>41</AuthorID>
<ISBN>0099590328</ISBN>
<Publisher xsi:nil="true"/>
<NumPages>464</NumPages>
<Year>2016</Year>
<Genre>Crime & Mystery</Genre>
<Price>8.6</Price>
</trace:values>
<trace:actions>
<trace:update rows-affected="0"/>
<trace:insert>
<trace:error code="19" state="1299">NOT NULL constraint failed: Books.Publisher</trace:error>
</trace:insert>
</trace:actions>
</Books>
Updated data in DB
To see what data is currently available in the database, you can use the DB Query pane. The extract of the Books table below shows that several new records have successfully been inserted. Since inserting one of Jo Nesbo's books has failed (trace report above), only two of his books out of three have been mapped (The Bat and The Redbreast).
Other possible scenarios
Some other possible scenarios are described below.
Scenario 2: Roll back current Author and all Author's Books
In this scenario, the Rollback current transaction and continue option is set only in the Authors table. With this setup, each Author record and the corresponding Book records are seen as an atomic operation. If there is a faulty Author record or at least one of the author's books is faulty, this author's record and its related child records will be rolled back. After that, processing continues with the next Author record.
Scenario 3: Roll back only current Book
In this scenario, the Rollback current transaction and continue option is set only in the Books table. If there is a faulty Book record, only this Book record will be rolled back, and then processing will continue. If you set the Rollback top transaction and continue option in the Books table and no other transaction-handling options at parent level, still only the faulty Book record will be rolled back. In this case, there is no difference between rolling back the top and current transaction, because there is only one transaction level and no transaction nesting.
Scenario 4: Roll back Author if Book cannot be inserted
In this scenario, the transaction action for the Authors table is set to Rollback current transaction and continue, and the transaction action for the Books table is Rollback top transaction and continue. With this setup, if inserting a child record fails, the rollback operation will be done for each level of the hierarchy, up to the topmost level at which transaction handling has been enabled. For example, we want to insert a new Author record (Jo Nesbo) and its three child records. In one of the Book records (The Cockroaches), there is no information about the publisher. Since the Books table has a NOT NULL constraint on the Publisher column, mapping a null value to this field will cause an error. In this scenario, when an error occurs, the faulty Book record (The Cockroaches) will be rolled back together with the other two Book records and the parent record. Then processing will continue with the next Author record.
The combination of the transaction-handling options described in this scenario is particularly relevant to more complex, nested structures (e.g., a parent table with two or more child tables).
Scenario 5: Roll back all DB transactions and continue
In this scenario, the Rollback top transaction and continue option is set at component level. This setting can be particularly useful when you have a chained mapping (e.g., XML-DB-JSON). If there is a database-related error, all the changes made to the database component will be rolled back, and processing will continue with the JSON component.