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.
Transactions have following standard properties, which are known as ACID Properties (Atomicity, Consistency, Isolation, Durability)
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.
this makes sure that all changes in database reflected successfully after transaction committed
There are different isolation level in SQL Server
this makes sure that result of committed transaction remain unaffected if anything goes wrong in system
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.
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
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;
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