Analysis of the stored procedures of transactions and SQL stored procedures in SQL Server

Analysis of the stored procedures of transactions and SQL stored procedures in SQL Server

Let's look at a concept:

Database Transaction refers to a series of operations performed as a single logical unit of work, either completely or completely. Then, adding a transaction to the stored procedure ensures that all SQL code in the transaction is either fully executed or completely not executed.

A simple stored procedure with transactions:

Begin Set nocount on; -- Set XACT_ABORT ON with no impact ON the number of rows returned; -- The XACT_ABORT parameter must be enabled to execute transactions using stored procedures (the default value is OFF) delete from table1 where name = ''-- delete data sql1 begin tran tran1 -- start a transaction tran1 delete from table1 where name ='' -- delete data sql2 save tran tran2 -- save a transaction point tran2 update table2 set name = ''where id = ''-- modify data sql3 if @ error <> 0 -- determine whether the data is modified incorrectly (@ error indicates that the returned data and @ ERROR are returned. recent Statement (sql3) non-zero error code. If there is no error, 0 is returned) begin rollback tran tran2 -- roll back the transaction to the Restore Point of tran2 commit tran tran1 -- commit the transaction tran1 end else -- if no error occurs, commit the transaction tran1 commit tran tran1 -- commit the transaction tran1 End

If sql3 fails to be executed, it will roll back to the creation of the transaction tran2 (equivalent to not executing both sql1 and sql2 ).

SQL Server Stored Procedure transaction lock

In your case above, the shared lock of the select statement is released as a result and will not be retained in the transaction.
The U Lock used by update and its further X lock must last until the end of the transaction.
If it is a multi-threaded program, the lock wait may occur at both select and update. This depends on whether the data in the actual operation conflicts.

What is the SQL stored procedure?

The SQL stored procedure is simply a function that can be defined by the user under t-SQL,
However, it is different from a common function. For example, its RETURN value can only be RETURN (INT type). If you want to OUTPUT any information, you can only use OUTPUT. this is also a feature of stored procedures. The set parameters can be output. It's a little abstract. Let's take a look at this example!
First, create a stored procedure
Create procedure cunchuguocheng
@ A int,
@ B int,
@ C int output
As
Begin
Select @ c = @ a + @ B
Return (0)
End
Then call this stored procedure
Declare @ value int, -- Return value
@ C int -- result value
Exec @ value = cunchuguocheng 2, 2, @ c output
Select @ value as return value
Select @ c as result value

The program is easy to write. After you run it, I think you will understand the stored procedure.