Execution of Stored Procedure timeout SQL

When executing a stored procedure, we often encounter execution timeout.

If there are too many data to be processed and the modification process is complicated, use the following methods to solve the problem:

Add transaction management to the processing of stored procedures:

SET transaction isolation level Repeatable read ---> need this line
Begin tran

/* Here isProgramProcessingCodeSegment */

Commit transaction
Quitwithrollback:
If (@ trancount> 0)
Begin
Rollback transaction
End

The following are transaction-related knowledge:

Begin transaction -- start transaction

Declare @ errorsun int -- defines the error counter

Set @ errorsun = 0 -- yes, it is 0.

Update a set id = 232 Where a = 1 -- SQL statement for transaction operations

Set @ errorsun = @ errorsun + @ error -- indicates whether the total number is incorrect.

Update AA Set ID = 2 Where a = 1 -- SQL statement for transaction operations

Set @ errorsun = @ errorsun + @ error -- indicates whether the total number is incorrect.

If @ errorsun <> 0 begin print 'error, rollback'

Rollback transaction -- transaction rollback statement

End else begin print 'success, submit'

Commit transaction -- transaction commit statement

End

1. What is a transaction: a transaction is an inseparable logical unit of work. It is used as the smallest control unit to execute concurrent operations on the database system. All the database operation commands contained in the database are submitted or withdrawn to the system as a whole. These database operation commands are either executed or not executed.

2. Transaction statement start transaction: Begin transaction commit transaction: commit transaction rollback transaction: rollback transaction

3. Four attributes of the transaction

① Atomicity: All elements in a transaction are committed or rolled back as a whole. The transaction elements are inseparable and the transaction is a complete operation.

② Consistemcy: when a transaction is completed, the data must be consistent, that is, the data stored in the data is in the same state before the transaction starts. Ensure data loss.

③ Isolation: multiple transactions that modify data are isolated from each other. This indicates that the transaction must be independent and should not affect other transactions in any way.

④ Durability: after the transaction is completed, its impact on the system is permanent. This modification will be retained even if a system failure occurs, and the database is actually modified.

4. transaction storage point save transaction storage point name -- custom storage point name and position rollback transaction storage point name -- rollback to custom storage point two examples the so-called transaction refers to a set of Logic operation Unit, it changes data from one state to another. It includes four features:

1. atomicity means that a transaction should be taken as a unit of work. After the transaction is processed, all the work is either saved in the database or completely rolled back.

2. Consistent transactions should be in the consistent state after they are completed or withdrawn.

3. Isolation of multiple transactions at the same time, and they should not interfere with each other. It should prevent a transaction from unreasonable access and incomplete data reading when processing data that other transactions also need to modify.

4. After a persistent transaction is committed, the work is permanently saved. For example, create a stored procedure and insert data into both tables.

Create proc registeruser

(

@ Usrname varchar (30 ),

@ Usrpasswd varchar (30 ),

@ Age int, @ sex varchar (10 ),

@ Phonenum varchar (20 ),

@ Address varchar (50 ))

As begin

Begin tran

Insert into userinfo (username, userpasswd)

Values (@ usrname, @ usrpasswd)

If @ error <> 0

Begin rollback tran

Return-1

End

Insert into userdoc (username, age, sex, phonenumber, address)

Values (@ usrname, @ age, @ sex, @ phonenum, @ address)

If @ error <> 0

Begin rollback tran

Return-1

End

Commit tran

Return 0

End

Transactions are classified into three categories:

The display transaction is also called a user-defined transaction or a user-specified transaction, that is, the start and end transactions can be explicitly defined. Distributed transactions are the default transaction management mode for displaying automatic transaction commit transactions. If a statement is successfully completed, the statement is submitted. If an error occurs, the statement is rolled back.

Implicit transactions when the connection operates in this mode, SQL automatically starts a new transaction after committing or rolling back the current transaction. You do not need to describe the start of a transaction. You only need to commit or roll back each transaction. It generates a continuous transaction chain.

1. The transaction is displayed through begin transacton, commit transaction, commit work, rollback transaction, or rollback work.

1. Start transaction format: Begin Tran transaction name or variable with Mark description

