Implementation of ASP Transaction Processing

I. Definitions of transactions in SQL Server books onlineTransactions
A transaction is a series of operations performed as a single logical unit of work. A logical unit of work must have four attributes, called acid (atomicity, consistency, isolation, and durability). Only in this way can a transaction be made:
Atomicity
A transaction must be an atomic unit of work. modifications to its data must either be performed in all or not.

Consistency
When the transaction is completed, all data must be consistent. In related databases, all rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures (such as B-tree indexes or two-way linked lists) must be correct.

Isolation
Modifications made by a concurrent firm must be isolated from those made by any other concurrent firm. The status of the data when the transaction is viewing the data is either the status before the transaction is modified or the status after the transaction is modified. The transaction does not view the data in the intermediate status. This is called serializability because it can reload the starting data and replays a series of transactions so that the State at the end of the data is the same as that of the original transaction execution.

Durability
After the transaction is completed, its impact on the system is permanent. This modification will be maintained even if a system failure occurs.

Specify and force Transaction Processing
SQL programmers are responsible for starting and ending transactions, while forcing logical consistency of data. Programmers must define the order of data modification so that the data is consistent with the business rules of their organizations. The programmer then includes the modified statements into a transaction so that Microsoft SQL server can force the physical integrity of the transaction.

Enterprise database systems (such as SQL Server) have the responsibility to provide a mechanism to ensure the physical integrity of each transaction. SQL Server provides:

Lock the device to isolate transactions.

Record the device to ensure transaction persistence. Even if the server hardware, operating system, or SQL Server fails, SQL Server can use transaction logs during restart to automatically roll back all unfinished transactions to the system fault location.

The transaction management feature forces the atomicity and consistency of transactions. After the transaction is started, it must be completed successfully. Otherwise, SQL Server will cancel all modifications made to the data after the transaction is started.
1988-2000 Microsoft Corporation. All rights reserved.
Ii. Instances

① Use the connection object in the ASP built-in ADO component to implement transaction processing for database operations.
Transaction Process in the connection object:

• Begintrans-start a new transaction.
• Committrans-save any changes and end the current transaction. It may also start new transactions.
• Rollbacktrans-cancels any changes made in the current transaction and ends the transaction. It may also start new transactions.

<%
Call dbconnection (objconn) '// link to the database
On Error resume next

Objconn. begintrans // start a transaction

'// Statement 1
Strsql = "insert into [user] (uname, upasswd) values ('" & s_uname & "', '" & s_upasswd &"')"
Objconn. Execute (strsql)
'// Statement 2
Strsql = "insert into userinfo (uname, age, sex, telphone, address) values ('" & s_uname & "'," & I _age &", '"& s_sex &"', '"& s_telphone &"', '"& s_address &"')"
Objconn. Execute (strsql)

If objconn. errors. Count then
Errors. Clear
Objconn. rollbacktrans '// if an error occurs, the transaction rolls back.
Response. Write ("fail ")
Else
Objconn. committrans '// submit a transaction
Response. Write ("OK ")
End if

Call dbclose (objconn) '// release the database link
%>
Note:
When the preceding Code encounters an error in a statement, normal transaction processing cannot be performed, and Statement 1 cannot be executed. Because on error resume next is used, objconn. errors. Count can only obtain the results returned by the objconn of the last database operation. However, because Statement 2 is correct, the transaction processing is invalid. Therefore, you need to make corresponding changes to the error handling.
Change If objconn. errors. Count then to If err then.

② Using the transaction processing mechanism of the database system, you can write a stored procedure containing transactions on the database server to complete the transaction processing of data operations. At the same time, the ADO component is used to call the stored procedure, and the returned code of the stored procedure is used to determine whether the transaction processing is successful.

In the database system, each SQL statement is a transaction. Therefore, each statement can be completed or returned to the start point. However, if you want all the operations of a group of SQL statements to be completed or all of them are invalid, you need to use the transaction processor of the database to implement the operation.

