SQL Transactions - Commit, Rollback, and Savepoint

SQL Transaction Control Language (TCL) commands are used to manage database transaction. SQL transaction command use with DML statement for INSERT, UPDATE and DELETE.

DML statement are store into SQL buffer until you execute Transaction commands. Once you execute transaction commands its store permanent to a database.

SQL COMMIT

SQL COMMIT command save new changes store into database.

Syntax

COMMIT;

Example

SQL> COMMIT;

Commit complete.

SQL SAVEPOINT

SQL SAVEPOINT command create new save point. SAVEPOINT command save the current point with the unique name in the processing of a transaction.

Syntax

SAVEPOINT savepoint_name;

Example

SQL> CREATE TABLE emp_data (
    no NUMBER(3), 
    name VARCHAR(50), 
    code VARCHAR(12)
    );

Table created.

SQL> SAVEPOINT table_create;

Savepoint created.

SQL> insert into emp_data VALUES(1,'Opal', 'e1401');

1 row created.

SQL> SAVEPOINT insert_1;

Savepoint created.

SQL> insert into emp_data VALUES(2,'Becca', 'e1402');

1 row created.

SQL> SAVEPOINT insert_2;

Savepoint created.

SQL> SELECT * FROM emp_data;

        NO NAME                                       CODE
---------- ------------------------------------------ ------------
         1 Opal                                       e1401
         2 Becca                                      e1402

SQL ROLLBACK

SQL ROLLBACK command execute at the end of current transaction and undo/undone any changes made since the begin transaction.

Syntax

ROLLBACK [To SAVEPOINT_NAME];

Example

Above example we are create 3 SAVEPOINT table_create, insert_1 and insert_2. Now we are rollback to insert_1 SAVEPOINT.

SQL> ROLLBACK TO insert_1;

Rollback complete.

SQL> SELECT * FROM emp_data;

        NO NAME                                       CODE
---------- ------------------------------------------ ------------
         1 Opal                                       e1401