1.1.1 Summary
I'm sure you're familiar with SQL Transcation. However, it ensures the data consistency and security of the database, especially when performing additions and deletions to the data, if an exception and error occurs, it triggers a transaction rollback, thus ensuring the consistency and security of our data, Below we will introduce the event (transcation) by a section of four.
1.1.2 Body
Let's first introduce the use of transcation with a specific example, if we have a table in our database userinfo, it contains three fields: UserID (self-increasing), UserName (nvarchar), and Luckynumber ( tinyint), as shown in the following illustration:
Figure 1 UserInfo Table
The SQL code for the UserInfo table is as follows:
Copy Code code 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] () not NULL,
[Luckynumber] [tinyint] Not NULL
) on [PRIMARY]
We then insert the data into the table userinfo, where a stored procedure is used to insert the data into the table, and the stored procedure Spadddatatouserinfo is defined as follows:
Copy Code code 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 the stored procedure.
Figure 2 Message to execute a stored procedure
With the figure above we know that an exception occurred during the execution of the stored procedure and that the value "20111111" data is out of the range of tinyint, now let's look at the data insertion.
Figure 3 UserInfo the data in the table
We found that only two rows of data were inserted, and the third row was not inserted successfully, but to ensure data integrity, we had to insert all or none of the data, and we could consider using transcation to ensure data integrity and security.
Then let's modify the stored procedure Spadddatatouserinfo and add transcation to the stored procedure.
Copy Code code 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'll run the stored procedure again to see if it's all not plugged into the table.
Figure 4 UserInfo The data in the table
We found that the results were still inserted into the table, as did the addition of transcation processing. What is the reason for this? Perhaps careful you have found that we did not add transaction rollback--rollback.
But where exactly do we add a transaction rollback (ROLLBACK)? or more specifically: "When are we going to trigger a transaction rollback (ROLLBACK)?"
Since our data insertion failed because of an exception in the insertion process, we will catch the exception and handle the exception, which is the design of try/catch, so that we continue to refine our stored procedures.
Copy Code code 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 have added an exception handling mechanism to the stored procedure try/catch (Note: SQL Server 2005 or later version only supports Try/catch), then let's run the stored procedure again.
Figure 5 UserInfo the data in the table
It looks like we've solved the problem and we know that stored procedures can embed stored procedures or functions, so we embed the above stored procedure spadddatatouserinfo into the stored procedure spmultidatatouserinfo, The definition of Spmultidatatouserinfo is as follows:
Copy Code code 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 an exception, it rolls back the transaction (ROLLBACK), but does spmultidatatouserinfo rollback or continue inserting the data?
Figure 6 Executing stored procedure messages
Figure 7 UserInfo the data in the table
An exception occurred during the insertion process, checking that the UserInfo table data was not inserted into the table, which was consistent with our design intent, but we found that the exception was not only the result of inserting data beyond the range of values, but also the transaction count exception.
Because two stored procedures are defined, and Spadddatatouserinfo is embedded in Spmultidatatouserinfo, both of these stored procedures have an exception and a transaction rollback (because user is empty).
Because the exception that the inserted data exceeds the range of the numeric value is deliberately caused by us, the transaction count exception is unexpected.
So let's take a look at what caused the anomaly, and here we'll look at the problem by outputting the transactions count.
Copy Code code 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 (' 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 ();
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
Above we view the transaction counter changes when the transaction is rolled back by outputting the transaction count (@ @TRANCOUNT).
Figure 8 Stored procedure execution messages
The above figure shows that when executing spmultidatatouserinfo and Spadddatatouserinfo, transaction counters are added 1, and when an exception is encountered in Spadddatatouserinfo, the transaction ROLLBACK TRANSACTION calculator is zero.
When performing transactions in Spmultidatatouserinfo, because the transaction calculator (@ @TRANCOUNT) has zero, causing an exception to be thrown, we now understand the cause of the transaction count exception, So we have to determine whether the transaction calculator (@ @TRANCOUNT) is 0 before doing a transaction rollback, and if 0 does not roll back the transaction.
Copy Code code 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 (' 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 have increased the judgment of the transaction counter, when the counter is 0 o'clock, no transaction rollback, so there is no previous transaction counter exception.
Figure 9 Stored procedure execution messages
Now we have a better understanding of the transaction, and the above transaction definition as a daily common template, the following gives a basic transaction template.
Copy Code code as follows:
-- =============================================
--Transaction Temp
-- =============================================
BEGIN TRY
BEGIN TRANSACTION
--
--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. can be an SQL statement or multiple SQL statements.
A transaction has four attributes
Atomicity: Not separated, the formation of the formation, failure is a failure.
Consistency: When a transaction completes, it must keep all data 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 transaction.
Durability: After the completion of the service, its impact on the system is permanent. This modification is maintained even if a system failure occurs.
The application controls transactions primarily by specifying when the transaction starts and ends.
Start transactions: Using API functions and Transact-SQL statements, you can initiate transactions either explicitly, automatically, or implicitly.
End transaction: You can use a COMMIT (success) or ROLLBACK (failure) statement, or an API function to end a transaction.
The transaction pattern is divided into: Display transaction mode, implicit transaction mode, automatic transaction mode. In SQL, the display mode is commonly used.
Principles for creating transactions:
It is important to keep transactions as short as possible, and when a transaction is started, the database management system (DBMS) must retain a lot of resources before the end of the transaction to ensure the proper 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 lockout contention.
1, transaction processing, prohibit interaction with the user, before the transaction began to complete user input.
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 the lower transaction isolation level flexibly.
6, flexibility to use lower cursor concurrency options, such as optimistic concurrency options.
7, in the transaction as far as possible to make the least amount of data access.
