SQL Transaction usage and instance rollback, sqltransaction

SQL Transaction usage and instance rollback, sqltransaction

A transaction executes a series of operations as a unit, either successful or failed, and rolls back to the initial state. In terms of transaction processing, a transaction is either committed or aborted. To commit a transaction, all participants must ensure that any changes to the data are permanent. Changes must be persistent, regardless of system crashes or other unexpected events. As long as one participant cannot make this guarantee, the entire transaction will fail. All data changes within the transaction scope will be rolled back to a specific setting point.

 

 

Begin TRANSACTION
Statement 1;
If @ error <> 0 Goto error
Statement 2;
If @ error <> 0 Goto error

Commit TRANSACTION
Return
Error:
Rollback TRANSACTION

 

A Transaction is a unit of concurrency control and a sequence of user-defined operations. These operations are either done or not done. They are an inseparable unit of work. Through transactions, SQL Server can bind a set of logical operations so that the Server maintains data integrity. When updating multiple tables, a certain execution fails. Transaction rollback is required to maintain data integrity. Display the following transaction setting code: begin try www.2cto.com begin transaction insert into shiwu (asd) values ('aasdasda '); commit transaction end try begin catch select ERROR_NUMBER () as errornumber rollback transaction end catch implicitly sets the transaction Code as follows: set implicit_transactions on; -- start the implicit transaction go begin try insert into shiwu (asd) values ('aasdasda '); insert into shiwu (asd) values ('aasdasda '); commit transaction; end try sel in catch sel Ect ERROR_NUMBER () as errornumber rollback transaction; -- roll back the transaction end catch set implicit_transactions off; -- close the implicit transaction go and show that the following statements of the transaction are not available. The implicit transaction code can be as follows: alter database; backup; www.2cto.com create database; drop database; reconfigure; restore; update statistics; show that transactions can be nested using the following code -- create procedure qiantaoProc @ asd nchar (10) as begin try begin transaction innerTrans save transaction savepoint -- create a transaction save point Insert into shiwu (asd) values (@ asd ); commit transaction innerTrans end try begin catch rollback transaction savepoint -- rollback to the Save point commit transaction innerTrans end catch end go begin transaction outrans exec qiantaoProc 'dasd'; rollback transaction outrans transaction nesting, when you roll back an outer transaction, an exception occurs if the nested transaction has been rolled back. In this case, you need to use the transaction storage point. SQL transaction rollback of the following instance specifies whether Microsoft & reg; SQL Server & #8482; automatically rolls back the current transaction if a running error occurs in the Transact-SQL statement: the Code is as follows: SET XACT_ABORT ON -- if an error is generated, automatically roll back gobegin traninsert into a values (4) insert into B VALUES (5) COMMIT TRAN www.2cto.com or use the _ ConnectionPtr object method: beginTrans, CommitTrans, and RollbackTrans use this series of functions to judge and roll back. Once the BeginTrans method is called, the database will no longer commit any changes made immediately before calling CommitTrans or RollbackTrans to end the transaction. Solution 2 code is as follows: begin transactioninsert into a values (4) ----- This table contains triggers, UPDATE other tables IF @ error <> 0 -- the error begin rollback transaction endelse begin commit transaction end SQL TRANSACTION is used in SQL server + in combination with asp.net. in the. net development environment, there are two ways to complete transaction operations to maintain the data integrity of the database; one is to use sqlserver/42850.htm target = _ blank> SQL stored procedures, the other is in ADO. NET is a simple transaction processing. Now we use a typical bank transfer example to illustrate the usage of the two examples. Let's first look at how the SQL stored procedure completes the transaction operation: first, create a table: the code is as follows: create database aaaa -- create a table that contains the user's account and amount of money gouse aaaacreate table bb (ID int not null primary key, -- account moneys money -- transfer amount) insert into bb values ('1', '000000') -- insert two pieces of data into bb values ('2', '000000 ') use this table to create a stored procedure: create procedure mon -- create a stored procedure, define several variables @ toID int, -- the account receiving the transfer @ fromID int, -- transfer out your account @ momeys money -- transfer amount asbegin tran -- start to execute the transaction update bb set moneys = moneys-@ momeys where ID = @ fromID-the first operation executed, transfer money, minus the transfer amount update bb set moneys = moneys + @ momeys where ID = @ toID -- perform the second operation and accept the transfer amount, add www.2cto.com if @ error <> 0 -- determine if either of the two statements has an error begin rollback tran-start transaction rollback, status return 0 endgo else before the recovery transfer starts -- how to execute the begin commit tran operation return 1 endgo next let's take a look at C #. net: the code is as follows: protected void button#click (object sender, EventArgs e) {SqlConnection con = new SqlConnection (@ "Data Source =. SQLEXPRESS; database = aaaa; uid = sa; pwd = jcx "); // connection string SqlCommand cmd = new SqlCommand (" mon ", con); // call the Stored Procedure cmd. commandType = CommandType. storedProcedure; con. open (); SqlParameter prar = new SqlParameter (); // pass the cmd parameter. parameters. addWithValue ("@ fromID", 1); cmd. parameters. addWithValue ("@ toID", 2); cmd. parameters. addWithValue ("@ momeys", Convert. toInt32 (TextBox1.Text); www.2cto.com cmd. parameters. add ("@ return ",""). direction = ParameterDirection. returnValue; // obtain the return value of the stored procedure cmd. executeNonQuery (); string value = cmd. parameters ["@ return"]. value. toString (); // value the returned value to the value if (value = "1") {Label1.Text = "added successfully" ;}else {Label1.Text = "failed to add ";}} this is to add transactions in the stored procedure. Let's see if the SQL stored procedure is not written in the database, ADO. NET: the code is as follows: protected void Button2_Click (object sender, EventArgs e) {SqlConnection con = new SqlConnection (@ "Data Source =. SQLEXPRESS; database = aaaa; uid = sa; pwd = jcx "); con. open (); SqlTransaction tran = con. beginTransaction (); // first instance the SqlTransaction class. This transaction uses the con connection and uses the BeginTransaction method to start executing the transaction SqlCommand cmd = new SqlCommand (); cmd. connection = con; cmd. transaction = tran; try {// execute the sqlcommand in the try {} block, cmd. commandText = "update bb set moneys = moneys-'" + Convert. toInt32 (TextBox1.Text) + "'where ID = '1'"; cmd. executeNonQuery (); cmd. commandText = "update bb set moneys = moneys + 'A' where ID = '2'"; cmd. executeNonQuery (); tran. commit (); // if both SQL commands are successfully executed, execute the commit method and perform these operations www.2cto.com Label1.Text = "added successfully ";} catch {Label1.Text = "failed to add"; tran. rollback (); // if an exception occurs, execute the rollback method and roll back to the beginning of the transaction;} This is a simple example of the different usage of two transactions, ADO. the NET transaction processing method looks simple, but he wants to use the same connection to execute these operations. If he wants to use several databases to execute these operations with one transaction at the same time, it will be complicated, however, it is relatively simple to use SQL stored procedures.