DataBase Transaction

Syedshakeer
Posted by in Sql Server category on for Beginner level | Views : 3744 red flag

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:

 

  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.




 
Page copy protected against web site content infringement by Copyscape

About the Author

Syedshakeer
Full Name: Syed Shakeer Hussiain P
Member Level:
Member Status: Member
Member Since: 2/5/2009 3:12:18 AM
Country: India
Syed Shakeer Hussain
http://www.dotnetfunda.com
Shakeer Hussain has completed his Master of Computer Applications degree from Deccan College of engg and technology of Osmania University.He is a MVM of www.dotnetspider.com.He has good experience in the areas of ASP.NET, C#.NET, VB.NET, SQL SERVER 2000/2005 and Windows Mobile. He has worked in Windows Mobile,Web Applicatin and ERP projects.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)