SQL Transcation Summary

1.1.1 Summary
I believe you are not familiar with SQL Transcation. It ensures data consistency and security of the database. Especially when adding or deleting data, if exceptions or errors occur, it triggers transaction rollback, this ensures data consistency and security. Next we will introduce the event in four parts ).

1.1.2 text
First, let's introduce the use of Transcation through a specific example. Assume that our database has a table UserInfo, which contains three fields: UserID (auto-increment) and UserName (nvarchar) and LuckyNumber (tinyint), as shown in:

Figure 1 UserInfo table

The SQL code for the UserInfo table is as follows:Copy codeThe 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 will insert the data into the table UserInfo. Here we use a stored procedure to insert the data into the table. The Stored Procedure SPAddDataToUserInfo is defined as follows:Copy codeThe 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 ('jkhuangt', 8 );
Insert into UserInfo VALUES ('jkrush', 23 );
Insert into UserInfo VALUES ('Jackson ', 20111111 );
END
GO

Now we have defined a stored procedure, and then let's execute it.

Figure 2 message for executing a stored procedure

We know that an exception occurred during the execution of the stored procedure, and the value "20111111" is generated because the data exceeds the tinyint range. Now let's take a look at the data insertion situation.

Figure 3 data in the UserInfo table

We found that only two rows of data were inserted, but the third row of data was not successfully inserted. To ensure data integrity, we need to insert all or all of the data, in this case, we can consider using Transcation to ensure data integrity and security.
Next, let's modify the Stored Procedure SPAddDataToUserInfo and add Transcation to the stored procedure.Copy codeThe 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 ('jkhuangt', 8 );
Insert into UserInfo VALUES ('Jackson ', 20111111 );
Insert into UserInfo VALUES ('jkrush', 23 );
COMMIT TRANSACTION
END
GO

Now we will execute the stored procedure again to see if all the data is not inserted into the table.

Figure 4 data in the UserInfo table

We found that the data is still inserted into the table as if Transcation was not added. What is the reason? You may have noticed that we have not added transaction ROLLBACK -- ROLLBACK.
But where can we add transaction ROLLBACK? Or, more specifically, "When should we trigger transaction ROLLBACK "?
Because data insertion fails because of exceptions in the insertion process, we need to capture exceptions and handle exceptions, that is, TRY/CATCH design, so that we can continue to improve our stored procedures.Copy codeThe 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 ('jkhuangt', 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 have added the TRY/CATCH mechanism for the stored procedure (Note: TRY 2005 or later versions support TRY/CATCH). Next let's execute the stored procedure again.
Figure 5 data in the UserInfo table

It seems that we have solved the problem. We know that stored procedures can be embedded with stored procedures or functions. Therefore, we embed the Stored Procedure SPAddDataToUserInfo into the Stored Procedure SPMultiDataToUserInfo. The definition of SPMultiDataToUserInfo is as follows:Copy codeThe Code is as follows: -- ===================================================== ======
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Invokes store procedure to insert data.
-- ===================================================== ======
Create procedure SPMultiDataToUserInfo

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 ('cris ', 1 );
EXEC SPAddDataToUserInfo
Insert into UserInfo VALUES ('ada ', 32 );

COMMIT TRANSACTION

END TRY
BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

END
GO

We know that the Stored Procedure SPAddDataToUserInfo will have an exception and it will roll back the transaction (ROLLBACK). But does SPMultiDataToUserInfo roll back or continue to insert data?

Figure 6 execution of Stored Procedure messages

Figure 7 data in the UserInfo table

An exception occurred during the Insert Process. Check that the UserInfo table data is not inserted into the table. This is in line with our design intent. However, we found that the exception is not only because the inserted data is beyond the value range, it also contains an exception in the transaction count.

Two stored procedures are defined, and SPAddDataToUserInfo is embedded in SPMultiDataToUserInfo. During execution of both stored procedures, exceptions occur and transaction rollback occurs (because the User is empty ).

The exception due to the inserted data that exceeds the value range is specially caused, and the transaction count exception is an exception beyond expectation.

Next, let's take a look at the cause of the exception. Here we can view the problem by outputting the Transactions count.Copy codeThe Code is as follows: -- ===================================================== ======
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Inserts data
-- ===================================================== ======
Alter PROCEDURE SPAddDataToUserInfo

AS
BEGIN
BEGIN TRY
BEGIN 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 ('jkhuangt', 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 ();
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE ();
PRINT 'rolled back successful 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 ();
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE ();
PRINT 'rolled back successful Transactions: '+ Convert (varchar, @ TRANCOUNT );
END CATCH

END
GO

In the above example, we can view the transaction counter changes during transaction rollback by outputting the transaction count (@ TRANCOUNT.
Figure 8 stored procedure execution message

We found that when executing SPMultiDataToUserInfo and SPAddDataToUserInfo, the transaction counter is added with 1. When an exception in SPAddDataToUserInfo occurs, the transaction rollback transaction calculator is set to zero.
When executing a transaction in SPMultiDataToUserInfo, an exception is thrown because the transaction calculator (@ TRANCOUNT) has set zero. Now we understand the cause of the transaction count exception, therefore, before performing transaction rollback, we must determine whether the transaction calculator (@ TRANCOUNT) is 0. If it is 0, the transaction will not be rolled back.Copy codeThe Code is as follows: -- ===================================================== ======
-- Author: JKhuang
-- Create date: 12/8/2011
-- Description: Inserts data
-- ===================================================== ======
Alter PROCEDURE SPAddDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN 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 ('jkhuangt', 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 have added the transaction counter judgment. When the counter is 0, no transaction rollback is performed, so that the transaction counter is abnormal.

Figure 9 stored procedure execution message

Now we have a better understanding of the Transaction, and the above Transaction is defined as a common daily template, the following provides a basic Transaction template.

Copy codeThe 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 series of operations performed as a single logical unit of work. It can be an SQL statement or multiple SQL statements.
Transactions have four features
Atomicity: it cannot be separated.
Consistency: when the transaction is completed, all data must be consistent.
Isolation: independent execution does not interfere with each other. Modifications made by a concurrent firm must be isolated from those made by any other concurrent firm.
Durability: after the service is completed, its impact on the system is permanent. This modification will be maintained even if a system failure occurs.
Applications control transactions by specifying the start time and end time of transactions.
Start transaction: You can use API functions and Transact-SQL statements to start transactions explicitly, automatically, or implicitly.
End transaction: You can use a COMMIT (SUCCESS) or ROLLBACK (failure) statement, or use an API function to end the transaction.
Transaction modes include: Display transaction mode, implicit transaction mode, and automatic transaction mode. Commonly Used in SQL is the display mode.
Principles for creating transactions:
It is important to keep the transaction as short as possible. After the transaction starts, 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 the transaction brief to reduce the competition for Lock of concurrent resources.
1. transaction processing, prohibiting interaction with users, and completing user input before the transaction starts.
2. Do not open transactions when Browsing data.
3. Keep transactions as short as possible.
4. Use Snapshot isolation for read-only queries to reduce blocking.
5. flexible use of lower transaction isolation levels.
6. flexible use of lower cursor concurrency options, such as open concurrency options.
7. Minimize the amount of data accessed in transactions.