SQL Server transactions, exceptions, and cursors (1/3)

This tutorial is about SQL Server transactions, exceptions, and cursors. I hope it will help you.

1. Features of transactions

A transaction consists of several T-SQL commands, and all the commands are submitted to the database tutorial system one whole last night. When executed, these commands are either completed or all canceled. Therefore, a transaction is an inseparable logical unit.

 

Transactions have four attributes: Atomicity, Consistency, Isolation, and Durability. They are also called ACID attributes of transactions.

        Atomicity: All the work in the transaction is either completed or not completed, and there is no such situation.

        Consistency: The subsequent operations in the transaction cannot violate the database's subsequent constraints or rules. When the transaction is completed, the internal data structure must be correct.

        Isolation: Transactions are directly isolated from each other. If two transactions operate on the same database, such as reading table data. All content seen by any transaction is either in the status before other transactions are completed or after other transactions are completed. One transaction cannot encounter the intermediate state of another transaction.

        Durability: After a transaction is completed, its impact on the database system is persistent. Even if a system error occurs, the transaction result will still exist after the system is restarted.

 

2. Transaction mode

A. Display transactions

The display transaction is the start (begin transaction) and commit (commit transaction) or rollback transaction (rollback transaction) that the user uses the T-SQL to clearly define the transaction)

B. Automatically submit the transaction

An automatic commit transaction is a default transaction method for the T-SQL that can be automatically executed and rolled back automatically. For example, when deleting a table record, if the record has a primary foreign key relationship, the deletion will be affected by the primary foreign key constraint, and the deletion will be canceled.

You can set the transaction to enter the implicit mode: set implicit_transaction on;

C. Implicit transactions

Implicit transactions refer to SQL Server automatically starting a transaction after the transaction is committed or rolled back. Therefore, implicit transactions do not need to start with the begin transaction display, but simply commit the transaction directly unemployed or roll back the T-SQL statement of the transaction.

You need to set the set implicit_transaction on statement to enable the implicit transaction mode. The next statement starts a new transaction, and the next statement starts a new transaction.

 

3. Transaction processing

Commonly used T-SQL transaction statement:

A. begin transaction statement

Start the transaction, and the @ trancount global variable is used to record the number of transactions plus 1. You can use the @ error global variable to record the error information during execution. If there is no error, you can directly commit the transaction, rollback is supported if an error occurs.

B. commit transaction statement

Roll back the transaction, indicating the end of an implicit or explicit transaction, and the modifications made to the database take effect. And subtract the value of @ trancount from 1;

C. rollback transaction statement

Roll back the transaction. After the rollback tran statement is executed, the data will be rolled back to the begin tran status

 

4. Transaction example

-- Start transaction

  

   Begin transaction tran_bank;
   
Declare @ tran_error int;
   
Set @ tran_error = 0;
   
Begin try
   
Update bank set totalMoney = totalMoney-10000 where userName = 'Jack ';
   
Set @ tran_error = @ tran_error + @ error;
   
Update bank set totalMoney = totalMoney + 10000 where userName = 'Jason ';
   
Set @ tran_error = @ tran_error + @ error;
   
End try
   
Begin catch
   
Print 'exception, error number: '+ convert (varchar, error_number () +', error message: '+ error_message ();
   
Set @ tran_error = @ tran_error + 1;
   
End catch
   
If (@ tran_error> 0)
   
Begin
   
-- Execute an error and roll back the transaction
   
Rollback tran;
   
Print 'transfer failed, cancel transaction ';
   
End
   
Else
   
Begin
   
-- No exception. Submit the transaction.
   
Commit tran;
   
Print 'transfer successful ';
   
End
   
Go
  

 

Ø exception

In a program, errors and exceptions may occur when you complete some Transact-SQL statements. If you want to handle the exception information yourself, you need to manually capture the information. We can use try catch.

TRY... The CATCH structure consists of a TRY block and a CATCH block. If an error condition is detected in the Transact-SQL statement contained in the TRY block, the control is passed to the CATCH block (this error can be handled in this block ).

After the CATCH block handles this exception error, the control is passed to the first Transact-SQL statement after the END CATCH statement. If the end catch statement is the last statement in a stored procedure or trigger, the control will return the code that calls the stored procedure or trigger. Do not execute the Transact-SQL statement after the wrong statement is generated in the TRY block.

If there are no errors in the TRY block, the control will be passed to the statement followed by the associated end catch statement. If the end catch statement is the last statement in a stored procedure or trigger, the control is passed to the statement that calls the stored procedure or trigger.

The TRY block starts with the begin try statement and ends with the end try statement. You can specify one or more Transact-SQL statements between the TRY in TRY and END TRY statements. The CATCH block must be followed by the TRY block. The CATCH block starts with the begin catch statement and ends with the end catch statement. In Transact-SQL, each TRY block is associated with only one CATCH block.

# Error functions

  

   Homepage 1 2 3
   Last page