When developing web applications, it is invariably necessary to access the database to complete operations such as querying, inserting, updating, and deleting data. Affected by application logic, it is sometimes necessary to combine multiple database operation instructions into a unit of work (transaction). In a database, a transaction refers to a set of logical operating units that transform data from one state to another. To ensure the consistency of data in the database, data should be operated on in discrete groups of logical units: when it is all completed, the consistency of the data can be maintained; but when part of the operation in the unit fails, the entire transaction will be ignored. , all operations from the starting point onwards are returned to the starting state.
In fact, every operation on the database is implicitly transactional by default. This article takes a typical user registration program as an example to introduce three methods of using ASP to implement transaction processing: a solution based on ASP database components, a solution based on the transaction processing mechanism within the database, and a solution based on MTS components.
Program functions
Create two tables in the SQL Server database: USER table and USERDOC table. The USER table stores the user name and password of the registered user, and the USERDOC table stores the registered user's personal information, indexed by the user name. The following is the definition of tables USER and USERDOC:
Create Table USER(userName varchar(30),userPasswd varchar(30))
Create Table USERDOC(userName varchar(30),Age int,Sex int,PhoneNumber varchar(20),Address varchar(50))
When the user requests registration, the ASP script first inserts the username and password into the USER table, and then in the USERDOC Insert the user's personal information (age, gender, contact number, home address, etc.) into the table. At the same time, the application must also ensure that each record in the USER table has a corresponding record in the USERDOC table.
Method 1 uses the Connection object in the ASP built-in ADO component to implement transactional processing of database operations. Some methods of the Connection object are as follows:
●Connection.BeginTrans method: start a transaction;
●Connection.CommitTrans method: complete/submit a transaction;
●Connection.RollBackTrans method: Undo/abandon a transaction.
//Start a transaction operation
<%Conn.BeginTrans%>
<% sqlText="Insert into USER(userName,userPasswd) values('" %>
<% sqlText=sqlText & request("usrName") & "','"&request("usrPasswd")&"') " %>
<% conn.execute(sqlText) %>
<% if conn.Errors.Count>0 then %>
<% conn.Errors.Clear %>
//If the insert data operation fails, the transaction rolls back forward
<% conn.RollBackTrans %>
<% response.Redirct RegisterFail.html %>
<% end if %>
<% sqlText="Insert into USERDOC(userName,Age,Sex,PhoneNumber,Address) "%>
<% sqlText=sqlText & "values('"& request ("usrName") & "', " & request("Age") %>
<% sqlText=sqlText & ",'" & request ("PhoneNum") & "','" %>
<% sqlText=sqlText & request("Address") & "') " %>
//Execute the second insert statement in the transaction unit
<% conn.execute(sqlText) %>
<% if conn.Errors.Count>0 then %>
<% conn.Errors.Clear %>
//If the operation fails, the transaction rolls back forward
<% conn.RollBackTrans %>
<% response.Redirct RegisterFail.html %>
<% end if %>
//If the entire transaction operation is executed correctly, commit the transaction
<% Conn.CommitTrans %>
//Go to the registration success processing page
<% response.Redirct RegisterOk.html %>
Method 2 can use the transaction processing mechanism within the database system to complete transaction processing of data operations by writing stored procedures containing transactions in the database server. At the same time, by using the ADO component to call the stored procedure, you can also determine whether the transaction is executed successfully based on the return code of the stored procedure.
In a database system, each SQL statement is a transaction. Therefore, it is guaranteed that each statement will either complete or return to the beginning. However, if you want the operations of a set of SQL statements to be either all completed or all invalid, you need to use the transaction processing mechanism of the database to achieve this.
The main code to generate the stored procedure in the database is as follows:
Create proc RegisterUser (@usrName varchar(30), @usrPasswd varchar(30),@age int, @PhoneNum varchar(20), @Address varchar(50) ) as begin
//Display definition and start a transaction
begin tran
insert into USER(userName,userPasswd) values(@usrName,@usrPasswd)
if @@error<>0
begin
//If the operation fails, the transaction will be rolled back
rollback tran
//Return to the stored procedure and set the return code to transaction operation failure
return -1
end
insert into USERDOC(userName,age,sex,PhoneNumber,Address)
values(@Usrname,@age,@PhoneNum,@Address)
if @@error<>0
begin
//If the operation fails, the transaction will be rolled back
rollback tran
return -1
end
//If the operation is performed correctly, commit the transaction
commit tran
return 0
end
The main code for calling the database stored procedure in the ASP script is as follows:
<% Set Comm=server.CreateObject
("ADODB.Command") %>
<% Set Comm.ActiveConnection=conn %>
<% Comm.CommandType=adCmdStoredProc %>
<% Comm.CommandText="RegisterUser" %>
//Create stored procedure to return parameter object
<% Set RetCode=Comm.CreateParameter
("RetCode",adInteger,adParamReturnValue) %>
//Create stored procedure input parameter object
<% Set usrName=Comm.CreateParameter ("usrName",adVarchar,adParamInput,30) %>
<% Set usrPwd=Comm.CreateParameter
("usrPasswd",adVarchar,adParamInput,30) %>
<% Set age=Comm.CreateParameter("age",adInteger,adParamInput) %>
<% Set PhoneNum=Comm.CreateParameter
("PhoneNum",adVarchar,adParamInput, 20) %>
<% Set Address=Comm.CreateParameter("Address",adVarchar,adParamInput,50) %>
<% Comm.Parameters.Append usrName %>
<% Comm.Parameters.Append usrPwd %>
<% Comm.Parameters.Append age %>
<% Comm.Parameters.Append PhoneNum %>
<% Comm.Parameters.Append Address %>
<% Comm.Parameters("usrName")=request("usrName") %>
<% Comm.Parameters("usrPasswd")=request("usrPasswd") %>
<% Comm.Parameters("age")=request("age") %>
<% Comm.Parameters("PhoneNum")=request("PhoneNum") %>
<% Comm.Parameters("Address")=request("Address") %>
<% Comm.Execute %>
<% RetValue=Cint(Comm("RetCode")) %>
//Determine whether registration is successful based on the database stored procedure return code
<% if RetValue< 0 then %>
<% response.Redirect RegisterFail.html %>
<% else %>
<% response.Redirect RegisterOk.html %>
<% end if %>
Method 3: When using the transaction processing mechanism of the MTS (Microsoft Transaction Server) component to implement transaction processing, special attention should be paid to the fact that transactions under this mechanism cannot span multiple ASP pages. If a transaction requires objects from multiple components, You must combine the operations on these objects in an ASP page.
First, you need to add the @TRANSACTION directive at the top of the page to declare an ASP page as transactional.
The @TRANSACTION directive must be on the first line of a page, otherwise an error will be generated. When the ASP script processing in the page ends, the current transaction ends.
<%@ TRANSACTION=Required Language=
VBScript%>
//Event triggered successfully by transaction execution
<% Sub OnTransactionCommit() %>
<% response.Redirect RegisterOk.html %>
<% End Sub %>
//Event triggered when things fail to execute
<% Sub OnTransactionAbort() %>
<% response.Redirect RegisterFail.html %>
<% End Sub %>
<% sqlText="Insert into USER(userName,userPasswd) values('" %>
<% sqlText=sqlText & request("usrName") & "','" &request("usrPasswd")&"') " %>
<% conn.execute(sqlText) %>
<% if conn.Errors.Count>0 then %>
<% conn.Errors.Clear %>
<% ObjectContext.SetAbort %>
<% end if %>
<% sqlText="Insert into USERDOC(userName,Age,Sex,PhoneNumber,Address) "%>
<% sqlText=sqlText & "values('" & request("usrName")& "', " & request("Age") %>
<% sqlText=sqlText & ",'" & request("PhoneNum") & "','" %>
<% sqlText=sqlText & request("Address") & "') " %>
<% conn.execute(sqlText) %>
<% if conn.Errors.Count>0 then %>
<% conn.Errors.Clear %>
<% ObjectContext.SetAbort %>
<% end if %>
<% ObjectContext.SetComplete %>
The solution is more flexible. The method of using ASP database components has certain advantages: you can use ADO database components to complete transaction processing, and you can also customize your own database components according to actual needs (as long as they meet the ASP component writing specifications) Can). If you consider the reliability of database transaction processing, it is better to use the transaction processing stored procedure inside the database. In this way, the database transaction mechanism can be directly used to complete the logical transaction processing of the application, which is safe and reliable, and reduces the data interaction between the Web server and the database server. This is especially important for distributed database systems. The advantage of the transaction processing method using MTS components is that the MTS server directly controls and manages the completion and undoing of component (components registered in MTS) operations. It has good expansion space and application prospects, and can give full play to the technical advantages of MTS. Enhance the fault tolerance performance of network applications and improve the dynamic performance of IIS web servers.