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 a thing: Commit TRANSACTION
ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

3.4 Features of a transaction
① atomicity (atomicity): All elements in a transaction are committed or rolled back as a whole, are non-folded, 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.
Transactions can be divided into 3 classes by how they are started and executed:
① Display transactions: Also known as user-defined or user-specified transactions, you can explicitly define start and end transactions. Distributed transactions belong to display transactions
② Auto COMMIT TRANSACTION: 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 the connection is operating 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, submit '
Commit transaction--Transaction COMMIT statement
END

6. Operations that cannot be used for transactions
Create Database
Modify Database Alter
Delete database drop databases
Recovering a database restore
Load Database load
Backing up log files backup logs
Restore Log Files Restore logs
Updating statistics update Statitics
Authorization Action Grant
Copy transaction log Dump Tran
DISK INIT disks Initialization
Update the system configuration after using sp_configure reconfigure

Second, the stored procedure

1. The advantages of the stored procedure
(1) can be implemented in 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 program needs to access the data on the server, there are generally 5 steps:
  query statements are sent to the server,
  Server compiles T-SQL statements,
  optimizations generate query execution plans;
  The database engine executes the query, and the
  executes the results back to the client.
If you execute a T-SQL program that is stored locally on the client, each time you execute the program, you go through the 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) reduces 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. The type of stored procedure
(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
Extended stored procedures are external programs that exist 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 of the master database, otherwise, you must precede the extended stored procedure with "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. The
(3) User stored procedure
User stored procedure is a stored procedure that is created by the user based on 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.