SQL transaction (Transaction) usage introduction and rollback instance

A transaction executes a sequence of operations as a unit, either succeeds or fails, and rolls back to its original state. In transactional terminology, a transaction is either committed or aborted. To commit a transaction, all participants must ensure that any changes to the data are permanent. Regardless of the system crash or other unforeseen events, the changes must be persistent. As long as one participant cannot make this guarantee, the entire transaction fails. All data changes within the scope of the transaction are rolled back to a specific set point.

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

  Commit   TRANSACTION   
  Return   
Error   
Rollback   

A transaction (Transaction) is a unit of concurrency control and is a user-defined sequence of operations. These operations are either done or not, and are an inseparable unit of work. With transactions, SQL Server can bind a logically related set of operations so that the server maintains the integrity of the data   a failure occurs when multiple tables are updated. In order to maintain the integrity of the data, transaction rollback is required.   Display setup transaction   code as follows 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 (' A Asdasda ');  commit transaction; end try begin catch select error_number () as errornumber  ROLLBACK TRANSACTION; --ROLLBACK TRANSACTION  end Catch set implicit_transactions off; --Close implicit transaction  go display transaction The following statement cannot be used, an implicit transaction can be   code as follows Alter database; backup;   www.2cto.com  create Database; drop database; reconfigure; restore; update Statistics; display transactions can be nested using the   code as follows--Create stored Procedures &Nbsp;create procedure qiantaoproc  @asd nchar (TEN)  as begin begin try begin transaction Innertrans save Transaction SavePoint--Create transaction savepoint  insert into Shiwu (ASD) values (@asd);  commit Transaction innertrans end try begin catch rollback transaction SavePoint--rollback to save point  commit Transaction innertrans end catch end go begin transaction outrans exec qiantaoproc ' ASDASD '  rollback transaction Outrans Transaction nesting, when the outer transaction is rolled back, there will be an exception if the transaction within the nested has been rolled back. You need to use a transaction savepoint at this point. The following instance of SQL transaction rollback specifies when the   Transact-SQL   statement generates a run-time error,microsoft&reg;   SQL   server& #8482;   Whether to automatically rollback the current transaction scenario a:  code as follows set   Xact_abort   on--If an error is generated automatically gobegin   Traninsert   into   A & nbsp Values   (4) INSERT   into   B   VALUES   (5) COMMIT   TRAN  www.2cto.com   can also use _con Methods for Nectionptr objects: BeginTrans, CommitTrans, RollbackTrans, using this series of functions to determine and rollback. Once the BeginTrans method is called, the call to CommitTrans or ROLLBACKTRThe,  database will no longer commit any changes immediately before the ANS ends the transaction. Scenario two   code as follows begin Transactioninsert into A   values   (4)  -----The table contains triggers, update other tables if @ @error <> 0 &N bsp;--error    BEGIN     rollback transaction             endelse   BEGIN     commit transaction          END  SQL transaction combined with ASP. Two usage in the SQL server+. NET development environment, there are two ways to perform transaction operations and maintain database integrity; one is to use sqlserver/42850.htm Target=_blank > SQL stored procedure, and the other is a simple transaction in ADO; now, with a typical example of bank transfers, let's take a look at the usage of these two examples to see how the SQL stored procedure is doing the operation of the transaction: first create a table:  The code below CREATE database AAAA--Creates a table containing the user's account number and money gouse aaaacreate table BB (ID int. NOT NULL primary key,  --account moneys money    --transfer amount) insert into BB values (' 1 ', ' 2000 ')--insert two data inserts into BB values (' 2 ', ' 3000 ') create a stored procedure with this table: "Create proc Edure Mon--Create a stored procedure, define several variables @toid int,    --account to receive the transfer @fromid int,  --turn out your account @momeys money--The amount of the transfer Asbegin Tran- -Start execution Transaction  update BB Set [Email&nbsP;protected] WHERE [email protected]-Performs the first operation, transfers money, minus the amount of the transferred out update BB set [email protected] where [email  protected]--Perform a second operation, accept the amount of the transfer, increase  www.2cto.com   if @ @error <>0--judge if any one of the two statements has an error begin Rollback tran– begins the rollback of a transaction, the recovery of the transfer before the start of the status return 0endgo else  --How to execute a successful begin commit Tran perform the operation of this transaction return 1endgo& nbsp, then look at how c#.net calls this stored procedure:     code below protected void Button1_Click (object sender, EventArgs e)   & nbsp {        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 parameters         CMD. 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;//Gets the return value of the stored procedure         CMD. ExecuteNonQuery ();        String value = cmd. parameters["@return"]. Value.tostring ();//assigns the return value to value        if (value = = "1")         {    &NB Sp       Label1.Text = "Add Success";       }        else    &NBSP ;   {            Label1.Text = "Add Failed";       } This is to add a transaction to the stored procedure, and then look at the The database writes SQL stored procedures, ADO. NET is the:  code for how transactions are handled protected void button2_click (object sender, EventArgs e)     {        SqlConnection con = new SqlConnection (@ "Data source=. Sqlexpress;database=aaaa;uid=sa;pwd=jcx ");      &NBSp Con. Open ();        sqltransaction Tran = con. BeginTransaction ();//First Instance SqlTransaction class, use this transaction is con this connection, use BeginTransaction this method to start executing this transaction         SqlCommand cmd = new SqlCommand ();        CMD. Connection = con;        CMD. Transaction = tran;        try        {            & nbsp;//Execute SqlCommand command in try{} block,            CMD. CommandText = "Update bb set moneys=moneys-" "+ Convert.ToInt32 (TextBox1.Text) +" ' Where id= ' 1 ' ";      &NB Sp     CMD. ExecuteNonQuery ();            CMD. CommandText = "Update bb set moneys=moneys+ ' AA ' where id= ' 2 '";            CMD. ExecuteNonQuery ();            Tran. Commit ();//If all two SQL commands execute successfully, execute this method of commit, perform these actions    www.2cto.com            &NBSp Label1.Text = "Add Success";       }        catch        {  &NB Sp         Label1.Text = "Add Failure";            Tran. Rollback ();//How to perform unsuccessful, exception occurs, execute Rollback method, rollback to start of transaction operation;       }    } This is a simple example of how two transactions are used in different ways, and the ADO method looks simple, but he wants to use the same connection to perform these operations, which is cumbersome if you use a few databases to execute with a single transaction, but it's relatively straightforward to use SQL stored procedures.

SQL Transaction (Transaction) usage introduction and rollback instance