Getting started with SQL stored procedures (transactions) (iv)

Let's take a look at the transaction processing technology in this article.

Why use a transaction, what is the use of a transaction, for instance.

Suppose we now have a business, as the success of something to the 2 table to insert data, a table, B table, we insert the order is first insert a, then insert B, if all successful insert successfully, of course, no problem, if any one table insert failed, and another table inserted successfully, Inserting a successful table is junk data. We have to judge that any table insert failure is rolled back, or not inserted, this is the basic use of the transaction.

One, the SQL transaction definition

A transaction is a user-defined sequence of database operations that is an inseparable unit of work. All of the database operations commands he contains are submitted or revoked as a whole, either wholly or completely.

For example, in a relational database, a transaction can be an SQL statement, or a set of SQL statements or an entire program.

Two, SQL transaction statements

Start transaction: Begin TRANSACTION
Commit TRANSACTION: Commit TRANSACTION
ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

A transaction usually begins with a BEGIN transaction, ends with a commit or rollback, commits a commit, commits all operations of the transaction. Specifically, all updates to the database in the transaction are written to disk

On the physical data, the transaction ends normally.

Rollback indicates a rollback, a failure occurred during the run of the transaction, the transaction cannot continue, and the system cancels all completed operations on the database in the transaction, rolling back to the state at the beginning of the transaction. Here's the operation

Refers to an update operation on a database.

Third, the characteristics of the transaction (ACID propertites)

Atomicity (atomicity): A transaction is a logical unit of work for a database, and all operations included in a transaction are either done or not.

Consistency (consistency): When things are done, the data must be consistent, that is, the data in the data store is in a consistent state before the things begin. Guarantee the lossless data

Isolation (Isolation): The execution of one transaction cannot be disturbed by other transactions. Each transaction that executes concurrently cannot interfere with each other.

Persistence (Durability): Refers to a transaction that, once committed, has a permanent change to the data in the database.

Four, the stored procedure uses the transaction

Get ready to work, create a table, insert a piece of data.

--Create TABLE userinfo (ID  int identity (UserName),  primary key, userpwd varchar,   Registertime datetime)--Initialize inserts a record insert into userinfo (username,userpwd,registertime) VALUES (' admin ', ' admin ', GETDATE ()) SELECT * FROM UserInfo

-------------------

ID userName userpwd Registertime

1  admin   admin    2013-04-13 10:30:36.387

As seen from the table structure, username is the primary key, which is a unique value, and now you want to insert two data

Create Procedure  myprocedure    as       Begin           Set    NOCOUNT on    ;            Set Xact_abort on; --that's very important  . Begin Tran   --Start transaction insert INTO                      userinfo (username,userpwd,registertime) VALUES (' admin ', ' Admin ', GETDATE ())           insert into userinfo (username,userpwd,registertime) VALUES (' Jack ', ' Jack ', GETDATE ())           Commit Tran       --Commit TRANSACTION       End

Perform

exec    myprocedure/* msg 2627, Level 14, State 1, Procedure myprocedure, line 9th violates the PRIMARY KEY constraint ' pk__userinfo__c9f284577f60ed59 '. You cannot insert a repeating key in the object ' Dbo.userinfo '. */--View Database------------------------------------------------    Admin    admin    2013-04-13 10:41:22.457

It says Set Xact_abort on; This sentence is very important, why? Let's look at the effect when we set it to off.

Create Procedure  myprocedure    as       Begin           Set    NOCOUNT on    ;            Set Xact_abort off; --that's very important. Begin  Tran   --Start transaction                      INSERT INTO UserInfo (username,userpwd,registertime) VALUES (' admin ' , ' admin ', GETDATE ())           insert into userinfo (username,userpwd,registertime) VALUES (' Jack ', ' Jack ', GETDATE ())           commit Tran       --Commit TRANSACTION       End       

Execute and view results

  exec    myprocedure/* msg 2627, Level 14, State 1, Procedure myprocedure, line 9th violates the PRIMARY KEY constraint ' pk__userinfo__c9f284577f60ed59 '. You cannot insert a repeating key in the object ' Dbo.userinfo '. Statement has been terminated. */--View Results select * FROM UserInfo
