Hi @annetdeboer, did you try the writer parameter Features to write per transaction?
Hi @annetdeboer, did you try the writer parameter Features to write per transaction?
Hi @itay it's currently set to 2000; the test-workspace writes about 9 records so I figured that should be suffice..?
This is a known limitation when writing multiple tables using transactions in FME. I think that Safe is working on alternative strategies to improve it, but I have no idea what their timeline is.
If consistency is very important to you, I highly recommend you write your data into a temporary staging schema first, validate it for consistency and then transfer it into your production schema if all is ok. This is not only very robust, but also more in tune with database best practices when doing bulk imports.
The last transfer must be done in a single atomic transaction, which e.g. can be done using an anonymous block inside a SQLExecutor.
Example (notice that you must not set FME_SQL_DELIMITER here):
BEGIN
SAVEPOINT start_tran;
INSERT INTO .... ; -- first DML
UPDATE .... ; -- second DML
BEGIN ... END; -- some other work
UPDATE .... ; -- final DML
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO start_tran;
RAISE;
END;
My initial observation is that you are using multiple writers - assuming we're using the term "writer" in the same way.
Can you not have a single writer that writes to multiple tables? I don't know about David's limitation (do you have a PR number David?) but I would think a single writer with multiple tables is more likely to rollback everything than multiple writers, each writing to a single table.
My initial observation is that you are using multiple writers - assuming we're using the term "writer" in the same way.
Can you not have a single writer that writes to multiple tables? I don't know about David's limitation (do you have a PR number David?) but I would think a single writer with multiple tables is more likely to rollback everything than multiple writers, each writing to a single table.
By limitation I mean that it is not trivial to load huge amounts of data spread over multiple tables into a relational database in an atomic transaction using FME.
You could of course put everything in the same writer and increase the transaction size enough so that everything fits, but that's not very good for performances if there is a lot of data. You also won't discover any potential errors before the workspace ends, possibly after hours of processing, causing a massive rollback. If your input data isn't in a format that's easy to query (e.g. CSV), it can be time-consuming to pinpoint where the problem is. At least that's my experience :-)
Hi @annetdeboer,
Well how about using the FeatureWriter and then validating the results via a follow up SQLExecutor?
This way you could validate that the number of features written is as expected and if that is not the case follow it up with another SQLExecutor to roll back the changes.
This is something displayed in the FMEWT this year, so I guess the demo workspaces might be available somewhere and if not its quite straightforward to set up.
My initial observation is that you are using multiple writers - assuming we're using the term "writer" in the same way.
Can you not have a single writer that writes to multiple tables? I don't know about David's limitation (do you have a PR number David?) but I would think a single writer with multiple tables is more likely to rollback everything than multiple writers, each writing to a single table.
We do use one writer for all database tables @Mark2AtSafe
The rollback only happens when the workspace fails with a Terminator (and then only when using a 'set transaction' from SQL Executor as a first step) but it does not rollback anything when it fails with i.e. a ORA- failure.
The workspace is called from a different process and it only handles like 10 or 20 records per run. Could be more or less sometimes but never more than 1k at once.
Hi @annetdeboer,
Well how about using the FeatureWriter and then validating the results via a follow up SQLExecutor?
This way you could validate that the number of features written is as expected and if that is not the case follow it up with another SQLExecutor to roll back the changes.
This is something displayed in the FMEWT this year, so I guess the demo workspaces might be available somewhere and if not its quite straightforward to set up.
Thanks, unfortunately it does not work. I tested this in 2016 with a FeatureWriter and the same thing happened there. It writes what it can write before failing (and commits, also).
I used a SQL Executor to 'rollback' when validating fails, but the commit has already happened right after it leaves the FeatureWriter (tested that with a breakpoint) so it never performs a rollback.
Thanks for the idea though!
Thanks, unfortunately it does not work. I tested this in 2016 with a FeatureWriter and the same thing happened there. It writes what it can write before failing (and commits, also).
I used a SQL Executor to 'rollback' when validating fails, but the commit has already happened right after it leaves the FeatureWriter (tested that with a breakpoint) so it never performs a rollback.
Thanks for the idea though!
I didnt expect it to be able to do a rollback but actually that you should delete the commited features yourself and in effect do a 'rollback'
We do use one writer for all database tables @Mark2AtSafe
The rollback only happens when the workspace fails with a Terminator (and then only when using a 'set transaction' from SQL Executor as a first step) but it does not rollback anything when it fails with i.e. a ORA- failure.
The workspace is called from a different process and it only handles like 10 or 20 records per run. Could be more or less sometimes but never more than 1k at once.
I'm sort of surprised that we don't rollback with an ORA failure. Only when the error is related to the Oracle undo space should that be an issue. Can you send the workspace and log file over to our support team and ask them to look into it?
Is there any way to put all writers into one transaction so that
it only commits the changes when there is no other work left to be done
by the workspace
Aside from the rollback issue, did you look into the different Oracle parameters in the Readers and Writers manual? In particular I'm thinking about Features Per Bulk Write and Features to Write Per Transaction.
If you set Features Per Bulk Write to a high number, FME will cache the data in memory and send it to Oracle as a large chunk. Make sure it is the same as Features to Write Per Transaction and set that to a high number too. Then the data will only be sent to Oracle and committed when it is correct.
Both of these are Writer parameters (not feature type parameters) so if you only have one "writer" writing to several "feature types" (tables) then they may help. Only if you truly have multiple "writers" will they not help.
I didnt expect it to be able to do a rollback but actually that you should delete the commited features yourself and in effect do a 'rollback'
That's not possible, because they're updates, not inserts :/
I'm sort of surprised that we don't rollback with an ORA failure. Only when the error is related to the Oracle undo space should that be an issue. Can you send the workspace and log file over to our support team and ask them to look into it?
I will do that, thanks in advance
Is there any way to put all writers into one transaction so that
it only commits the changes when there is no other work left to be done
by the workspace
Aside from the rollback issue, did you look into the different Oracle parameters in the Readers and Writers manual? In particular I'm thinking about Features Per Bulk Write and Features to Write Per Transaction.
If you set Features Per Bulk Write to a high number, FME will cache the data in memory and send it to Oracle as a large chunk. Make sure it is the same as Features to Write Per Transaction and set that to a high number too. Then the data will only be sent to Oracle and committed when it is correct.
Both of these are Writer parameters (not feature type parameters) so if you only have one "writer" writing to several "feature types" (tables) then they may help. Only if you truly have multiple "writers" will they not help.
The workspace where I test this issue has only one writer.
The workspace that was supposed to be the definite one has multiple writers though.. (spatial and non-spatial) will see if I can at least try it out with the test-workspace to set the same high number to Features to Write Per Transaction and the Features per Bulk Write. Will let you know the outcome (I can test this no earlier than friday)
That's not possible, because they're updates, not inserts :/
Ha, ok if you store the features that are used for the upade in a temp ffs you could re-update with the original data...in case of a failure.
A lot is possible to bulit in a fault torelance process but solving the problem of other applications in FME is not always the best option.
Any luck with applying Marks idea of the writer settings?
This is a known limitation when writing multiple tables using transactions in FME. I think that Safe is working on alternative strategies to improve it, but I have no idea what their timeline is.
If consistency is very important to you, I highly recommend you write your data into a temporary staging schema first, validate it for consistency and then transfer it into your production schema if all is ok. This is not only very robust, but also more in tune with database best practices when doing bulk imports.
The last transfer must be done in a single atomic transaction, which e.g. can be done using an anonymous block inside a SQLExecutor.
Example (notice that you must not set FME_SQL_DELIMITER here):
BEGIN
SAVEPOINT start_tran;
INSERT INTO .... ; -- first DML
UPDATE .... ; -- second DML
BEGIN ... END; -- some other work
UPDATE .... ; -- final DML
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO start_tran;
RAISE;
END;
I am finding that the rollback functionality does not work even when writing to only one table. I am using a dataset with only 16 records with Features Per Transaction and Bulk Write Size settings set to defaults of 1000 and 200 respectively but if there is an Oracle error there is no rollback.
