Switch, Asp.net Transaction Processing

Transaction Processing
A transaction is a mechanism that ensures that multiple SQL statements are treated as a single work order.
To process. Transactions have the following roles:
* Consistency: the query and update operations at the same time do not conflict with each other.
The user will not see the changed but not submitted data.
* Recoverability: Once a system failure occurs, the database automatically recovers completely.
.

2. Transactions and consistency
A transaction is the unit of integrity. The execution of a transaction is to extract the database from one
To another consistent state. Therefore, if the transaction is executed in an isolated manner
Yes, but if multiple transactions are concurrently staggered, they may interfere with each other,
The database status is inconsistent. In a multi-user environment, the same database must be avoided
When the query and update operations conflict. This is very important if
The processed data can be modified by another user when the processing is running,
The processing result is not clear.
Concurrent access without control may produce the following errors:
1. Lost updates)
When multiple transactions concurrently modify one data, an incorrect conclusion is obtained without any control.
If this happens, one modification overwrites the other.
2. Non-repetition of reading
When multiple transactions access data in a certain time sequence
An error may also occur if the access is not controlled.
3 dirty read (dirdy data), read inconsistency
4. Chaos of the current value caused by the cursor
During the execution of a transaction, the current query position on a table is determined by the cursor table.
. The cursor points to the record currently being processed. After this record is processed
To the next record. When multiple transactions are concurrently executed, modifications to a transaction may produce
Causes a transaction error related to the cursor.
5. The chain exits due to unreleased changes
A transaction may fail during the modification operation.
Roll back the changes that have been made ). If an error has been found
If you allow other transaction reads to be modified (dirty reads) before restoration
The chain exits.
6 when a transaction updates a table, other transactions modify or delete
Definition.
The database automatically sets an appropriate level of locking for each transaction. For
Problems: dirty reads, unreleased modifications, resulting in a chain exit, a transaction on
When the table is updated, other transactions modify or delete the definition of the table, and the database automatically
Solution. The other three problems need to be artificially defined in the programming process
Lock to solve.
3. Transactions and recovery
Databases themselves shoulder the responsibility of managing transactions. Transactions are the smallest logical work.
Unit, in which all database updates are required
All succeeded, or all failed (rollback ). As long as the application specifies
The segment program processes a transaction (commit or rollback), and the database system
The system automatically maintains the features of the transaction.

Oracle Database Transaction Definition
Oracle transactions start from commit, rollback, database connection, or first
When an executable SQL statement starts, a commit, rollback statement, or exit
Database End. If a transaction contains a DDL statement
Both start and end a transaction by executing the commit statement implicitly.
If a transaction must be in
Cancel the statement before submission, and the database is restored to the state before these statements and execution.
Status.
The rollback statement can be used to undo or roll back
Transaction. The entire transaction can be rolled back, or some transactions can be rolled back, but cannot be rolled back.
A committed transaction. The rollback command for some transactions is:
Rollback to savepoint storage name
A storage point is a tag that the user puts into the transaction and is used to indicate a retainable
Location. A storage point is inserted by placing a savepoint command in a transaction. The
Command syntax:
Savepoint storage name
If the storage name is not given in the rollback statement, the entire transaction is returned.
Return.
Transaction definition of Sybase Database
Sybase uses the begin transaction and commit transaction commands to specify
SQL statements of any number are processed as a unit. Rollback transaction
The command allows the user to restore to the beginning of the transaction, or to the transaction that has been saved
The storage point defined by the transaction command.
Begin transaction and commit transaction can contain any number of SQL statements
The statement and stored procedure are simple:
Begin transaction [transaction name]
Commit transaction
If a transaction must be committed due to some faults or a change in mind
And then the database is restored to the status before these statements and process execution.
The rollback transaction command can be used in the commit transaction command
Roll back a transaction at any time. You can roll back the entire transaction or part of the transaction,
You cannot roll back a committed transaction. The rollback transaction command is:
Rollback transaction [transaction name | storage name]
The storage name is the identifier that the user puts into the transaction and is used to indicate that a transaction can be rolled back.
Location. The storage name is inserted by placing a save transaction command in the transaction.
. The syntax of this command is:
Save transaction storage name
If no store name or transaction name is provided in rollback transaction
The transaction is rolled back to the first begin transaction statement in the batch processing.
Else ---------------------------------------------------------------------------------------------------------------------------------

Ado. net transaction processing.
In ADO. net, you can use the connection and transaction objects to control transactions. To execute a transaction, perform the following operations:
• Call the begintransaction method of the connection object to mark the start of the transaction.
• Assign the transaction object to the transaction attribute of the command to be executed.
• Execute the required commands.
• Call the commit method of the transaction object to complete the transaction or call the rollback method to cancel the transaction.
Of course, the ADO. net transaction processing has advantages and disadvantages. It depends on the specific situation.
• Advantages:
-Simplicity
-Almost as fast as data transactions
-The proprietary code of different databases is hidden independently of databases.
• Disadvantages:
-Transactions cannot span multiple database connections.
-The transaction runs on the database connection layer. Therefore, you must maintain a database connection during the transaction process.
Let's look at an example below to create a page, which is also simple. You only need a button and then program it:
1 using system;
2 using system. Data;
3 using system. configuration;
4 using system. collections;
5 using system. Web;
6 using system. Web. Security;
7 using system. Web. UI;
8 using system. Web. UI. webcontrols;
9 using system. Web. UI. webcontrols. webparts;
10 using system. Web. UI. htmlcontrols;
11 using system. Data. sqlclient;
12
13 namespace webapplication1
14 {
15 public partial class adoaction: system. Web. UI. Page
16 {
17 protected void page_load (Object sender, eventargs E)
18 {
19
20}
21
22 protected void btn_click (Object sender, eventargs E)
23 {
24 sqlconnection con = new sqlconnection ();
25 con. connectionstring = configurationmanager. connectionstrings ["DSN"]. connectionstring;
26 con. open ();
27 // start a transaction.
28 sqltransaction mytran = con. begintransaction ();
29 // create a command for the transaction. Note that the first execution of the two commands is successful. We will execute it again and fail.
30 // for the third time, we change one of the commands without changing the other. At this time, the transaction reports an error. This is the transaction mechanism.
31 sqlcommand mycom = new sqlcommand ();
32 mycom. Connection = con;
33 mycom. Transaction = mytran;
34 try
35 {
36 mycom. commandtext = "insert into sqlaction values ('test 2', '123 ')";
37 mycom. executenonquery ();
38 mycom. commandtext = "insert into sqlaction values ('test 3', '123 ')";
39 mycom. executenonquery ();
40 mytran. Commit ();
41 response. Write ("successfully executed ");
42
43}
44 catch (exception ex)
45 {
46 mytran. rollback ();
47 // create an error message and return an exception
48 response. Write (ex. tostring ());
49 response. Write ("failed to write to Database ");
50}
51 finally
52 {
53 con. Close ();
54}
55
56}
57
58
59}