Committing and Rolling Back a Transaction

A database transaction is a group of SQL statements for a logical unit of work.

An example of a database transaction is a money transfer. One UPDATE statement would subtract from one account, and another UPDATE would add money to the other account. Both the subtraction and the addition must be recorded in the database; otherwise, money will be lost. If there is a problem with the money transfer, then the subtraction and addition must both be undone.

Using the COMMIT statement to permanently record the results made by SQL statements in a transaction.


SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    SAL NUMBER(7, 2),
  5                    DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>

Using the ROLLBACK statement to undo the results and reset all the rows back to what they were originally.


SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    SAL NUMBER(7, 2),
  5                    DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);

1 row created.

SQL>
SQL> ROLLBACK;

Rollback complete.

SQL>

Home »
Oracle »
Table »

Database Transactions: