DataBase Transaction

Introduction

A transaction begind when the first DML statement is encountered and ends when one of the following occurs:

1. A COMMIT or ROLLBACK statement is issued
2. A DDL statement such a CREATE is issued
3. A DCL statement is issued

There are two Types of Transaction Control Statements:

1. Explicit Transaction Control Statements
2. Implicit Transaction Control Statements

Explicit Transaction Control Statements :

 You can Control the logic of transactions by using the COMMIT,SAVEPOINT and ROLLBACK statements.

COMMIT:       

Satae of the Date after COMMIT:

 Ends the current transaction by making all Pending data changes Permanent.

a)    The previous state of the data is permanently lost

b)    All users can view the results of the transaction

c)     The locks on the eaffected rows are released

d)    All save Points are erased

Example:

  Delete from emp where empid=101;

     1 row deleted.

  Insert into emp values(101,’smile’);

     1 row inserted.

   Commit the changes:

   Commit;

    Commit Complete.

SAVEPOINT:

Marks a savepoint within the current transaction.

ROLLBACK:

a)    It ends the current transaction by discarding all pending data changes

b)    Previous state of the data is restored.

c)     Locks on the affected rows are released.

Example:

Delete from emp;

10 rows deleted.

ROLLBACK;

Rollback Complete.

Delete from emp where empid=101;

1 row deleted.

Implicit Transaction Control:

  An automatic commit occurs under the following circumstances:

a)    DDL statement is issued.(Create ,Alter,Drop,Rename,Truncate)

b)    DCL statement is issued.(Grant and Revoke)

c)     Normal exit without issuing COMMIT or ROLLBACK Statements

  An automatic Rollback occurs under an abnormal termination.

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.