About DML Statements and Transactions

Data manipulation language (DML) statements add, change, and delete database table data. A transaction is a sequence of one or more SQL statements that Oracle AI Database treats as a unit: either all of the statements are performed, or none of them are.

About Transaction Control Statements

A transaction is a sequence of one or more SQL statements that Oracle AI Database treats as a unit: either all of the statements are performed, or none of them are. You need transactions to model business processes that require that several operations be performed as a unit.

For example, when a manager leaves the company, a row must be inserted into the JOB_HISTORY table to show when the manager left, and for every employee who reports to that manager, the value of MANAGER_ID must be updated in the EMPLOYEES table. To model this process in an application, you must group the INSERT and UPDATE statements into a single transaction.

The basic transaction control statements are:

• SAVEPOINT, which marks a savepoint in a transaction—a point to which you can later roll back. Savepoints are optional, and a transaction can have multiple savepoints.

• COMMIT, which ends the current transaction, makes its changes permanent, erases its savepoints, and releases its locks.

• ROLLBACK, which rolls back (undoes) either the entire current transaction or only the changes made after the specified savepoint.

In the SQL*Plus environment, you can enter a transaction control statement after the SQL> prompt.

In the SQL Developer environment, you can enter a transaction control statement in the Worksheet. SQL Developer also has Commit Changes and Rollback Changes icons, which are explained in “Committing Transactions” and “Rolling Back Transactions”.

Caution:

If you do not explicitly commit a transaction, and the program terminates abnormally, then the database automatically rolls back the last uncommitted transaction.

Oracle recommends that you explicitly end transactions in application programs, by either committing them or rolling them back.

See Also:

• Oracle AI Database Concepts for more information about transaction management

• Oracle AI Database SQL Language Reference for more information about transaction control statements

Committing Transactions

Committing a transaction makes its changes permanent, erases its savepoints, and releases its locks.

To explicitly commit a transaction, use either the COMMIT statement or (in the SQL Developer environment) the Commit Changes icon.

Note: Oracle AI Database issues an implicit COMMIT statement before and after any data definition language (DDL) statement. For information about DDL statements, see “About Data Definition Language (DDL) Statements”.

Before you commit a transaction:

• Your changes are visible to you, but not to other users of the database instance.

• Your changes are not final—you can undo them with a ROLLBACK statement.

After you commit a transaction:

• Your changes are visible to other users, and to their statements that run after you commit your transaction.

• Your changes are final—you cannot undo them with a ROLLBACK statement.

Example 3-1 adds one row to the REGIONS table (a very simple transaction), checks the result, and then commits the transaction.

Example 3-1 Committing a Transaction

Before transaction:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

4 rows selected.

Transaction (add row to table):

INSERT INTO regions (region_id, region_name) VALUES (5, 'Africa');

Result:

1 row created.

Check that row was added:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Commit transaction:

COMMIT;

Result:

Commit complete.

See Also: Oracle AI Database SQL Language Reference for information about the COMMIT statement

Rolling Back Transactions

Rolling back a transaction undoes its changes. You can roll back the entire current transaction, or you can roll it back only to a specified savepoint.

To roll back the current transaction only to a specified savepoint, you must use the ROLLBACK statement with the TO SAVEPOINT clause.

To roll back the entire current transaction, use either the ROLLBACK statement without the TO SAVEPOINT clause, or (in the SQL Developer environment) the Rollback Changes icon.

Rolling back the entire current transaction:

• Ends the transaction

• Reverses all of its changes

• Erases all of its savepoints

• Releases any transaction locks

Rolling back the current transaction only to the specified savepoint:

• Does not end the transaction

• Reverses only the changes made after the specified savepoint

• Erases only the savepoints set after the specified savepoint (excluding the specified savepoint itself)

• Releases all table and row locks acquired after the specified savepoint

  Other transactions that have requested access to rows locked after the specified savepoint must continue to wait until the transaction is either committed or rolled back. Other transactions that have not requested the rows can request and access the rows immediately.

To see the effect of a rollback in SQL Developer, you might have to click the Refresh icon.

As a result of Example 3-1, the REGIONS table has a region called ‘Middle East and Africa’ and a region called ‘Africa’. Example 3-2 corrects this problem (a very simple transaction) and checks the change, but then rolls back the transaction and checks the rollback.

Example 3-2 Rolling Back an Entire Transaction

Before transaction:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Transaction (change table):

UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

Result:

1 row updated.

Check change:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

5 rows selected.

Roll back transaction:

ROLLBACK;

Result:

Rollback complete.

Check rollback:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

See Also: Oracle AI Database SQL Language Reference for information about the ROLLBACK statement

Setting Savepoints in Transactions

The SAVEPOINT statement marks a savepoint in a transaction-a point to which you can later roll back. Savepoints are optional, and a transaction can have multiple savepoints. Example 3-3 does a transaction that includes several DML statements and several savepoints, and then rolls back the transaction to one savepoint, undoing only the changes made after that savepoint.

Example 3-3 Rolling Back a Transaction to a Savepoint

Check REGIONS table before transaction:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Check countries in region 4 before transaction:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;

Result:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          4
Israel                                   IL          4
Kuwait                                   KW          4
Nigeria                                  NG          4
Zambia                                   ZM          4
Zimbabwe                                 ZW          4

6 rows selected.

Check countries in region 5 before transaction:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;

Result:

no rows selected

Transaction, with several savepoints:

UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'ZM';
SAVEPOINT zambia;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'NG';
SAVEPOINT nigeria;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'ZW';
SAVEPOINT zimbabwe;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'EG';
SAVEPOINT egypt;

Check REGIONS table after transaction:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

5 rows selected.

Check countries in region 4 after transaction:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;

Result:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Israel                                   IL          4
Kuwait                                   KW          4

2 rows selected.

Check countries in region 5 after transaction:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;

Result:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          5
Nigeria                                  NG          5
Zambia                                   ZM          5
Zimbabwe                                 ZW          5

4 rows selected.

ROLLBACK TO SAVEPOINT nigeria;

Check REGIONS table after rollback:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

5 rows selected.

Check countries in region 4 after rollback:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;

Result:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          4
Israel                                   IL          4
Kuwait                                   KW          4
Zimbabwe                                 ZW          4

4 rows selected.

Check countries in region 5 after rollback:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;

Result:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Nigeria                                  NG          5
Zambia                                   ZM          5

2 rows selected.

See Also: Oracle AI Database SQL Language Reference for information about the SAVEPOINT statement