SQL transactions and stored procedures

One, SQL transaction

1. What is a transaction: a transaction is an inseparable unit of work that is used as the smallest control unit when concurrent operations are performed on the database system. All of the database operations commands that he contains are either committed or revoked as a whole, and the set of database operations commands are executed or not executed.

2. Statement of the transaction
start things: Begin TRANSACTION
Commit things: Commit TRANSACTION
ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

3.4 attributes of a transaction
 ① atomicity (atomicity): All elements in a transaction are committed or rolled back as a whole, are non-discounted, and the transaction is a complete operation.
 ② Consistency (CONSISTEMCY): When things are done, the data must be consistent, that is, the data in the data store is in a consistent state before the things begin. Ensure that data is lossless.
 ③ Isolation (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.
 ④ Persistence (durability): After a transaction is complete, its effect on the system is permanent, and the modification persists even if a system failure occurs, and the database is actually modified

4. Classification of transactions.
by transaction initiation and execution, transactions can be divided into 3 classes:
 ① display transactions: Also known as user-defined or user-specified transactions, that is, you can explicitly define start-up and end transactions. The distributed transaction belongs to the display transaction
 ② autocommit transaction: The default transaction management mode. If a statement completes successfully, the statement is committed, and if an error is encountered, the statement is rolled back.
 ③ Implicit transaction: When a connection operates in this mode, SQL will automatically start a new transaction after committing or rolling back the current transaction. You do not need to describe the start of a transaction, simply commit or roll back each transaction. It generates a continuous chain of transactions.

5. Example
Begin transaction--Start Transaction

DECLARE @errorSun INT--Define error counters
SET @errorSun = 0--yes, 0.

UPDATE a SET id=232 WHERE a=1--transaction operation SQL statement
SET @[email Protected][email protected] @ERROR--whether the accumulation is wrong

UPDATE aa SET id=2 WHERE a=1--transaction operation SQL statement
SET @[email Protected][email protected] @ERROR--whether the accumulation is wrong

if @errorSun <>0
BEGIN
PRINT ' ERROR, rollback '
ROLLBACK transaction--transaction rollback statement
END
ELSE
BEGIN
PRINT ' success, commit '
Commit transaction--Transaction commit statement
END

6, operations that cannot be used for transactions
to create a database.
Modify database alter databases
Delete databases drop database
Recover databases Restore Database
Load database
Back up log files backup logs
Recovery log files Restore logs
Update STATISTICS updates statitics
Licensing actions                grant
Copy transaction log dump Tran
Disk initialization             DISK INIT
Update the system configuration after using sp_configure             reconfigure

Second, the stored procedure

1. Advantages of stored Procedures
(1) can achieve modular programming. A stored procedure is a program module that is created according to the needs of the actual functionality and is stored in the database. In the future, the user will have to complete this function, simply call the stored procedure directly in the program, without having to write duplicate program code. Stored procedures can be created by specialized personnel in the field of database programming and can be modified and extended independently of the source code of the program.
(2) Using stored procedures can improve execution efficiency. When the client needs to access the data on the server, it typically takes 5 steps:
Query statements are sent to the server;
The server compiles T-SQL statements;
Optimize the generation of query execution plan;
The database engine executes the query;
The execution results are sent back to the client program.
If you execute a T-SQL program that is stored locally on the client, each time you execute the program, you go through the above 5 steps for each statement in the program. The stored procedure is compiled and optimized when it is created, and when the stored procedure is first executed, SQL Server generates a query plan for it and saves it in memory so that it does not have to be compiled at a later time when the stored procedure is called, that is, the 2nd and 3rd steps in the above 5 steps are omitted. This can greatly improve the performance of the system.
(3) Reduce network traffic. An operation that requires hundreds of lines of T-SQL code, and if it is created as a stored procedure, the operation can be done using a statement that invokes the stored procedure. This avoids sending hundreds of of lines of code on the network, thus reducing network load.
(4) can be used as a security mechanism. Administrators can grant users access to the tables involved in the stored procedure without granting them permission to execute the stored procedure. This ensures that the user can manipulate the data in the database through the stored procedure and that the user cannot directly access the tables involved in the stored procedure. The user accesses the table through the stored procedure, and the operation can be limited, thus guaranteeing the security of the data in the table.

2. Types of stored Procedures
(1) System stored procedure
Many of the administrative work in SQL Server is done by executing system stored procedures. System stored procedures are created and saved in the master database and are prefixed with the name sp_. A system stored procedure is a SQL Server system that comes with users who perform system stored procedure permissions and can be called directly outside of the master database. In general, the system stored procedure execution successfully returns a value of 0 and returns a value other than 0 if an error occurs.
(2) Extended stored procedures
An extended stored procedure is an external program that exists in the form of a dynamic-link library (DLL). SQL Server itself is installed in the master database with a large number of extended stored procedures, and the extended stored procedure executes the same way as normal stored procedures.
If the extended stored procedure is prefixed with sp_, the extended stored procedure can also be called directly outside the master database, otherwise the extended stored procedure must precede the "master.dbo." Prefix. Developers can use other programming languages to create extended stored procedures, and after they have written an extended stored procedure, they can be registered by members of the sysadmin server role in SQL Server to register the extended stored procedure, and then grant other users permission to perform the procedure. Extended stored procedures can only be added to the master database, with extended stored procedures that extend the functionality of SQL Server.
(3) User stored procedure
A user stored procedure is a stored procedure that is created by the user according to the needs of the actual problem. Fixed server role members of the sysadmin can create user stored procedures in the master database as needed, and if you use sp_ as a prefix for stored procedures, the stored procedure can be called directly anywhere, otherwise you must precede the stored procedure with "master.dbo." Prefix. For a stored procedure created in a user database, it is best not to use sp_ as the prefix for its name, or the stored procedure will never be executed if the stored procedure has the same name as the system stored procedure. And if you call the stored procedure outside of the user database, you must also precede the stored procedure name with the user database name. Owner name. Prefix to find and execute the stored procedure.

SQL transactions and stored procedures