About Transaction and Distribution Transaction

About Transaction – A C I D

What is SQL Server transaction?

A block of or single statements execute in the database in sequence ways perform all the statement   or none of them. A transaction must satisfy four properties

-          Atomicity

Transactions either commit or roll back  

-          Consistency

It should maintain the state data from start of transaction to the end of transaction.

-          Isolation

It should isolate the modification by the two transactions.

-          Durability

If any disaster occurred in the database before the transaction it rolled back all data otherwise it will store the data in place.

Begin Transaction | Tran trans1

-          Start point of transaction.

Commit Transaction trans1

-          Store the data permanently in the database.

Rollback Transaction trans1

-          Rolled back all the data from begin transaction statements.

Save Transaction trans1

-          Store the some part of transaction data in the database.

BEGIN TRAN T1;

UPDATE table1 ...;

BEGIN TRAN M2 WITH MARK;

UPDATE table2 ...;

SELECT * from table1;

COMMIT TRAN M2;

UPDATE table3 ...;

COMMIT TRAN T1;

MSDTC – Microsoft distribution transaction coordinator

Whenever do the operation on the multiple server that time need to use the distrinbuted trancation.

BEGIN DISTRIBUTED TRANSACTION;

-- Delete candidate from local instance.

DELETE AdventureWorks.HumanResources.JobCandidate

    WHERE JobCandidateID = 13;

-- Delete candidate from remote instance.

DELETE RemoteServer.AdventureWorks.HumanResources.JobCandidate

    WHERE JobCandidateID = 13;

COMMIT TRANSACTION;