Autonomous Transactions Flashcards
A transaction starts when one of the following events take place -
- The first SQL statement is performed after connecting to the database.
- At each new SQL statement issued after a transaction is completed.
A transaction ends when one of the following events take place −
- A COMMIT or a ROLLBACK statement is issued
- A DDL or DCL statement is issued (Commit automatically performed)
- The user disconnects from the database
- A DML statement fails (Rollback automatically performed)
A transaction is made permanent by issuing the SQL command ___
COMMIT;
Changes made to the database without COMMIT could be undone using the ___ command
ROLLBACK;
___ are sort of markers that help in splitting a long transaction into smaller units by setting some checkpoints
Savepoints
What is the general syntax for creating a save point and rolling back to said save point?
SAVEPOINT saveName;
ROLLBACK TO saveName;
What are autonomous transactions?
Autonomous transactions are independent transactions that can be called from within another transaction
What must autonomous transactions do before it returns control to the calling transaction?
commit or rollback
When do changes made by the autonomous transaction become visible to other transactions?
Upon commit of the autonomous transactions
Does the autonomous transaction see uncommitted changes made by the main transaction?
No
How do you call an autonomous transaction from within a PL/SQL block?
PRAGMA AUTONOMOUS_TRANSACTION;
In the declare section
What kind of PL/SQL blocks can you declare to be autonomous?
- Stored procedure or function
- Local procedure or function
- Package
- Type method
- Top-level anonymous block
What are Pragmas?
- A compiler directive
- Processed at compile time, not at run time
- Passes info to the compiler
