SQL Server transaction and error handling

SQL Server transaction and error handling

Transactions

A transaction combines a series of tasks into an execution unit. Each transaction starts with a specific task and ends with a specific task. When all tasks are successful, the transaction fails. Therefore, a transaction has only two results: failure or success.
You can use the following command to combine more than two T-SQL statements into a transaction:
Begin transaction
Rollback transaction
Commit transaction
If any errors occur in a group of commands, you must use rollback to cancel the execution. If all the statements in a transaction are successfully executed in sequence, all the changes need to be recorded in the database: commit to the database.

Use pubs

Declare @ interrorcode int

Begin tran

Update authors

Set phone = '2014 354-9866'

Where au_id = '2017-80-9391'

Select @ interrorcode = @ Error

If (@ interrorcode <> 0) goto Problem

Update publishers

Set city = 'calcutta ', Country = 'India'

Where pub_id = '20140901'

Select @ interrorcode = @ Error

If (@ interrorcode <> 0) goto Problem

Commit tran

Problem:

If (@ interrorcode <> 0) begin

Print 'unexpected error occurred! '

Rollback tran

End

Before the actual processing starts, use the tran in TRAN flag: All the following actions as a transaction. It contains two update statements. If no error occurs, the stored procedure is complete when all changes made to commit Tran are committed to the database. If an error occurs during update, check @ error to jump to the problem tag, display the error message, and roll back all the modifications during this period.
Note: Make sure that begin Tran and commit or rollback appear in pairs.

Nested transactions

SQL Server allows you to nest transactions. This feature means that a new transaction can be started even if the previous transaction is not completed. The T-SQL allows you to nest in TRAN nested transactions. The built-in variable @ trancount shows the nesting level of the transaction. 0 indicates no nesting, 1 indicates a nested layer, and so on.

Commit commits local transactions, but all changes are saved to the disk until the transaction at the outermost layer is committed. It only reduces the value of @ trancount. Rollback can roll back all transactions at that layer.
When you start a transaction, the @ trancount variable is automatically increased from 0 to 1; when the number of commits is reduced by 1. When rollback is performed, the number is reduced to 0. As you can see, commit and rollback are not symmetric. If you have nested transactions, the commit layer is less than 1, 1. The rollback command rolls back all transactions 2. The difference between commit and rollback lies in nested error handling.

Figure 1: A commit corresponds to a begintransaction, reducing the number of @ trancount by 1.

Figure 2: rollback always rolls back the entire transaction

As shown in figure 1 and figure 2, you can use @ trancount to detect nested transactions. You can also understand the asymmetry between commit and rollback. Commit reduces the value of @ trancount, and rollback changes the value to 0. Only the last commit can actually commit the transaction. No matter how many transactions you nest, only the last commit takes effect.

Use pubs

Select 'before Tran in TRAN ', @ trancount -- @ trancount: 0

Begin tran

Select 'after begin Tran ', @ trancount -- @ trancount: 1

Delete sales

Begin Tran nested

Select 'after begin Tran nested ', @ trancount

-- @ Trancount: 2

Delete titleauthor

Commit Tran nested

-- The value of @ trancount is reduced and nothing is done.

Select 'after commit Tran nested ', @ trancount

-- @ Trancount: 1

Rollback tran

Select 'after rollback Tran ', @ trancount -- @ trancount: 0

-- Because rollback Tran rolls back the entire transaction. @ Trancount is set to 0.

Select top 5 au_id from titleauthor

Use pubs

Select 'before Tran in TRAN ', @ trancount -- @ trancount: 0

Begin tran

Select 'after begin Tran ', @ trancount -- @ trancount: 1

Delete sales

Begin Tran nested

Select 'after begin Tran nested ', @ trancount

-- @ Trancount: 2

Delete titleauthor

Rollback tran

Select 'after commit Tran nested ', @ trancount

-- @ Trancount: 0 because rollback Tran rolls back the entire transaction. @ Trancount is set to 0.

If (@ trancount> 0) begin

Commit tran -- it will never be executed here because the transaction is rolled back.

Select 'after commit Tran ', @ trancount

End

Select top 5 au_id from titleauthor

In this example, commit Tran is never executed because rollback Tran rolls back all transactions. Unless rollback Tran is called with a save point, rollback Tran rolls back all transactions and sets @ trancount to 0.

Save Tran and save point

The storage point provides a separate rollback Mechanism for the Department. You can set the save point or mark within the transaction. The storage point defines a rollback position when the transaction is canceled. SQL Server

You can use the Save Tran statement to define a save point. Declaring that the save point does not affect the value of @ trancount, and rollback to the Save point does not affect @ trancount. Rollback must contain the name of the storage point in rollback Tran; otherwise, the entire transaction will be rolled back.

Use pubs

Select 'before begin Tran main', @ trancount

-- @ Trancount: 0

Begin Tran main

Select 'after begin Tran main', @ trancount

-- @ Trancount: 1

Delete sales

Save Tran sales -- set a save point

Select 'after save Tran sale', @ trancount

-- @ Trancount the value is still 1

Begin Tran nested

Select 'after begin Tran nested ', @ trancount

-- @ Trancount: 2

Delete titleauthor

Save Tran titleauthor -- mark a save point

Select 'after save Tran titleauthor', @ trancount

-- @ Trancount is still 2

Rollback Tran sales

Select 'after rollback Tran sale', @ trancount

-- @ Trancount is still 2

Select top 5 au_id from titleauthor

If (@ trancount> 0) begin

Rollback tran

Select 'after rollback Tran ', @ trancount

-- @ Trancount: 0 because rollback Tran rolls back the entire transaction. @ Trancount is set to 0.

End

Select top 5 au_id from titleauthor

Error Handling

The following example shows an idealized storage process for interacting with databases. What you can do when an error occurs is terminate the code execution sequence. Either route the code to another code

Or return to terminate the program. @ Error this system variable is often used for error handling. It contains the code for the last execution error of the SQL statement. When the code is successfully executed, the value of @ error is 0.
When the code execution is complete, use the if statement to determine the value of @ error immediately. This is because when the next statement is successfully executed, the value of @ error is reset to 0. Therefore, it must be captured immediately. After executing the nsert, update, or delete statement, you need to immediately test the change of the @ error value.

This solution has a lot of repetitive processing, especially when your business logic requires more than 2 T-SQL statements. A more elegant solution is to combine all the code and use a common error handling method.

Create procedure addtitle (@ title_id varchar (6), @ au_id varchar (11 ),

@ Title varchar (20), @ title_type char (12 ))

As

Begin tran

Insert titles (title_id, title, type)

Values (@ title_id, @ title, @ title_type)

If (@ error <> 0) begin

Print 'unexpected error occurred! '

Rollback tran

Return 1

End

Insert titleauthor (au_id, title_id)

Values (@ au_id, @ title_id)

If (@ error <> 0) begin

Print 'unexpected error occurred! '

Rollback tran

Return 1

End

Commit tran

Return 0