ADO.NET Connection Transaction
Lets talk about, how in various scenarios we can use TransactionScope with various options for managing real life transactions using ADO.NET.
One of the important aspect for any business application is Transaction management. Each and every large scale development framework provides a component for managing transactions. .NET Framework is a large development framework and it also provides its own transaction management component.
From.NET Framework 2.0 onwards TransactionScope class is available in the System.Transactions assembly. This class provides a transactional framework with the help of which any .NET developer can write transactional code without having much knowledge. To resolve transactional issues like deadlocks, timeouts, etc., you must know each and every concept directly/indirectly related to a transaction. There is no alternative. So the concepts of a transaction and its related components need to be clear.
In a database transaction we can say, a series of MDL operations like insert/update/delete, execute as a whole. All statements either successfully execute, or will fail each and every statement, so that the database is in consistent mode. Database transactions actually represent a database state change in an accurate way.
There are two types of Transactions based on the boundary;
Local Transaction
Distributed Transaction
To manage local transactions, a transaction where a series of DML statements execute as a whole on a single database. It is actually a single phase transaction handled by a database directly. All transactions are started by System.Transactions are handled directly by this component.
However, if it finds the transaction nature is distributed based on some predefined rules it has a fallback transaction to the distributed transaction. If a transaction fails then the affected data sources will be rolled back. In System.Transactions, MSDTC (Microsoft Distributed Transaction Coordinator) manages distributed transactions. It implements a two phase commit protocol. A distributed transaction is much slower than a local transaction. The transaction object automatically escalates a local transaction to a distributed transaction when it understands that a distributed transaction is needed. The developer can not do anything here.
We know that in a distributed transaction, several sites are involved. Each site has two components:
Transaction Manager
Transaction Coordinator
1. Transaction Manager: Maintains a log and uses that log if recovery is needed. It controls the whole transaction by initiating and completing, and managing the durability and atomicity of a transaction. It also coordinates transactions across one or more resources. There are two types of transaction managers.
Local Transaction Manager: Coordinates transaction over a single resource only.
Gloabl Transaction Manager: Coordinates transaction over multiple resources.
2. Transaction Coordinator: Starting the execution of transactions that originate at the site. Distributes subtransactions at appropriate sites so that they can execute in those sites. Coordinates each transaction of each site. As a result the transaction is committed or rolled back to all sites.
Usage:
Transaction which is tied directly with a database connection (SqlConnection) is called Connection Transaction. SqlTransaction
There ate few transaction related topics you mush aware of;
Transaction Properties
There are four important properties for a transaction. We call them ACID properties. They are:
A-Atomic
C-Consistent
I-Isolation
D-Durable
Atomic: If all parts of the transaction individually succeed then data will be committed and the database will be changed. If any single part of a transaction fails then all parts of the transaction will fail and the database will remain unchanged. Part of the transaction might fail for various reasons like business rule violation, power failure, system crash, hardware failure, etc.
Consistent: Transaction will change the database from one valid state to another valid state following various database rules like various data integrity constraints (primary/unique key, check/not null constraint, referential integrity with valid reference, cascading rules ) etc.
Isolation: One transaction will be hidden from another transaction. In another way we can say, one a transaction will not affect an other transaction if both work concurrently.
Durability: After a transaction is successfully completed (committed to the database), changed data will not be lost in any situation like system failure, database crash, hardware failure, power failure etc.
Transaction Isolation Levels
A Transaction Management System introduces a locking mechanism. With the help of this mechanism one transaction is isolated from another. The locking policy behaves differently based on the Isolation level set for each transaction. There are four very important isolation levels in .NET transaction scope. These are:
Serializable: Highest level of isolation. It locks data exclusively when read and write occurs. It acquires range locks so that phantom rows are not created.
Repeatable Read: Second highest level of isolation. Same as serializable except it does not acquire range locks so phantom rows may be created.
Read Committed: It allow shared locks and read only committed data. That means never read changed data that are in the middle of any transaction.
Read Un-Committed: It is the lowest level of Isolation. It allows dirty read.
Lets start understanding TransactionScope and its usage pattern:
It is very important to know about the default properties of the TransactionScope object. Why? Because many times we create and use this object without configuring anything.
Three very important properties are:
IsolationLevel
Timeout
TransactionScopeOptions
IsolationLevel - It defines the locking mechanism and policy to read data inside another transaction. Serializable (Default), Read Committed, Read Un Committed, Repeatable Read
Timeout - How much time object will wait for a transaction to be completed. Never confuse it with the SqlCommand Timeout property. SqlCommand Timeout defines how much time the SqlCommand object will wait for a database operation (select/insert/update/delete) to be completed. 1 Minute Maximum 10 Minutes.
TransactionScopeOption -
Required (Default): It is default value for TransactionScope. If any already exists any transaction then it will join with that transaciton otherwise create new one.
Required New: When select this option a new transaction is always created. This transaction is independent with its outer transaction.
Suppress: When select this option, no transaction will be created. Even if it already.
One of the important aspect for any business application is Transaction management. Each and every large scale development framework provides a component for managing transactions. .NET Framework is a large development framework and it also provides its own transaction management component.
From.NET Framework 2.0 onwards TransactionScope class is available in the System.Transactions assembly. This class provides a transactional framework with the help of which any .NET developer can write transactional code without having much knowledge. To resolve transactional issues like deadlocks, timeouts, etc., you must know each and every concept directly/indirectly related to a transaction. There is no alternative. So the concepts of a transaction and its related components need to be clear.
In a database transaction we can say, a series of MDL operations like insert/update/delete, execute as a whole. All statements either successfully execute, or will fail each and every statement, so that the database is in consistent mode. Database transactions actually represent a database state change in an accurate way.
There are two types of Transactions based on the boundary;
Local Transaction
Distributed Transaction
To manage local transactions, a transaction where a series of DML statements execute as a whole on a single database. It is actually a single phase transaction handled by a database directly. All transactions are started by System.Transactions are handled directly by this component.
However, if it finds the transaction nature is distributed based on some predefined rules it has a fallback transaction to the distributed transaction. If a transaction fails then the affected data sources will be rolled back. In System.Transactions, MSDTC (Microsoft Distributed Transaction Coordinator) manages distributed transactions. It implements a two phase commit protocol. A distributed transaction is much slower than a local transaction. The transaction object automatically escalates a local transaction to a distributed transaction when it understands that a distributed transaction is needed. The developer can not do anything here.
We know that in a distributed transaction, several sites are involved. Each site has two components:
Transaction Manager
Transaction Coordinator
1. Transaction Manager: Maintains a log and uses that log if recovery is needed. It controls the whole transaction by initiating and completing, and managing the durability and atomicity of a transaction. It also coordinates transactions across one or more resources. There are two types of transaction managers.
Local Transaction Manager: Coordinates transaction over a single resource only.
Gloabl Transaction Manager: Coordinates transaction over multiple resources.
2. Transaction Coordinator: Starting the execution of transactions that originate at the site. Distributes subtransactions at appropriate sites so that they can execute in those sites. Coordinates each transaction of each site. As a result the transaction is committed or rolled back to all sites.
Usage:
Transaction which is tied directly with a database connection (SqlConnection) is called Connection Transaction. SqlTransaction
There ate few transaction related topics you mush aware of;
Transaction Properties
There are four important properties for a transaction. We call them ACID properties. They are:
A-Atomic
C-Consistent
I-Isolation
D-Durable
Atomic: If all parts of the transaction individually succeed then data will be committed and the database will be changed. If any single part of a transaction fails then all parts of the transaction will fail and the database will remain unchanged. Part of the transaction might fail for various reasons like business rule violation, power failure, system crash, hardware failure, etc.
Consistent: Transaction will change the database from one valid state to another valid state following various database rules like various data integrity constraints (primary/unique key, check/not null constraint, referential integrity with valid reference, cascading rules ) etc.
Isolation: One transaction will be hidden from another transaction. In another way we can say, one a transaction will not affect an other transaction if both work concurrently.
Durability: After a transaction is successfully completed (committed to the database), changed data will not be lost in any situation like system failure, database crash, hardware failure, power failure etc.
Transaction Isolation Levels
A Transaction Management System introduces a locking mechanism. With the help of this mechanism one transaction is isolated from another. The locking policy behaves differently based on the Isolation level set for each transaction. There are four very important isolation levels in .NET transaction scope. These are:
Serializable: Highest level of isolation. It locks data exclusively when read and write occurs. It acquires range locks so that phantom rows are not created.
Repeatable Read: Second highest level of isolation. Same as serializable except it does not acquire range locks so phantom rows may be created.
Read Committed: It allow shared locks and read only committed data. That means never read changed data that are in the middle of any transaction.
Read Un-Committed: It is the lowest level of Isolation. It allows dirty read.
Lets start understanding TransactionScope and its usage pattern:
It is very important to know about the default properties of the TransactionScope object. Why? Because many times we create and use this object without configuring anything.
Three very important properties are:
IsolationLevel
Timeout
TransactionScopeOptions
IsolationLevel - It defines the locking mechanism and policy to read data inside another transaction. Serializable (Default), Read Committed, Read Un Committed, Repeatable Read
Timeout - How much time object will wait for a transaction to be completed. Never confuse it with the SqlCommand Timeout property. SqlCommand Timeout defines how much time the SqlCommand object will wait for a database operation (select/insert/update/delete) to be completed. 1 Minute Maximum 10 Minutes.
TransactionScopeOption -
Required (Default): It is default value for TransactionScope. If any already exists any transaction then it will join with that transaciton otherwise create new one.
Required New: When select this option a new transaction is always created. This transaction is independent with its outer transaction.
Suppress: When select this option, no transaction will be created. Even if it already.
Comments