Atomicity in Pervasive PSQL Databases
The principle of atomicity states that if a given statement does not execute to completion, then it should not leave partial or ambiguous effects in the database. For example, if a statement fails after it has inserted three out of five records but does not undo that insert, then the database is not in a consistent state when you retry the operation. If the statement is atomic and it fails to complete execution, then all changes are rolled back, so that the database is in a consistent state. In this example, if all five records are not successfully inserted, then none of them are inserted.
The atomicity rule is especially significant for statements that modify multiple records or tables. It also makes retrying failed operations simpler, because any previous attempt is guaranteed not to have left any partial effects.
Pervasive PSQL enforces atomicity in two ways:
1
Any UPDATE, INSERT, or DELETE statement is defined to be atomic. Pervasive PSQL guarantees that, if a multi-record and/or multi-table modification operation fails, none of the effects of that modification remain in the database.
This is true for Update, Insert, or Delete operations whether or not they are performed inside or outside of procedures.
2
You may specify stored procedures as ATOMIC when you create them. Such procedures apply the rule of atomicity to their entire execution. Therefore, not only do UPDATE, INSERT, or DELETE statements within an ATOMIC procedure execute atomically, but if any other statements within that procedure fail, all effects of the procedure’s execution thus far are rolled back.
Transaction Control in Procedures
Because triggers are always initiated by an external data change statement (INSERT, DELETE, or UPDATE), and all data change statements are defined to be atomic, the following statement are not allowed in triggers or in any procedures invoked by triggers:
•
START TRANSACTION
•
COMMIT WORK
•
ROLLBACK WORK (including RELEASE SAVEPOINT and ROLLBACK TO SAVEPOINT)
In other words, triggers follow the same rules as ATOMIC compound statements.
No user-initiated COMMIT WORK, ROLLBACK WORK, RELEASE SAVEPOINT, or ROLLBACK TO SAVEPOINT statement can cause a system-begun transaction (for purposes of atomicity) to end.