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;
