First, the basic knowledge
1) Using transaction level readuncommited results in dirty reads, which means that the read is uncommited (uncommitted) data. How do you understand it? After a transaction that uses this isolation level begins. Updated the data of a row of the database, but the workload of the transaction is relatively large, followed by a lot of code is not finished. Unfortunately, there is a friend came to read the data, this time read is the value of uncommitted, if the subsequent work all normal, there is no effect. Once an error occurs in subsequent code execution, there is an inconsistent error, which is applicable to situations where the transaction is extremely self-confident, and the feature is readable and immutable. About the non-change need to explain, MS SQL is the most basic execution unit, if the UPDATE statement of the same data in a transaction is not commited, other transactions need to wait.
2) Using transaction level readcommited will eliminate dirty reads, which means that the read is commited (committed) data. After a transaction using this isolation level begins, other queries except that transaction will wait until the transaction commits. Features are unreadable and cannot be changed,
3) Use transaction level REPEATABLE read, repeatable reading. It does not block the select query like readcommited, but it blocks the UPDATE statement. When a transaction using this isolation level starts, other transactions are blocked from changing the query. But you can still add data.
4) Use transaction level serializable is the most stringent one, compared with repeatable read is at this level can not add data.
5) The following table is a brief description
additions and deletions in addition to other transactions in the transaction has been in the data row of the possible row , which delete, change, check the operation of the same data in multiple transactions. √ Indicates that the operation under this isolation level is not blocked to respond immediately .
Isolation level |
Increase (other transactions) |
Delete (other services) |
Change (Other transaction) |
Check (other services) |
Dirty Read |
Read Submit |
REPEATABLE READ |
Phantom read |
Data expiration |
readuncommited |
√ |
X |
X |
√ |
√ |
X |
X |
√ |
√ |
readcommited |
√ |
X |
X |
X |
X |
√ |
X |
√ |
X |
Repeatable Read |
√ |
X |
X |
√ |
X |
√ |
√ |
√ |
√ |
Serializable |
X |
X |
X |
√ |
X |
√ |
√ |
X |
√ |
Ii. transactions in C #
1) ADO. NET transactions with transactions in EF, they are a class of things. Can be said to be the same thing, are encapsulated in the SQL statement in the Begin Tran commit and so on.
2) Distributed transaction Transcationcope need to reference System.data.Transcations.dll to use
3) The difference between distributed transactions and transactions , the main difference is that the former is used to control data consistency in a data connection, the latter control data consistency in multiple database connections, if not understood, can be remembered as a database using transactions with the former, multiple databases use the latter, If you use Transcationcope in a data connection, it can be interpreted as a simplified version of a transaction because it has only the complete method.
4) Note that the MSDTC component needs to be installed and set up Transcationcope for multi-transaction coordination.
5) Parallel transaction : refers to enabling two transactions in the same dbconnection, this is not supported by ADO (currently), if the program is running and connection does not support parallel transactions, Check to see if two transactions are used in a database connection.
Third, the application and validation of the transaction.
1) Verify the following in turn (last completed)
Considerations for Validation: Setting Pooling = False in the connection string turns off the data connection pool, especially after multiple tests. If you need to verify that multiple transactions are throwing exceptions or blocking processes, you need to set the time-out to more than the program's expected run time.
How transactions are handled in database transactions and their EF