Some summary sharing of SQLTranscation

I believe that everyone is familiar with SQL Transcation, which ensures the data consistency and security of the database, especially when adding or deleting data, if an exception or error occurs, it will trigger a transaction rollback. This ensures the consistency and security of our data. Below we will introduce the event (Transcation) in four parts

1.1.1 Summary
I believe that everyone is familiar with SQL Transcation, which ensures the data consistency and security of the database, especially when adding or deleting data, if an exception or error occurs, it will trigger a transaction rollback, thus To ensure the consistency and security of our data, we will introduce the event (Transcation) in four parts.

1.1.2 Text
First, let us introduce the use of Transcation through a specific example. If there is a table UserInfo in our database, it contains three fields: UserID (auto-increment), UserName (nvarchar) and LuckyNumber (tinyint), as shown below:


Figure 1 UserInfo table


The sql code of UserInfo table is as follows:
The code is as follows:
— The definition of UserInfo.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserInfo](
[UserID] [ int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[LuckyNumber] [tinyint] NOT NULL
) ON [PRIMARY] Next we want to Insert into the table UserInfo, here use a stored procedure to insert data into the table, the definition of the stored procedure SPAddDataToUserInfo is as follows:
The code is as follows:
— ============= ====================================
— Author: JKhuang
— Create date: 12/ 8/2011
— Description: Inserts data
— ==================================== ============
CREATE PROCEDURE SPAddDataToUserInfo

AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— Hard code inserted data.
INSERT INTO UserInfo VALUES(‘JKhuang’, 8);
INSERT INTO UserInfo VALUES(‘JKRush’ , 23);
INSERT INTO UserInfo VALUES(‘Jackson’, 20111111);
END
GO Now that we have defined a stored procedure, let’s execute it.

Figure 2 The message of executing the stored procedure


Through the above figure, we know that an exception occurred during the execution of the stored procedure, and it is because the value “20111111” data exceeds the range of tinyint Generated, now let’s look at the data insertion situation.

Figure 3 Data in the UserInfo table

We found that only two rows of data were inserted, and the third row of data was not inserted successfully, but in order to ensure data integrity, we have to insert all the data Or do not insert at all, then we can consider using Transcation to ensure data integrity and security.
Then let us modify the stored procedure SPAddDataToUserInfo and add Transcation in the stored procedure.
The code is as follows:
— =========================================== ======
— Author: JKhuang
— Create date: 12/8/2011
— Description: Inserts data
— ========= ========================================
Alter PROCEDURE SPAddDataToUserInfo
AS
BEGIN
BEGIN TRANSACTION
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Hard code inserted data.
INSERT INTO UserInfo VALUES(‘JKhuang’, 8);
INSERT INTO UserInfo VALUES(‘Jackson’, 20111111);
INSERT INTO UserInfo VALUES(‘JKRush’, 23);
COMMIT TRANSACTION
END
GO Now we execute the stored procedure again to see if all are not inserted into the table.

Figure 4 Data in the UserInfo table

We found that the result is the same as that without Transcation processing, and the data is still inserted into the table. What is the reason? Maybe you have noticed carefully that we did not add transaction rollback – ROLLBACK.
But where do we add transaction rollback (ROLLBACK)? Or more specifically: “When exactly do we trigger a transaction rollback (ROLLBACK)”?
Since the failure of our data insertion is due to an abnormal situation in the insertion process, then we have to catch and handle the exception, that is the design of TRY/CATCH, so let us continue to improve our stored procedure.
The code is as follows:
— =========================================== ======
— Author: JKhuang
— Create date: 12/8/2011
— Description: Inserts data
— ========= ========================================
Alter PROCEDURE SPAddDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Hard code inserted data.
INSERT INTO UserInfo VALUES(‘JKhuang’, 8);
INSERT INTO UserInfo VALUES(‘Jackson’, 20111111);
INSERT INTO UserInfo VALUES(‘JKRush’, 23);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO

Now we add an exception handling mechanism to the stored procedure TRY /CATCH (Note: SQLSERVER 2005 or later versions only support TBEGIN TRANSACTION
PRINT ‘In [SPAddDataToUserInfo] Transactions: ‘ + Convert(varchar, @@TRANCOUNT);
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
–SET NOCOUNT ON;
— Hard code inserted data.
INSERT INTO UserInfo VALUES(‘JKhuang’, 8);
INSERT INTO UserInfo VALUES(‘Jackson’, 20111111);
INSERT INTO UserInfo VALUES(‘JKRush’, 23);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT ‘Error in [SPAddDataToUserInfo]: ‘ + ERROR_MESSAGE();
IF ( @@TRANCOUNT > 0)
ROLLBACK TRANSACTION
PRINT ‘Rolled back successful in SPAddDataToUserInfo Transactions: ‘ + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO
— ================================================
— Author: JKhuang
— Create date: 12/8/2011
— Description: Invokes store procedure to insert data.
— ============= ===================================
ALTER PROCEDURE SPMultiDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
PRINT ‘In [SPMultiDataToUserInfo] Transactions: ‘ + Convert(varchar, @@TRANCOUNT);
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
–SET NOCOUNT ON;
— Hard code inserted data.
INSERT INTO UserInfo VALUES(‘Cris’, 1);
EXEC SPAddDataToUserInfo
INSERT INTO UserInfo VALUES( ‘Ada’, 32);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT ‘Error in [SPMultiDataToUserInfo]: ‘ + ERROR_MESSAGE();
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
PRINT ‘Rolled back successful in SPMultiDataToUserInfo Transactions: ‘ + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO Now we add the judgment of the transaction counter, When the counter is 0, no transaction rollback is performed, so there is no previous transaction counter exception.

Figure 9 Stored procedure execution message

Now we have a better understanding of the transaction, and define the above transaction as a daily general template, a basic Transaction template is given as follows .

The code is as follows:
— ========================================= ==========
— Transaction Temp
— ================================= =================
BEGIN TRY
BEGIN TRANSACTION

— You code here.

COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
— Adds store procedure
— Writes the error into ErrorLog table.
ROLLBACK TRANSACTION
END IF
END CATCH

1.1.3 Summary
A transaction is a sequence of operations performed as a single logical unit of work. It can be one SQL statement or multiple SQL statements.
Transactions have four characteristics.
Atomicity: inseparable, success is success, and failure is failure.
Consistency: When a transaction is completed, all data must be kept in a consistent state.
Isolation: Independent execution does not interfere with each other. Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transactions.
Persistence: After the task is completed, its impact on the system is permanent. This modification will persist even in the event of a system failure.
The application mainly controls the transaction by specifying the time when the transaction starts and ends.
Start a transaction: Using API functions and Transact-SQL statements, you can start a transaction explicitly, automatically or implicitly.
End Transaction: You can use the COMMIT (success) or ROLLBACK (failure) statement, or through the API function to end the transaction.
The transaction mode is divided into: display transaction mode, implicit transaction mode, automatic transaction mode. Commonly used in SQL is the display mode.
The principle of creating a transaction:
It is very important to keep the transaction as short as possible. When the transaction is started, the database management system (DBMS) must reserve a lot of resources before the transaction ends to ensure the correct and safe execution of the transaction.
Especially in a large number of concurrent systems, it is more important to keep transactions short to reduce concurrent resource lock contention.
1. Transaction processing, prohibiting interaction with users, and completing user input before the transaction starts.
2. When browsing the data, try not to open the transaction.
3. Keep the transaction as short as possible.
4. Consider using snapshot isolation for read-only queries to reduce blocking.
5. Use a lower transaction isolation level flexibly.
6. Flexible use of lower cursor concurrency options, such as optimistic concurrency options.
7. Try to minimize the amount of data accessed in the transaction.

This article is from the internet and does not represent1024programmerPosition, please indicate the source when reprinting:https://www.1024programmer.com/some-summary-sharing-of-sqltranscation/

author: admin

Previous article
Next article

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact Us

Contact us

181-3619-1160

Online consultation: QQ交谈

E-mail: 34331943@QQ.com

Working hours: Monday to Friday, 9:00-17:30, holidays off

Follow wechat
Scan wechat and follow us

Scan wechat and follow us

Follow Weibo
Back to top
首页
微信
电话
搜索