An atomic statement is a statement that is executed completely or not at all. For example, a LOAD statement that inserts thousands of rows might encounter an error after many rows. If the statement does not complete, and the default ON FILE ERROR ROLLBACK option is in effect, all changes are undone. This LOAD statement is atomic.
All noncompound SQL statements are atomic. A compound statement can be made atomic by adding the keyword ATOMIC after the BEGIN keyword.
BEGIN ATOMIC INSERT INTO sales_order (id, order_date, sales_rep) VALUES (41880, 1998-08-24, 2054) ; INSERT INTO sales_order_items (line_id, prod_id, quantity, ship_date) VALUES (01, 43629, 15, 'bad_data') ; END;
In this example, the two INSERT statements are part of an atomic compound statement. They must either succeed or fail as one. The first INSERT statement would succeed. The second one causes a data conversion error since the value being assigned to the ship_date column cannot be converted to a date.
The atomic compound statement fails and the effect of both INSERT statements is undone. Even if the currently executing transaction is eventually committed, neither statement in the atomic compound statement takes effect.
COMMIT and ROLLBACK and some ROLLBACK TO SAVEPOINT statements are not permitted within an atomic compound statement. See “Transactions and savepoints in procedures”.
There is a case where some, but not all, of the statements within an atomic compound statement are executed. This is when an error occurs, and is handled by an exception handler within the compound statement.
For more information, see “Using exception handlers in procedures”.