2. End transaction format: commit Tran transaction name or variable (the transaction name is consistent with the transaction name in the tran in TRAN or commit work, but there is no Parameter

3. roll back the rollback Tran transaction name or variable | savepoint_name | savepoint_variable or rollback Work Description: clears all data modifications made from the start point of the transaction or to a save point.

4. Set the save point format in the transaction: Save Tran savepoint_name | savepoint_variable. Example:

Use bookdb

Go

Begin Tran mytran

Insert into book values (9, "windows2000', 'Press ')

Save Tran mysave

Delete book where book_id = 9

Rollback Tran mysave

Commit tran

Go

Select * from book

Go

As you can know, after the preceding statement is executed, a record is inserted in the book, but not deleted. Because the rollback Tran mysave statement is used to roll back the operation to the Save point before deletion.

5. Mark the transaction format: with Mark. For example, use the database mark to set a flag for the statement to restore the log to a predefined time point in the transaction log. Note that at least one update must be submitted for the marked transaction to mark the log.

Begin Tran mymark with Mark update pubs. DBO. lastlogmark set marktime = getdate () Commit Tran mymark backs up transaction logs according to your common methods.

Backup log pubs to disk = 'C: \ Backups \ fullbackup. Bak' with init now you can restore the database to the log mark point. First, recover the database and make it ready to accept log recovery. Restore database pubs from disk = n'c: \ Backups \ fullbackup. Bak 'with norecovery now restores the log to a time point containing the mark and makes it available for use. Note that stopat cannot be executed when the database is executing large-capacity logs. Restore log pubs from disk = n'c: \ Backups \ logbackup. Bak' with recovery, stopat = '2017/24/60 17:35:00'

5. cannot be used for transaction operations create database create database modify Database alter database delete database drop database Restore database load database backup log file backup log restore Log File restore log update statistics update statitics authorization operation grant copy transaction log dump Tran disk initialization disk init update system configuration reconfigure after using sp_configure

II. The automatic commit transaction SQL connection is performed in the automatic commit mode before the begin Tran statement starts an explicit transaction or before the implicit transaction mode is set to open. When an explicit transaction is committed or rolled back, or the implicit transaction mode is closed, it is returned to the automatic commit mode. Example: The three insert statements are not executed due to a compilation error.

Use test

Go

Create Table testback (COLA int primary key, COLB char (3 ))

Go

Insert into testback values (1, 'aaa ')

Insert into testback values (2, 'bbb ')

Insert into testback value (3, 'ccc ')

Go

Select * From testback

Go does not return any results

Iii. Set the implicit transaction mode to open through an API function or a Transact-SQL set implicit_transactions on statement. The next statement automatically starts a new transaction. When the transaction is completed, another Transact-SQL statement starts a new transaction. When a large number of DDL and DML commands are executed, the transaction starts automatically until the user explicitly commits the transaction. to switch between implicit transactions, you can use set implicit_transactions to set the implicit transaction mode for the connection. when set to on, set implicit_transactions sets the connection to implicit transaction mode. When it is set to off, the statement that causes the connection to return to the automatic commit transaction mode includes: alter table insert open create Delete revoke drop select fetch truncate table grant update example: explicit and implicit transactions are used below. It uses the @ tracount function to demonstrate opened transactions and closed transactions:

Use test

Go

Set nocount on

Create Table T1 (a int)

Go

Insert into T1 values (1)

Go

Print 'use explicit transactions'

Begin tran

Insert into T1 values (2)

Print 'number of external transactions: '+ Cast (@ trancount as char (5 ))

Commint tran

Print 'number of external transactions: '+ Cast (@ trancount as char (5 ))

Go

Print

Go

Set implicit_transactions on go print 'use implicit transaction'

Go

Insert into T1 values * 4)

Print 'number of transactions in the transaction: '+ Cast (@ trancount as char (5 ))

Commint Tran print 'number of external transactions: '+ Cast (@ trancount as char (5 ))

Go

Execution result: display the number of transactions in the transaction: 2 Number of transactions outside the transaction: 1 Number of transactions in the implicit transaction: 1 Number of transactions outside the transaction: 0

4. distributed transactions are distributed transactions in a single SQL server that spans two or more databases. Difference from local transactions: it must be managed by the Transaction Manager to avoid situations where a transaction is successfully submitted by some resource managers due to a network failure but rolled back by other resource managers. SQL Server supports distributed transactions by DTC Microsoft Distributed Transaction Coordinator. You can use the begin Distributed Transaction command to start a Distributed Transaction in two phases:

A preparation phase B submission phase

Tutorial:

1. SQL scripts or applications are connected to execute SQL statements for starting distributed transactions

2. the SQL statement is executed on the master server of the transaction.

3. scripts or applications perform distributed queries on linked servers, or execute remote stored procedures on remote servers.

4. After a distributed query or remote process call is executed, the master server automatically calls MSDTC to register the servers and remote servers linked to the distributed transaction.

5. When a script or application sends a commit or rollback statement, the master SQL will call the two-phase commit process of MSDTC management, or notify the linked server and remote server to roll back the transaction.

From: http://blog.csdn.net/zhupt/archive/2009/02/09/3870195.aspx