Savepoints divide long transaction into smaller parts - Oracle Transaction

Discussion

• Nirja Shah   -Posted on 22 Sep 15
  - You can declare intermediate markers called savepoints within the context of a transaction.
  
  - Savepoints divide a long transaction into smaller parts.
  
  - Using savepoints, you can arbitrarily mark your work at any point within a long transaction.
  
  - You then have the option later of rolling back work performed before the current point in the transaction but after a declared savepoint within the transaction.
  
  - For example, you can use savepoints throughout a long complex series of updates, so if you make an error, you do not need to resubmit every statement.
  
  - Savepoints are similarly useful in application programs.
  
  - If a procedure contains several functions, then you can create a savepoint before each function begins.
  
  - Then, if a function fails, it is easy to return the data to its state before the function began and re-run the function with revised parameters or perform a recovery action.
  
  - After a rollback to a savepoint, Oracle releases the data locks obtained by rolled back statements.
  
  - Other transactions that were waiting for the previously locked resources can proceed.
  
  - Other transactions that want to update previously locked rows can do so.

➨ Post your comment / Share knowledge