In my 2-part blog series I will be explaining how transactions can be implemented in .Net and SQL servers respectively. In this blog (Part 1), I will go into details on how transactions can be implemented in DotNet.
Introduction
Most applications deal with back-end databases. For instance, you are supposed to insert data into master and childtables in the SQL Server. While doing this, if you inserted one row into the master table and then some error occurred in that task, child data is not inserted and you must rollback the master table data also; otherwise inconsistency in data will happen. In such situations, transaction plays a vital role to make sure data operations that occur, happen successfully.
What is `Transaction’?
A transaction is a single unit of work which means either ALL or NONE. If a transaction is successful, all of the data operations are committed and become a durable part of the database. If a transaction encounters errors/exceptions and must be canceled or rolled back, then all of the data modifications/operations need to be removed.
Properties of Transaction
Transaction has the four standard properties, called by the acronym as ACID.
- Atomicity
Ensures that all operations within the work unit are completed successfully; If the transaction fails then operations are rolled back to their previous state. - Consistency
Ensures that the database properly changes states upon a successfully committed transaction. - Isolation
Enables transactions to operate independently of and transparent to each other. - Durability
Ensures that a committed transaction persists in case of a system failure.
Example:
Create two tables (Project Member, Project). In Project Member table, column ProjectID is a foreign key to Project table’s ProejctID column.
Here is the sample code to implement transaction in ADO.NET.
In the above code , it executes two SQL queries: first it inserts record to project, second it inserts record to projectMember table. First SQL statement executes properly but second sql statement throws exception because we are inserting projectId as 2 which is not present in project table. Transaction dictates either complete success or failure, there is nothing in between. So to avoid the above problem, we will use Transaction to make everything perfect. Here it uses SqlTransaction class and creates object by calling BeginTransaction() method of SqlConnection class. Transaction starts here. If everything goes fine then commit (save into database) it otherwise rollback (delete inserted record in scope in the transaction) it.
Using TransactionScope
It is present in namespace System.Transactions.TransactionScope
It has 3 main properties
- Isolation Level
It defines the locking mechanism to read data in another transaction. Available options are Read UnCommitted, Read Committed, Repeatable Read, Serializable. Default is Serializable. - Timeout
How much time transaction object will wait to be completed. SqlCommand Timeout is different than Transaction Timeout. SqlCommand Timeout defines how much time the SqlCommand object will wait for a database operation to be completed. Available options are 10 minutes. Default is 1 minute. - TransactionScopeOption
It is an enum. Following are the options.
| Option | Description |
| Disable | This component does not participate in a transaction. This is the default value. |
| Not Supported | This component runs outside the context of a transaction. |
| Required | It is the default value for TransactionScope. If any already exists then it will join with that transaction otherwise create new one. |
| Requires New | When this option is selected a new transaction is always created. This transaction is independent with its outer transaction. |
| Suppress | When this option is selected, no transaction will be created. Even if is already there. |
You can set default time out in web.config like.
- <system.transactions>
- <defaultSettings timeout=”30″/>
- <machineSettings maxTimeout=”1200″/>
- </system.transactions>
System.Transactions is available by default; we need to add the reference.
In the below code sample, it creates TransactionScope class object and defines SQL queries to add records to Project table, ProjectMember table. Once everything is fine, it calls Complete() to commit the data. If exception occurs it rollsback to previous state.
Difference between TransactionScope and BeginTranaction()
- TransactionScope is usually a better choice because it allows you to nest calls to other methods that might require a transaction without you having to pass the transaction state around.
- With TransactionScope, as long as the scope exists, it will handle everything that registers with the current Transaction on the thread, making your code cleaner, and more maintainable.
- TransactionScope uses MS DTC(Microsoft Distributed Transaction Coordinator) for transaction management.
- Due to its ease of use and efficiency, it is recommended that you use the TransactionScope class when developing a transaction application.
