I have a question regarding the use of BEGIN ATOMIC:
As I understood the database has to run in CHAINED MODE to use an atomic block, because I can not use commit/rollback inside it.
So whats the use of the atomic block if I have to execute commit/rollback after exiting that block and with the database being in chained mode everything since the last commit/rollback is one big atomic operation anyway.
It seems to me that marking the BEGIN-END-block as 'atomic' has no effect.
Using BEGIN ATOMIC will ensure the statements between BEGIN and END will execute completely or not at all. If there are many SQL statements between BEGIN ATOMIC and END and an error occurs in any of those statements, everything done to the database since the BEGIN ATOMIC will be undone.
hmm, I thought the chained mode will handle that already for me. If I'm in chained mode I have execute a rollback (in case of error) and everything since the last commit will be undone. Or in case of success I execute a commit and everything will be written to the database. That's already atomic...
BEGIN ATOMIC is different in two ways. First, the operations are undone automatically (no ROLLBACK statement). Second, you are not undoing everything from the beginning of the transaction, but just to the beginning of the compound statement. This is most useful when you are doing EXCEPTION handling.