Oracle Transaction | Transaction control language - oracle tutorial - sql tutorial

What is Oracle Transaction ?

• Transaction Control statements (TCL) are used to manage the changes made by DML statements.

• DML statements affect records in a table.
• These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.

• SELECT - select records from a table
• INSERT - insert new records
• UPDATE - update/Modify existing records
• DELETE - delete existing records
• DML (Data Manipulation Language) DML statements affect records in a table.
• These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.

• SELECT - select records from a table
• INSERT - insert new records
• UPDATE - update/Modify existing records
• DELETE - delete existing records
• It allows statements to be grouped together into logical transactions.

• Transactions have the following four standard properties, usually referred to by the acronym ACID.

• ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.

• Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
• Consistency − ensures that the database properly changes states upon a successfully committed transaction.
• Isolation − enables transactions to operate independently of and transparent to each other.
• Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.

• Make changes done in transaction permanent.

• Following is an example which would delete those records from the table which have age = 32 and then COMMIT the changes in the database.

• Thus, two rows from the table would be deleted and the SELECT statement would produce the following result.

• To rollback the changes done in a transaction give rollback statement.
• Rollback restore the state of the database to the last commit point.

• Following is an example, which would delete those records from the table which have the age = 32 and then ROLLBACK the changes in the database.

• Thus, the delete operation would not impact the table and the SELECT statement would produce the following result.

• As changes are made in a transaction, we can create SAVEPOINTs to mark different points within the transaction.
• If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.
• The syntax for a SAVEPOINT command,

• This command serves only in the creation of a SAVEPOINT among all the transactional statements.
• The ROLLBACK command is used to undo a group of transactions.
• The syntax for rolling back to a SAVEPOINT command,

• Following is an example where you plan to delete the three different records from the Employees table.
• You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.

• The following code block contains the series of operations.

• Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2.
• Because SP2 was created after the first deletion, the last two deletions are undone −

• Notice that only the first deletion took place since you rolled back to SP2.