Free Web Development Online Tutorials, Learn to Code
Learn SQL Database Development, MS SQL Tutorial Online

Using 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.

ACID Properties of Transactions

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

Property Description
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

Isolation Level of Transaction


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.

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

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;

Transaction in SQL Database

Implement Transaction in SQL Server