Free Web Development Online Tutorials, Learn to Code
Learn Transaction in SQL Server, SQL Transaction COMMIT and Rollback

SQL Server Transaction Example


What is Transaction in SQL?
When a query or a set of query gets executed in SQL server and all consolidated into a single task, that logical task is called Transaction.

When any query inside the transaction gets failed the entire transaction gets failed. And when a transaction fail if there are any single or partial query which was executed successfully, will also rollback, in transaction either all queries will be successful or all will be failed.

SQL Transactions ACID Properties

Transactions have following standard properties, which are known as ACID Properties

  • Atomicity

    this makes sure that any operation within the work unit is completed successfully. Else transaction is aborted and all previous successful operations are rolled back.

  • Consistency

    this makes sure that all changes in database reflected successfully after transaction committed

  • Isolation

    There are different isolation level in SQL Server

  • Durability

    this makes sure that result of committed transaction remain unaffected if anything goes wrong in system

SQL Transaction Isolation Level

  • READ COMMITTED
  • READ UNCOMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
  • SNAPSHOT

SQL Command in Transaction

  • COMMIT

    Save all changes

  • ROLLBACK

    Roll back all changes

  • SAVEPOINT

    SAVEPOINT command is used to save a transaction temporarily, so that if require you can rollback the transaction to that point.

  • SET TRANSACTION
  • Begin TRANSACTION

How to Implement Transaction in SQL

Now you have read what transaction in SQL Server, now we see some example of how to implement transaction in SQL Server.

Now here we write two sql insert statement with in a transaction, and to check if transaction is working properly or not we will write one insert with correct data, and the other one with incorrect data.

Expected result transaction should get rolled back and no insertion should happen in database table

Begin Try
    Begin Transaction 
	
    // Here you write SQL codes
    Commit
End Try

Begin Catch 
    // implement error handling 
    Rollback
End Catch

sql transaction

in above data insertion example we had only one data incorrect, and that causes transaction aborted

INSERT INTO [tbTeacher1]([FullName],[Address],[contactnumber],[updatedon])
VALUES ('Ajit Mukherji','Kormongola, Bangalore', '9932051451','wrong datatype')

If everything goes right in your transaction then use Commit, means all data will be saved, else Rollback, no data will be saved.

If you are using SAVEPOINT command

// after data manipulation
SAVEPOINT savepoint_name;
//in case you want to rollback till that point ROLLBACK TO savepoint_name;

Comment
Name
Email
Website
Subscribe