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 SQL 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, Consistency, Isolation, Durability)

  • 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
default isolation level in sql server is "Read Committed", means if we don’t specify isolation level in our sql query, the transaction object will consider “Read Committed” is the default isolation level.
SQL Command in Transaction

    Save all changes


    Roll back all changes


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


Implement Transaction in SQL Database

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
End Try
Begin Catch 
    // implement error handling 
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;

XACT_STATE in SQL Transaction

Sometimes we come across situation where the error is so minor, we can consider further whether to commit or rollback the transaction.

In such situation we can use XACT_STATE function, which return small integer value, indicates whether the transaction is capable of being committed.

We can implement xact_state() state in sql transaction in following way.

-- Test whether the transaction is uncommittable.  
IF (XACT_STATE()) = -1  
-- take action 1
-- Test whether the transaction is active and valid.  
IF (XACT_STATE()) = 1  
-- take action 2

Here you can learn more about how to use XACT_STATE in Sql transaction.

You may be interested in following posts

Hire SQL Developer
Transaction in SQL Database
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.

Implement Transaction in SQL Server
MS SQL Examples | Join MS SQL Course