Example Analysis of Sql Server Transaction Grammar and Usage Method

Time:2019-7-8

This paper gives an example of Sql Server transaction grammar and its usage. Share for your reference, as follows:

Transactions are about atomicity. The concept of atomicity means that something can be viewed as an inseparable unit. From the point of view of database, it refers to the minimal combination of one or more statements that should be executed completely or not.

In order to understand the concept of transactions, it is necessary to be able to define very clear boundaries. Transactions should have very clear starting and ending points. Each 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 all the contents of the execution statement or nothing will be executed. But what if you need more than one statement? 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. Some T-SQL statements can be used to “mark” these points in a transaction.

  • Begin tran: Set the starting point
  • Commit tran: Make transactions a permanent, irreversible part of the database
  • Rollback tran: essentially trying to forget that it happened
  • Save tran: Create a specific tag that allows only partial rollbacks

begin tran

The beginning of a transaction may be the easiest concept to understand in the process of things. Its sole purpose is to represent the beginning of a unit. If for some reason you can’t or don’t want to commit a transaction, that’s the starting point for all database activities to roll back. That is to say, the database ignores all statements that are not submitted after this starting point.

The grammar is as follows:

Copy codeThe code is as follows:
begin tran[saction] [<transaction name>|<@transaction variable>][with mark[<‘description’>]]

commit tran

Transaction submission is the end of a transaction. When a commit tran command is issued, the transaction can be considered complete. That is to say, 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 backup or database files are not physically damaged). The only way to undo a completed transaction is to issue a new transaction, which functionally reverses the previous transaction.

The commit tran grammar is as follows:

Copy codeThe code is as follows:
commit tran[saction][<transaction name>|<@transaction variable>]

rollback tran

Rollback is a transaction rollback, and anything that happens from the associated start statement will be forgotten, that is, all operations contained in the transaction will be revoked. In addition to allowing savepoints, the rollback syntax looks the same as the begin or commit statement:

Copy codeThe code is as follows:
rollback tran[saction][<transaction name>|<save point name>|<@transaction variable>|<@savepoint variable>]

save tran

Saving transactions is essentially creating bookmarks. Create a name for the bookmark, which can be referenced in the rollback after the “bookmark” is created. The advantage of creating bookmarks is that you can roll back to a specific point in your code – just name the savepoint you want to roll back to.

The grammar is as follows:

Copy codeThe code is as follows:
save tran[saction][<save point name>|<@savepoint variable>]

SqlServer transaction instance

The UserInfo table structure is shown as follows:

 

Transaction code 1:

Begin tran_AddUserInfo -- Start a transaction
declare @tran_error int;
set @tran_error=0;
begin try
 Insert into dbo. UserInfo values (2016009,'aaa','2016-08-19 09:13:41.227','male')
 Insert into dbo. UserInfo values (2016009,'bbb','2016-08-19 09:13:41.227','Hum')
 Insert into dbo. UserInfo values (2016009,'ccc','2016-08-19 09:13:41.227','haha')
end try
begin catch
 Set @[email protected]_error+1; -- With or without semicolons
end catch
if(@tran_error>0)
begin
 Rollback tran_AddUserInfo; -- Error execution, rollback transaction (specified transaction name)
 print @tran_error;
end
else
begin
 Commit tran tran_AddUserInfo; -- No exceptions, commit the transaction (specify the transaction name)
 print @tran_error;
end

Transaction code 2:

Begin tran_AddUserInfo -- Start a transaction
declare @tran_error int;
set @tran_error=0;
begin try
 Insert into dbo. UserInfo values (2016009,'aaa','2016-08-19 09:13:41.227','male')
 Insert into dbo. UserInfo values (2016009,'bbb','2016-08-19 09:13:41.227','Haha')
 Insert into dbo. UserInfo values (2016009,'ccc','2016-08-19 09:13:41.227','Haha')
end try
begin catch
 Set @[email protected]_error+1; -- With or without semicolons
end catch
if(@tran_error>0)
begin
 Rollback tran; -- Error execution, rollback transaction (no transaction name specified)
 print @tran_error;
end
else
begin
 Commit tran; -- no exceptions, commit transactions (no transaction name specified)
 print @tran_error;
end

C# Background Code Spelling Sql Transaction Statement

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();
    /* The sqlSB.AppendLine ("begin tran_handle") is not the same as the newline in SqlServer.
     C# Background Sql statements must be separated by spaces.
     SqlSB. AppendLine ("begin tran_handle") cannot be used to replace sqlSB. Append ("begin 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 @[email protected]_error+1; you can end with a semicolon without spaces
    sqlSB.Append("set @[email protected]_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();
  }
}

More readers interested in the related content of SQL Server can see the topics of this site: SQL Server Query Operating Skills Complete, Summary of SQL Server Date and Time Operating Skills, SQL Server Stored Procedure Skills Complete, SQL Server Index Operating Skills Complete and Summary of Common Functions of SQL Server.

I hope this article will be helpful to the design of SQL Server database program.