DataBase Transaction
Introduction
Introduction goes here
DataBase Transaction?
A transaction begind when the first DML statement is encountered and ends when one of the following occurs:
- A COMMIT or ROLLBACK statement is issued
- A DDL statement such a CREATE is issued
- A DCL statement is issued
There are two Types of Transaction Control Statements:
- Explicit Transaction Control Statements
- 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.