A transaction is a set of
T-SQL statements that are executed together as a unit like as a single T-SQL
statement. If all of these T-SQL statements executed successfully, then a
transaction is committed and the changes made by T-SQL statements permanently
saved to database. If any of these T-SQL statements within a transaction fail,
then the complete transaction is cancelled/ rolled back.
We use transaction in
that case, when we try to modify more than one tables/views that are related to
one another. Transactions affect SQL Server performance greatly. Since When a
transaction is initiated then it locks all the tables data that are used in the
transaction. Hence during transaction life cycle no one can modify these
tables’ data that are used by the transaction. The reason behind the locking of
the data is to maintain Data Integrity.
Types of
Transactions
1.
Implicit Transaction
Implicit transactions are maintained by SQL Server for
each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE,
DELETE) statements. All these T-SQL statements runs under the implicit
transaction. If there is an error occurs within these statements individually,
SQL Server will roll back the complete statement.
2.
Explicit Transaction
Explicit transactions are defined by programmers. In
Explicit transaction we include the DML statements that need to be execute as a
unit. Since SELECT statements doesn’t modify data. Hence generally we don’t
include Select statement in a transaction.
Transactions
Example
1. CREATE TABLE Department
2. (
3. DeptID int PRIMARY KEY,
4. DeptName varchar(50) NULL,
5. Location varchar(100) NULL,
6. )
7. GO
8. CREATE TABLE Employee
9. (
10. EmpID int PRIMARY KEY,
11. Name varchar(50) NULL,
12. Salary int NULL,
13. Address varchar(100) NULL,
14. DeptID int foreign Key references Department(DeptID)
15.)
1. --Now Insert data
2. INSERT INTO Department(DeptID,DeptName,Location)VALUES(1,'IT','Delhi')
3. GO
4. INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',15000,'Delhi',1)
5. SELECT * FROM Department
6. SELECT * FROM Employee
1. BEGIN TRANSACTION trans
2. BEGIN TRY
3. INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
4. INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
5. IF @@TRANCOUNT > 0
6. BEGIN COMMIT TRANSACTION trans
7. END
8. END TRY
9. BEGIN CATCH
10. print 'Error Occured'
11. IF @@TRANCOUNT > 0
12. BEGIN ROLLBACK TRANSACTION trans
13. END
14.END CATCH
1. --Now Select data to see transaction
affects
2. SELECT * FROM Employee
3. SELECT * FROM Department
1. --Transaction with Save Point BEGIN
TRANSACTION trans
2. BEGIN TRY
3. INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
4. IF @@TRANCOUNT > 0
5. BEGIN SAVE TRANSACTION trans;
6. END
7. INSERT INTO Department(DeptID,DeptName,Location)VALUES(3,'Admin','Delhi')
8. INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
9. IF @@TRANCOUNT > 0
10. BEGIN COMMIT TRANSACTION trans
11. END
12.END TRY
13.BEGIN CATCH
14. print 'Error Occured'
15.IF @@TRANCOUNT > 0
16. BEGIN ROLLBACK TRANSACTION trans
17. END
1. --Now Select data to see transaction
affects
2. SELECT * FROM Employee
3. SELECT * FROM Department