-------------------------------------------    Admin    admin    2013-04-13 10:41:22.45715    Jack    Jack    2013-04-13 10:44:05.203

Here we set the Xact_abort to off, the execution of the transaction already has an error, but the "Jack" record is inserted. This violates the principle of consistency of the transaction. So we're going to set the xact_abort to on reason.

Take a look at the following instructions:

1, the use of stored procedures to execute things, you need to turn on the Xact_abort parameter (the default is OFF), set this parameter to on, indicating that when the transaction is executed, if an error occurs, the transcation is set to uncommittable state, Then all operations will be rolled back after the statement block batch is completed, and if the parameter is set to OFF, the statement will not execute when the transaction is executed, and the other correct operation continues if an error occurs.

2. When set NOCOUNT is on, no count is returned (the count indicates the number of rows affected by the Transact-SQL statement, for example, after a delete operation in SQL Server Query Analyzer, the lower window prompts (3) rows affected). When set NOCOUNT is OFF, the count is returned, and we should add set NOCOUNT on the head of the stored procedure so that when the stored procedure is exited, the set NOCOUNT OFF is added to achieve the purpose of optimizing the stored procedure.

five, transaction and try...catch in stored procedure Federated Use

If we have an error in the stored procedure transaction, we do not want to display the error, we want to handle the error message dynamically, such as error, we rollback, we set the value of a property, here will use a try, catch
Or starting with an example.

Create Procedure  myprocedure    as       Begin           Set    NOCOUNT on    ;            Set Xact_abort on; --that's very important.           begin try               begin  Tran   --Start transaction insert INTO                              userinfo (username,userpwd,registertime) VALUES (' admin ', ' admin ', GETDATE ())               insert into userinfo (username,userpwd,registertime) VALUES (' Jack ', ' Jack ', GETDATE ())               commit Tran       --Commit transaction            End Try            begin catch                --here you can use Xact_state () to determine if there are non-committed transactions, Non-committed transactions                --Indicates an error has occurred inside the transaction. Xact_state () has three kinds of values:-1. The transaction is not submitted;                 --1. Transaction may be submitted; 0. Indicates that there is no transaction, at which time commit or rollback will report an error.                 if Xact_state () =-1                     rollback Tran;            End Catch

      Set Xact_abort OFF;

End

We're not going to have that kind of mistake again when we do it.

  The EXEC    myprocedure--------------command has completed successfully.   --There's no such error.    SELECT * from UserInfo      --------------------------------------------------    Admin    Admin    2013-04-13 10:55:50.653

As you can see, the transaction is rolled back and no data has been inserted.

If we want to see the error message, let's look at an example.

Create Procedure  myprocedure    as       Begin           Set    NOCOUNT on    ;            Set Xact_abort on; --that's very important.           begin try               begin  Tran   --Start transaction insert INTO                              userinfo (username,userpwd,registertime) VALUES (' admin ', ' admin ', GETDATE ())               insert into userinfo (username,userpwd,registertime) VALUES (' Jack ', ' Jack ', GETDATE ())               commit Tran       --Commit transaction            End Try            begin catch                --here you can use Xact_state () to determine if there are non-committed transactions, Non-committed transactions                --Indicates an error has occurred inside the transaction. Xact_state () has three kinds of values:-1. The transaction is not submitted;                 --1. Transaction may be submitted; 0. Indicates that there is no transaction, at which time commit or rollback will report an error.                 if Xact_state () =-1                 begin                       rollback TRAN;                       SELECT error_number ()  as ErrorNumber,                       error_message ()  as  errormessage;                     End            End Catch                  end

Perform

    EXEC    myprocedure--------------------------------errornumber         ErrorMessage2627             violated the PRIMARY KEY constraint ' pk__ Userinfo__c9f284577f60ed59 '. You cannot insert a repeating key in the object ' Dbo.userinfo '.

Description: 1. There are many functions for capturing errors, as follows:

Error_number () returns the error number.

Error_severity () returns the severity.

Error_state () returns the error status number.

Error_procedure () returns the name of the stored procedure or trigger where the error occurred.

