Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of the syntax and usage of Sql Server transactions. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

An example is given to illustrate the syntax and usage of Sql Server transactions. The details are as follows:

Affairs are about atomicity. The concept of atomicity means that something can be treated as an indivisible unit. From a database perspective, it refers to the smallest combination of one or more statements that should be executed or not executed at all.

In order to understand the concept of transactions, you need to be able to define very well-defined boundaries. The transaction should have a very clear beginning and end point. Every select, insert, update, delete statement in SqlServer is part of an implicit transaction. Even if only one statement is issued, it will be treated as a transaction-or execute all or nothing of the statement. But what if you need not just one statement, but multiple statements? In this case, there needs to be a way to mark the beginning and end of the transaction, as well as the success or failure of the transaction. You can use some T-SQL statements to "mark" these points in a transaction.

Begin tran: set the starting point commit tran: make the transaction a permanent, irreversible part of the database rollback tran: essentially want to forget that it ever happened save tran: create a specific tag that allows only partial rollback

Begin tran

The beginning of a transaction is probably the easiest concept to understand in the process of things. Its sole purpose is to indicate the beginning of a unit. If, for some reason, you cannot or do not want to commit a transaction, then this is the starting point where all database activities will be rolled back. That is, the database ignores all statements that end up not being committed after this starting point.

The syntax is as follows:

The copy code is as follows: begin tran [saction] [|] [with mark []]

Commit tran

The commit of a transaction is the end of a transaction. When the commit tran command is issued, the transaction can be considered complete. That is, after all the Sql statements contained in the transaction have been executed, the impact of the transaction is now persistent and will continue, even if the system fails (as long as there is a backup or the database file has not been physically corrupted). The only way to undo a completed transaction is to issue a new transaction, which is functionally a reversal of the previous transaction.

The commit tran syntax is as follows:

The copy code is as follows: commit tran [saction] [|]

Rollback tran

Rollback is a transaction rollback, and anything that happens from the associated begin statement is forgotten, that is, undoing all operations contained in the transaction. Except that SavePoint is allowed, the syntax of rollback looks the same as a begin or commit statement:

The copy code is as follows: rollback transaction [|]

Save tran

Saving a transaction is essentially creating a bookmark (bookmark). Create a name for the bookmark, and after you create the Bookmark, you can reference it in the rollback. The advantage of bookmarking is that you can roll back to a specific point in your code-just name the SavePoint you want to roll back to.

The syntax is as follows:

The copy code is as follows: save tran [saction] [|]

SqlServer transaction instance

The structure of the UserInfo table is as follows:

Transaction code 1:

Begin tran tran_AddUserInfo-start transaction declare @ tran_error int;set @ tran_error=0 Begin try insert into dbo.UserInfo values (2016009 recordings of aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -- end catchif (@ tran_error > 0) begin rollback tran tran_AddUserInfo; can be executed normally with or without semicolon-- execution error, rollback transaction (specified transaction name) print @ tran_error;endelsebegin commit tran tran_AddUserInfo;-- No exception, commit transaction (specified transaction name) print @ tran_error;end

Transaction Code 2:

Begin tran tran_AddUserInfo-start transaction declare @ tran_error int;set @ tran_error=0 Begin try insert into dbo.UserInfo values (2016009 recordings aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa -- end catchif (@ tran_error > 0) begin rollback tran; can be executed normally with or without semicolon-execution error, rollback transaction (without specifying transaction name) print @ tran_error;endelsebegin commit tran;-without exception, commit transaction (without specifying transaction name) print @ tran_error;end

C # background code spelling Sql transaction statements

Public partial class TestSqlTran: System.Web.UI.Page {protected void Page_Load (object sender, EventArgs e) {if (! IsPostBack) {Execute ();}} private void Execute () {string connString = ConfigurationManager.ConnectionStrings ["connString"] .ToString (); SqlConnection connection = new SqlConnection (connString); StringBuilder sqlSB=new StringBuilder () / * sqlSB.AppendLine ("begin tran tran_handle") is not the same thing as line wrapping in SqlServer. Each line of Sql statement in the C# background must be separated by a space, and sqlSB.AppendLine ("begin tran tran_handle") cannot be used instead of sqlSB.Append ("begin tran tran_handle") * / sqlSB.Append ("begin tran tran_handle"); sqlSB.AppendFormat ("declare {0} int;set {0} = 0;", "@ tran_error") SqlSB.Append ("begin try"); sqlSB.AppendFormat ("delete from Descriptions where Id=' {0}'", "1"); sqlSB.Append ("end try"); sqlSB.Append ("begin catch"); / / set @ tran_error=@tran_error+1; with a semicolon ending without a space sqlSB.Append ("set @ tran_error=@tran_error+1;"); sqlSB.Append ("end catch") SqlSB.Append ("if (@ tran_error > 0) begin rollback tran; end"); sqlSB.Append ("else begin commit tran; end"); SqlCommand cmd=new SqlCommand (sqlSB.ToString (), connection); connection.Open (); int count = cmd.ExecuteNonQuery (); connection.Close () }} this is the end of the case analysis on the syntax and usage of Sql Server transactions. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.