DDL Part II

Outline

•Overview of Database Transactions

•Commit

•Savepoint

•Rollback

Database Transactions

A database transaction consists of one of the following:

•DML statements which constitute one consistent change to the data

•One DDL statement

•One DCL statement

Database Transactions

•Begin when the first DML SQL statement is executed

•End with one of the following events:

    –A COMMIT or ROLLBACK statement is issued

    –A DDL or DCL statement executes (automatic commit)

    –The system crashes

Advantages of COMMIT and ROLLBACK Statements

With COMMIT and ROLLBACK statements, you can:

•Ensure data consistency

•Preview data changes before making changes permanent

•Group logically related operations

Controling Transactions Diagram

Rolling Back Changes to a Savepoint

•You can create a marker in the current transaction by using the SAVEPOINT statement which divides the transaction into smaller sections.

•You can then discard pending changes up to that marker by using the ROLLBACK TO SAVEPOINT statement.

•If you create a second savepoint with the same name as an earlier savepoint, the earlier savepoint is deleted.

Rolling Back Changes to a Marker

•Create a marker in a current transaction by using the SAVEPOINT statement.

•Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement.

EX.

UPDATE...

SAVEPOINT update_done;

Savepoint created.

INSERT...

ROLLBACK TO update_done;

Rollback complete.

State of the Data
Before COMMIT or ROLLBACK

•Every data change made during the transaction is temporary until the transaction is committed

•The current user can review the results of the DML operations by using the SELECT statement.

•Other users cannot view the results of the DML statements by the current user.

•The affected rows are locked; other users cannot change the data within the affected rows.

State of the Data after COMMIT

•Data changes are made permanent in the database.

•The previous state of the data is permanently lost.

•All users can view the results.

•Locks on the affected rows are released; those rows are available for other users to manipulate.

•All savepoints are erased.

Committing Data

•Make the changes.

Ex.

DELETE FROM employees

WHERE  employee_id = 99999;

1 row deleted.

INSERT INTO departments

VALUES (290, 'Corporate Tax', NULL, 1700);

1 row inserted.

•Commit the changes.

COMMIT;

Commit complete.

State of the Data After ROLLBACK

Discard all pending changes by using the ROLLBACK statement:

•Data changes are undone.

•Previous state of the data is restored.

•Locks on the affected rows are released.

Ex.

DELETE FROM copy_emp;

22 rows deleted.

ROLLBACK;

Rollback complete.

Statement-Level Rollback

•If a single DML statement fails during execution, only that statement is rolled back.

•All other changes are retained.

•The user should terminate transactions explicitly by executing a COMMIT or ROLLBACK statement.

Summary

In this lesson, you should have learned how to use DML

statements and control transactions.

Statement     Description

INSERT    Adds a new row to the table

UPDATE    Modifies existing rows in the table

DELETE    Removes existing rows from the table

MERGE     Conditionally inserts or updates data in a table

COMMIT    Makes all pending changes permanent

SAVEPOINT Is used to rollback to the savepoint marker

ROLLBACK  Discards all pending data changes