Transaction in SQL Server gives the guarantee to complete the task.

We use the transaction when we want to either complete the whole task in one go with guarantee of task completion or if the transaction get failed there all process should roll back.


Below image will explain the flow of transaction.


sql transacton flow digram in sql server

Transaction having four properties, it is also called as ACID properties.
1) Atomicity : In transaction may have multiple statements, but it consider as single statement. So if it is failed or pass treat them as single statement.
2) Consistency : Transaction should consistence it mean transaction either get fully successfully or fully get fail.
3) Isolation : If data get modify by one transaction must be separate (Isolation) from the data modifications made by all other transactions. If let's consider there are two transaction are going on same data then unless first transaction complete then second transaction should wait.
4) Durability : It is talking about commit data, if data get committed then it should not be undone.



Syntax for transaction

BEGIN TRAN

--SQL Statements

IF --(any error comes)
ROLLBACK TRAN

COMMIT TRAN

1) Example of SQL Transaction

Consider below example we have use the transaction. In below example what exactly we are trying to do is
1) We start transaction
2) Write two update statement
3) After update student name we verify that student name get updated properly
4) After update student name we update student city.
5) We commit transaction
6) If error occur we rollback transaction.

Example

SELECT * FROM tblStudentDetails WHERE ID = 1

BEGIN TRAN

      BEGIN TRY
UPDATE tblStudentDetails SET StudentName = 'Geeta' WHERE ID = 1
SELECT * FROM tblStudentDetails WHERE ID = 1

UPDATE tblStudentDetails SET City = 'Thiruvananthapuram' WHERE ID = 1

      COMMIT TRAN
      END TRY
      BEGIN CATCH
SELECT ERROR_NUMBER () AS ErrorNumber, ERROR_MESSAGE () AS ErrorMessage
ROLLBACK TRAN

      END CATCH

SELECT * FROM tblStudentDetails WHERE ID = 1

Output

rollback transaction in sql server example


What happen exactly in above example.
1) Select the record from student detail where id is 1.
2) Update the student name as Geeta where id is 1.
3) Select the record from student detail where id is 1 and verify the name updated properly.
4) Update the city of student as Thiruvananthapuram, but this statement gives an error because size of City column is 10 character and we are trying to add more than 10 character in it. So transaction get fail but we update the student name , it mean we update partial data of student.
5) We select the error message.
6) Rollback the transaction on error occur.
7) Select the record to verify that record partially update or not. But it not update student name because as transaction consider all statement as one statement and it rollback all updated data. It is one of the ACID property.



Correct the above example.
If we give the city name less than 10 character then transaction get commited.


Example

SELECT * FROM tblStudentDetails WHERE ID = 1

BEGIN TRAN

      BEGIN TRY
UPDATE tblStudentDetails SET StudentName = 'Geeta' WHERE ID = 1
SELECT * FROM tblStudentDetails WHERE ID = 1

UPDATE tblStudentDetails SET City = 'Thiruvananthapuram' WHERE ID = 1

      COMMIT TRAN
      END TRY
      BEGIN CATCH
SELECT ERROR_NUMBER () AS ErrorNumber, ERROR_MESSAGE () AS ErrorMessage
ROLLBACK TRAN

      END CATCH

SELECT * FROM tblStudentDetails WHERE ID = 1

Output

commit transaction in sql server example