Error_line () returns the line number in the routine that caused the error.

Error_message () returns the full text of the error message. The text can include values provided by any replaceable parameter, such as length, object name, or time.

When a transaction is used in a stored procedure, if there is a Try...catch statement block, then when the error is caught, you need to manually perform the rollback operation in the catch statement block, or the system will pass an error message to the client. If set XACT_ABORT on at the beginning of the stored procedure, when an error occurs, the system will place the current transaction in a non-committed state, which will set Xact_state () to 1, at which time only the transaction can be rollback and cannot be committed (commit) Operation, we can determine whether a transaction is in a non-committed state based on the value of Xact_state () in the Catch statement block, and if so, you can do rollback.

If set Xact_abort off at the beginning of the stored procedure, then when an error occurs, the system does not speak Xact_state () to 1, then we cannot determine in the CATCH block whether a rollback is required based on the value of the function. But we can judge according to the @ @Trancount global variable, if in the catch block to determine that the @ @Trancount value greater than 0, the representative has uncommitted transactions, since entering the catch statement block, then there are uncommitted transactions, The transaction should be rollback, but this approach may not be accurate in some cases.

The recommended method is to determine if the rollback operation is required by Xact_abort on the set and then judging the value of xact_state () in the catch.

Let's look at an example below and keep familiar with it in practice.

This example is if you insert duplicate data to give a message and return

--Determine if the stored procedure name to be created exists if Exists (Select name from sysobjects Where name = ' P_insertuser ' and type = ' P ')--Delete stored procedure drop Procedure Dbo. P_insertusergo

Use [Storetest]gocreate Procedure [dbo]. [P_insertuser] @UserName varchar, @UserPwd varchar (asbeginset) NOCOUNT on; Set Xact_abort on; --This sentence is very important begin try if (IsNull (@UserName, ') = ")
Begin PRINT ' UserName is empty ';
Return
End declare @iCount int;    Set @iCount = 0;    Select @iCount = Count (1) from UserInfo with (NOLOCK) where [email protected];        if (@iCount > 0) BEGIN print ' The current name already exist ';        Return end begin Tran-start transaction, cannot have return statement in transaction--insert INSERT INTO UserInfo (username, userpwd       , registertime) VALUES (@UserName, @UserPwd, GETDATE ()) Commit Tran --Commit TRANSACTION End Trybegin catch--here can use Xact_state () to determine if there are non-committed transactions, non-committed transactions--Indicates an error has occurred inside the transaction. Xact_state () has three kinds of values:-1. The transaction is not submitted;--1. transaction may be submitted; 0. Indicates that there is no transaction, at which time commit or rollback will report an error.   If Xact_state () =-1begin rollback tran; --Transaction rollback SELECT Error_number () as ErrorNumber, Error_message () as Errormessage;endend catch Set xact_abort off; end--Call stored procedure exec [P_insertuser] ', ' admin ' select * from Userinfogo

There are a lot of things to do here, and hopefully it will be a good result.

near here. C # uses the syntax of transactions, the concept is the same, but is implemented in C #.
Http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction (v=vs.110). aspx

 using (SqlConnection conn = new SqlConnection (connstring) {conn.           Open ();           Start a local transaction. SqlTransaction Sqltran = conn.           BeginTransaction ();           Enlist a command in the current transaction. SqlCommand command = conn.           CreateCommand (); BEGIN TRANSACTION Command.              Transaction = Sqltran;               try {//Execute separate commands.               Command.commandtext = "xxxxx"; Command.               ExecuteNonQuery (); Command.commandtext = "yyyyyy"; command.               ExecuteNonQuery ();               Commit the transaction.           Sqltran.commit ();               } catch (Exception ex) {//Handle the Exception if the transaction fails to commit. Lblmsg.text = ex.               Message;                   try {//attempt to roll back the transaction.     Sqltran.rollback ();          } catch (Exception exrollback) {//Throws an INVALIDOPERATIONEXCE ption if the connection//is closed or the transaction have already been rolled//b                   ACK on the server.               Lblmsg.text = Exrollback.message; }           }       }

To be Continued ...

Getting started with SQL stored procedures (transactions) (iv)