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.
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
- READ COMMITTED
- READ UNCOMMITTED
- REPEATABLE READ
- SERIALIZABLE
- SNAPSHOT
-
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
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
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;
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