The main code for generating a stored procedure in a database is as follows:
Create procedure DBO. registeruser
@ Uname varchar (30 ),
@ Upasswd varchar (30 ),
@ Age int,
@ Sex varchar (6 ),
@ Telphone varchar (20 ),
@ Address varchar (50)
As
Set nocount on
Begin
Begin transaction
Insert into DBO. [user] (uname, upasswd) values (@ uname, @ upasswd)
If @ error <> 0
Begin
/* Operation failed, transaction rollback */
Rollback transaction
/* Return the stored procedure and set the return code as a transaction operation failure */
Return-1
End
Insert into DBO. [userinfo] (uname, age, sex, telphone, address) values (@ uname, @ age, @ sex, @ telphone, @ address)
If @ error <> 0
Begin
/* Operation failed, transaction rollback */
Rollback transaction
Return-1
End
/* If the operation is correct, the transaction is committed */
Commit transaction
Return 0
End
Go
The main code for calling a database stored procedure in an ASP script is as follows:

Call dbconnection (objconn) '// link to the database

Set mycomm = server. Createobject ("ADODB. Command ")
Mycomm. activeconnection = objconn 'objconn is the database connection object.
Mycomm. commandtext = "registeruser" 'specifies the name of the stored procedure.
Mycomm. commandtype = 4 'indicates that this is a stored procedure
Mycomm. Prepared = true 'requires that the SQL command be compiled first

'Declare the Stored Procedure Return Value
Mycomm. Parameters. append mycomm. createparameter ("retcode", 2, 4)
'// Create a stored procedure input parameter object
Mycomm. Parameters. append mycomm. createparameter ("@ uname", 200,1, 30, s_uname)
Mycomm. Parameters. append mycomm. createparameter ("@ upasswd", 200,1, 30, s_upasswd)
Mycomm. Parameters. append mycomm. createparameter ("@ age", 3, 1, 4, I _age)
Mycomm. Parameters. append mycomm. createparameter ("@ sex", 200,1, 6, I _sex)
Mycomm. Parameters. append mycomm. createparameter ("@ telphone", 200,1, 20, s_telphone)
Mycomm. Parameters. append mycomm. createparameter ("@ address", 200,1, 50, s_address)
Mycomm. Execute

Retvalue = CINT (mycomm ("retcode "))
'// Determine whether the registration is successful Based on the returned value
If retvalue <0 then
Response. Write ("fail ")
Else
Response. Write ("OK ")
End if

Set mycomm = nothing
Call dbclose (objconn) '// release the database link

③ When using the transaction processing mechanism of the MTS (Microsoft Transaction Server) component to process transactions, note that transactions under this mechanism cannot span multiple ASP pages, if a transaction needs objects from multiple components, the operations on these objects must be combined in an ASP page.

First, add the command @ transaction on the top to declare an ASP page as transactional.

@ Transaction command must be the first line on the first page; otherwise, an error will occur. When ASP Script Processing ends on the page, the current transaction ends.

<% @ Transaction = "required" Language = "VBScript" %>
<%
On Error resume next
'// Event triggered when the transaction is successfully executed
Sub ontransactioncommit ()
Response. Write ("OK ")
End sub

'// Transaction execution failure trigger event
Sub ontransactionabort ()
Response. Write ("fail ")
End sub

Call dbconnection (objconn) '// link to the database

Strsql = "insert into [user] (uname, upasswd) values ('" & s_uname & "', '" & s_upasswd &"')"
Objconn. Execute (strsql)
Strsql = "insert into userinfo (uname, age, sex, telphone, address) values ('" & s_uname & "'," & I _age &", '"& s_sex &"', '"& s_telphone &"', '"& s_address &"')"
Objconn. Execute (strsql)

If err then
Err. Clear
Objectcontext. setabort ()
Else
Objectcontext. setcomplete ()
End if

Call dbclose (objconn) '// release the database link
%>
Comparison of solutions:
From a flexible perspective, choosing the ASP database component method has certain advantages: You can use the ADO database component to complete transaction processing, and you can also choose the actual needs, customize your own database components (as long as the ASP Component writing specifications are met ).
If you consider the reliability of database transaction processing, it is better to use the internal transaction processing stored procedures of the database. In this way, you can directly use the database transaction mechanism to complete the logical transaction processing of the application, which is secure and reliable, and reduces the Data Interaction Between the Web server and the database server. This is especially important for distributed database systems.
The transaction processing method using MTS components has the following advantages: the MTS server directly controls and manages the completion and revocation of operations on the components (the components registered in MTS, it has good expansion space and application prospects. It can give full play to the technical advantages of MTS, enhance the fault tolerance of network applications, and improve the dynamic performance of IIS web servers.