Transaction can be implemented in SQL Server (DB Level) as well as in Dot Net. In Part 1 of the blog series on Transactions, we implemented transactions in front-end code(C# code). However, we can also define transactions in the back-end code like SQLServer database.
These are the commands used to control transactions:
- COMMIT – To save the changes.
- ROLLBACK – To rollback the changes.
- SAVEPOINT – Creates points within groups of transactions in which to ROLLBACK
- SET TRANSACTION – Places a name on a transaction.
Types of Transactions
Implicit transactions are maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements run under the implicit transaction. If an error occurs within these statements individually, SQL Server will roll back the complete statement.
Explicit transactions are defined by programmers. In Explicit transaction we include the DML statements that need to be executed as a unit. Since SELECT statements don’t modify data, hence generally we don’t include Select statement in a transaction.
It controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. There are five types of Isolation levels.
- READ UNCOMMITTED
If any table is updated(insert or update or delete) under a transaction and the same transaction is not completed that is not committed or rolled back then uncommitted values will display (Dirty Read) in select query of “Read Uncommitted” isolation transaction sessions.
- READ COMMITTED
In select query it takes only committed values of table. If any transaction is opened and incomplete on table in other sessions then select query will wait until no transactions are pending on same table.
- REPEATABLE READ
Select query data of table that is used under transaction of isolation level. “Repeatable Read” cannot be modified from any other sessions until transaction is completed.
Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on range lock.
Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold lock on table during the transaction so table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data in case any data modification occurs in other sessions, then existing transaction displays the old data from Tempdb. It is highest level of Isolation but it affects performance.
Now we create StoredProcedure(SP) which implements transaction. The SP executes two inserted SQL statements: one for tblProject table another is tblProjectMember table. It keeps all SQL statements inside BEGIN TRANSACTION block and then commits. If any SQL fails then it goes to CATCH block and rollsback to previous state of database.
CREATE PROCEDURE spAddProject @ProjectID INT, @MemberID INT, @Name VARCHAR(10) AS BEGIN BEGIN TRY BEGIN TRANSACTION; -- Insert record into Project table INSERT INTO tblProject(ProjectID, Name) VALUES(1, 'TestProject'); -- Insert record into ProjectMember table INSERT INTO tblProjectMember(MemberID, ProjectID) VALUES(2, 1); COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; DECLARE @ErrorNumber INT = ERROR_NUMBER(); DECLARE @ErrorLine INT = ERROR_LINE(); DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(); PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10)); PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10)); RAISERROR(@ErrorMessage); END CATCH END;
Returns the number of BEGIN TRANSACTION statements that have occurred on the current transaction. The BEGIN TRANSACTION statement adds @@TRANCOUNT by 1. ROLLBACK TRANSACTION l @@TRANCOUNT to 0. COMMIT TRANSACTION or COMMIT WORK subtracts @@TRANCOUNT by 1.
call the Stored procedure from code like below.
@@TRANCOUNT: Returns the number of BEGIN TRANSACTION statements that have occurred on the current transaction. The BEGIN TRANSACTION statement adds @@TRANCOUNT by 1. ROLLBACK TRANSACTION l @@TRANCOUNT to 0. COMMIT TRANSACTION or COMMIT WORK subtracts @@TRANCOUNT by 1. call the Stored procedure from code like below.