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.
 transaction control language

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.

DML statements include the following:

  • 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.

DML statements include the following:

  • 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.

Properties of Transactions

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

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

  • 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.

TCL Statements available in Oracle are,

oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

COMMIT:

  • Make changes done in transaction permanent.

Syntax:

COMMIT;
click below button to copy the code. By - oracle tutorial - team

Example

Table_Employees

ID NAME AGE ADDRESS SALARY
1 Arnold 32 Ahmedabad 12000.00
2 Christopher 25 Delhi 11500.00
3 Joseph 23 Kota 12000.00
4 Chaitali 25 Mumbai 16500.00
5 Hardik 27 Bhopal 18500.00
6 Komal 22 MP 14500.00
7 Muffy 32 Indore 20000.00
  • Following is an example which would delete those records from the table which have age = 32 and then COMMIT the changes in the database.
SQL> DELETE FROM Employees
WHERE AGE = 32;
SQL> COMMIT;
click below button to copy the code. By - oracle tutorial - team
  • Thus, two rows from the table would be deleted and the SELECT statement would produce the following result.

Output:

ID NAME AGE ADDRESS SALARY
2 Christopher 25 Delhi 11500.00
3 Joseph 23 Kota 12000.00
4 Chaitali 25 Mumbai 16500.00
5 Hardik 27 Bhopal 18500.00
6 Komal 22 MP 14500.00

ROLLBACK :

  • To rollback the changes done in a transaction give rollback statement.
  • Rollback restore the state of the database to the last commit point.
Oracle transactions - read uncommited no lock-read commited-repeatable-read-serailizable-holdlock

Oracle transactions - read uncommited no lock-read commited-repeatable read-serailizable holdlock

oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

Syntax:

ROLLBACK;
click below button to copy the code. By - oracle tutorial - team

Example

Table_Employees

ID NAME AGE ADDRESS SALARY
1 Arnold 32 Ahmedabad 12000.00
2 Christopher 25 Delhi 11500.00
3 Joseph 23 Kota 12000.00
4 Chaitali 25 Mumbai 16500.00
5 Hardik 27 Bhopal 18500.00
6 Komal 22 MP 14500.00
7 Muffy 32 Indore 20000.00
  • 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.
SQL> DELETE FROM Employees
WHERE AGE = 32;
SQL> ROLLBACK;
click below button to copy the code. By - oracle tutorial - team
  • Thus, the delete operation would not impact the table and the SELECT statement would produce the following result.

Output:

ID NAME AGE ADDRESS SALARY
1 Arnold 32 Ahmedabad 12000.00
2 Christopher 25 Delhi 11500.00
3 Joseph 23 Kota 12000.00
4 Chaitali 25 Mumbai 16500.00
5 Hardik 27 Bhopal 18500.00
6 Komal 22 MP 14500.00
7 Muffy 32 Indore 20000.00

SAVEPOINT :

  • 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,
SAVEPOINT SAVEPOINT_NAME;
click below button to copy the code. By - oracle tutorial - team
  • 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,
ROLLBACK TO SAVEPOINT_NAME;
click below button to copy the code. By - oracle tutorial - team
  • 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.

Example

oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

Table_Employees

ID NAME AGE ADDRESS SALARY
1 Arnold 32 Ahmedabad 12000.00
2 Christopher 25 Delhi 11500.00
3 Joseph 23 Kota 12000.00
4 Chaitali 25 Mumbai 16500.00
5 Hardik 27 Bhopal 18500.00
6 Komal 22 MP 14500.00
7 Muffy 32 Indore 20000.00
  • The following code block contains the series of operations.
SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM Employees WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM Employees WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM Employees WHERE ID=3;
1 row deleted.
click below button to copy the code. By - oracle tutorial - team
  • 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 −
SQL> ROLLBACK TO SP2;
Rollback complete.
click below button to copy the code. By - oracle tutorial - team
  • Notice that only the first deletion took place since you rolled back to SP2.
SQL> SELECT * FROM Employees;
click below button to copy the code. By - oracle tutorial - team
ID NAME AGE ADDRESS SALARY
2 Christopher 25 Delhi 11500.00
3 Joseph 23 Kota 12000.00
4 Chaitali 25 Mumbai 16500.00
5 Hardik 27 Bhopal 18500.00
6 Komal 22 MP 14500.00
7 Muffy 32 Indore 20000.00

6 rows selected.


This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning

Related Searches to Oracle transaction | Transaction control language