Implementing Database Transactions with Microsoft.NET (iv)

Data | Database attached A: Introduction to the transaction
A transaction is a series of actions executed as a single logical unit, which means that the actions are either all successful or fail altogether. If the last action fails, the previous action should be rolled back in turn, and the whole state is back to the beginning of the original transaction. For example, there are 1000 dollars deducted from a bank account, into another account, then the transaction guarantee that two events can only be successful together, as long as there is a failure, then there will be no actual action, two accounts did not occur any changes, the money has not been deposited and deducted.



ACID Properties

There are four attributes that are often used to describe transactions: atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability). In an ideal environment, transactions meet these four criteria, but the isolation level of a transaction can be set, because setting an isolation level for the best "serializable" increases the probability that a "deadlock" situation occurs. A deadlock is a two-transaction attempt to lock a resource that has been locked by the other, and the last transaction needs to be rolled back so that the other can continue. To avoid this, you need to lower the isolation level of a transaction to reduce its contention.

L Atomicity: Transactions to work as an atomic unit, either to execute successfully or all to fail

L Consistency: You need to maintain consistency of all data after a transaction, in a relational database where all rules occur on transaction-induced changes to maintain data consistency, and all internal data structures, such as b-tree indexes and two-way joins, must remain correct after the end of the transaction.

L Isolation: Changes made by concurrent transactions must be isolated from changes made by any other concurrent transaction. A transaction either sees the data that the other transaction changed before it, or sees the data after the other transaction changes, it cannot see the temporary data ...

L Persistence: When a transaction is finished, its results persist in the system, and its changes can occur even when the system is paralyzed.



Transaction support Level



Support Level
Describe

Isolated
Ignore transaction environment

does not support
objects or pages do not run in the environment of things. When the request arrives, the context of the object is created as a non transactional type, regardless of whether an active transaction exists.

Support
An object or page runs in an existing transaction environment and is not run as a transaction if the environment has no transactions.

Need
An object or page runs in an existing transaction environment, and if the environment has no transactions, a new one is created.

Need a new
Object or page requires a transaction, and a new transaction is created on each request.


Table 2: Transaction support levels



For ASP.net, the default transaction level for the page is "isolated", where the default level is "required" in the enterprise service, and in general, we will set the level "required" on the first level object that requires the transaction, and the object being invoked is set to "support." This means that the top-level object creates a transaction that will enter the context of the transaction when each invoked object is activated.



Transaction ISOLATION LEVEL



In a program, there will be three different cases depending on the isolation level of the transaction.

L Dirty reads: A transaction reads data that has not yet been submitted by another transaction, so you will see some data that was eventually rolled back by another transaction.

L read is not reproducible: one transaction reads a record, another transaction changes the record and submits it, when the first transaction reads the record again, it has changed.

Phantom reads: A transaction uses a WHERE clause to retrieve data from one table, another transaction inserts a new record, and the Where condition is met, so that the first transaction retrieves data with the same where condition, and then one more record is added.

Support Level
Describe

Read IN UNCOMMITTED
At the lowest level of isolation, there is dirty read, read, and Phantom reads at this level of transactions.

Read into the submitted
The default level for many databases, at which you may experience reading values that are not reproducible and phantom reads.

REPEATABLE READ
This level will encounter phantom reads.

Serializable
Completely isolated between transactions, in COM + 1.0, this is the only option, but in COM + 1.5, it is just one of the options.


Table 3: Transaction Isolation Levels



The isolation level chosen by one firm affects how the database treats locks held by other transactions, and the isolation level you choose depends on your system and business logic. For example, a bank checks its account balances before the customer takes the money, in which case a transaction with an isolation level that is serializable will be required so that the other money-taking action cannot be performed until this completes. If they only need to provide an account balance, "Read Committed" will be the appropriate level because they only need to query the value of the balance, and a lower level will make the transaction run faster.



Suggestions on the development of B affairs
L Test the transaction controller under a certain load to ensure that a deadlock under load does not occur

• Use stored procedures whenever possible, and they still have the best performance in all situations

L Enterprise-Class services provide a high-level approach to transaction control, but use caution because it lowers performance

L Analyze your system to determine which isolation level of transactions to use. The banking system should use a serializable isolation level



With C monitoring Services
There is no special mechanism to track database and ado.net transactions.

Asp. NET and enterprise-class services can be monitored by the console's Component Services, and Component Services can be found in the Start menu's management tools. From this tool, you can monitor all transactions that run through MS DTC, that is, ASP.net and enterprise-class services. You can also monitor the execution time of transactions, or even the entire process of committing, rolling back, and canceling transactions.



Attach D deadlock


Deadlocks occur when two or more processes (or threads) hold each other's locks, typically in a database system, but can also occur in any multithreaded program, such as:

Business 1
Business 2

Lock Table A
Lock Table B

Update table A
Update table B

Attempt to lock table B
Attempt to lock table A




At this point, two transactions cannot continue because they attempt to lock the resource that the other has locked, and according to the rules of the transaction, two transactions are not aware of each other's status. SQL Server detects this situation and chooses one of the rollbacks so that at least one transaction can be completed properly. At this point, the program that controls which transaction is sacrificed has to decide whether to reboot the transaction or throw an error to the user. In general, SQL Server sacrifices the transaction that started after that, or the transaction that can unlock the most deadlocks.

One problem with deadlocks is that the database automatically makes a rollback, this causes a problem in the rollback code of the middle tier that if you encounter a deadlock and you can intercept an exception in the middle tier, you get another exception when the middle tier rolls back, so that you have a second exception, because the SQL The server has done a rollback for you, so you actually did a second rollback when SQL Server could not find a transaction that could be rolled back. So a good habit is to put the rollback code in a try catch, or match the exception to SqlException, and detect the SqlException exception code, if it is 1205, then the deadlock occurs.

For example:

Try

{

Run SQL Commands in ado.net transaction



catch (SqlException Sqlex)

{

Specific catch for deadlock

if (Sqlex. Number!= 1205)

{

Tx. Rollback ();

}

orderId = 0;

throw (Sqlex);

}



Ways to avoid deadlocks



There are several ways to reduce the chance of a deadlock in a transaction

L try to access the table in the same order in the transaction, in the previous example, if two transactions Access table B First, then the deadlock can be avoided and two transactions can succeed.

• Avoid user involvement in transactions/short transactions, long running transactions and the chance of deadlocks in other transactions, which may take the shortest amount of time to run transactions in a stored procedure.

L with a low isolation level, setting the isolation level of your transaction to a minimum will make your business less likely to risk deadlocks, by default, SQL Server sets the isolation level to read Committed, which means that if a transaction locks some data for reading, another transaction allows it to be locked to read, write, Delete and change, this will result in "phantom read" situation, but in the general business is acceptable.

L Use bound connections, in which two connections share a transaction/lock space to avoid lock collisions.