The use of sql transaction and its skill arrangement

Summary:

In the actual project development, in order to ensure the consistency of data operation results and other requirements, transaction is an essential solution tool.

According to the implementation principle of SQL server, in fact, every execution statement of SQL server is a transaction operation, that is to say, every SQL statement either succeeds in operation or fails in operation: for example, when updating statements and multiple fields at the same time, some fields will not be updated successfully or some fields will fail.

However, in the development process, we usually refer to a set of ordered SQL sets, which ensures the consistency of the execution results.

Transaction characteristics:

The main characteristics of transaction include: principle, consistency, isolation and persistence

1. Atomicity: a transaction must be an automatic unit of work, either all or none.
2. Consistency: a transaction brings a database from one consistent state to another. At the end of a transaction, all internal data is correct.
3. Isolation: when multiple transactions are concurrent, the execution of one transaction is not affected by other transactions.
4. Persistence: after the transaction is committed, the data is permanent and cannot be rolled back, and is not affected by events such as shutdown.

Transaction classification:

According to the execution dimension strength of the transaction, the transaction can be divided into automatic commit transaction, explicit transaction and implicit transaction.

Auto commit transaction: a default mechanism of sql server, also known as its own transaction. This mode is adopted by every sql statement execution.
Explicit transaction: This is also the transaction we usually say. Start the transaction through Begin Transaction, execute a set of SQL statements, and end the transaction by Commit Transaction and Rollback Transaction.
Implicit transaction: use set implicit transaction on to open the implicit transaction mode, and automatically commit after sql execution. When a transaction ends, this mode will automatically enable the next transaction. Only Commit Transaction and Rollback Transaction can be used.

The main difference between display transactions and implicit transactions is that implicit transactions are automatically committed after execution.

Introduction to explicit transactions:

Show that the transaction is opened through begin Transaction and rolled back through Rollback Transaction

To prepare data, first create a table:

---- Create a table TEST_Name ，Each field is non empty
CREATE TABLE [dbo].[TEST_Name](
	[Id] [int] NULL,
	[Name] [nvarchar](50) NULL
) ON [PRIMARY]

Example:

----Execute a transaction normally and completely, and there is no exception in the SQL within the transaction

---- Execute a transaction normally and completely, and within the transaction SQL No abnormalities
begin tran
insert into TEST_name values(1,1)
insert into TEST_name values(3,3)
commit tran

----Execute a transaction, and there is an exception in the SQL within the transaction

begin tran
insert into TEST_name values(10,10)
insert into TEST_name values(11,null)----Statement execution failed because the name Cannot be empty
insert into TEST_name values(12,12)
commit tran

----- The final result of statement execution is
Insert successfully id Two pieces of data: 10 and 12 

Through the above statement, the execution result is inconsistent with the consistency in our transaction. This is not the effect we want to see when using the transaction. In fact, what we want is that all the three statements are inserted successfully or failed.

In order to achieve data, three methods can be used: try catch; judge the execution result, step by step, error rollback; enable xact_abort.

try catch to implement transaction rollback

begin tran
begin try
    insert into TEST_name values(1,1)
    insert into TEST_name values(2,null)
    insert into TEST_name values(3,2)
    commit tran
end try
begin catch
    select 'Execution exception, transaction rollback'
    rollback tran
end catch
---- The execution result is: a piece of data will not be inserted

Judgment of execution result, step by step, error rollback

begin tran
    declare @error int 
    set @error=0
    
    insert into TEST_name values(1,1)
    set @error=@error+@@error
    insert into TEST_name values(2,null)
    set @error=@error+@@error
    insert into TEST_name values(3,2)
    set @error=@error+@@error
    
if(@error<>0)
    begin 
        select 'Execution exception, transaction rollback'
        rollback tran
    end
else 
    begin
        commit tran
    end

---- Each step is executed correctly before proceeding.
begin tran
    ---- Number of rows affected
    declare @ROWCOUNT int
    set @ROWCOUNT=0
    
    insert into TEST_name values(1,1)
    set @ROWCOUNT=@@ROWCOUNT
    if(@ROWCOUNT>0)
        begin 
            insert into TEST_name values(2,null)
            set @ROWCOUNT=@@ROWCOUNT
        end
        
    if(@ROWCOUNT>0)
        begin 
            insert into TEST_name values(3,2)
            set @ROWCOUNT=@@ROWCOUNT
        end
        
if(@ROWCOUNT<=0)
    begin 
        select 'Execution exception, transaction rollback'
        rollback tran
    end
else 
    begin
        commit tran
    end

Open Xact "abort"

---- XACT_ABORT Set up on :Represents a statement execution error, does not continue to execute, and automatically rolls back the transaction
---- XACT_ABORT Set up off :Represents a statement execution error. The child rolls back the execution of the statement, continues to execute subsequent statements, and submits the successful statements.
　　---- off This situation should be rarely used in transactions. After all, the purpose of using transactions is to achieve the consistency of execution results.
set XACT_ABORT on
begin tran
insert into TEST_name values(10,10)
insert into TEST_name values(11,null)----Statement execution failed because the name Cannot be empty
insert into TEST_name values(12,12)
commit tran

Set transaction savepoint:

During normal transaction use, it may also need to be implemented. When a transaction is rolled back, it only rolls back to the specified location, and the execution result before the specified location is not rolled back.

In SQL server, transaction savepoints can be used to realize accurate rollback of transactions. The key words are save transaction and rollback transaction. The specific rules are as follows:

---- Each step is executed correctly before proceeding.
begin tran
    ---- Number of rows affected
    declare @ROWCOUNT int
    set @ROWCOUNT=0
    
    insert into TEST_name values(1,1)
    set @ROWCOUNT=@@ROWCOUNT
    save tran stanstation1
    --- save tran transtation1
    if(@ROWCOUNT>0)
        begin 
            insert into TEST_name values(2,null)
            set @ROWCOUNT=@@ROWCOUNT
        end
        
    if(@ROWCOUNT>0)
        begin 
            insert into TEST_name values(3,2)
            set @ROWCOUNT=@@ROWCOUNT
        end
        
if(@ROWCOUNT<=0)
    begin 
        select 'Execution exception, transaction rollback'
        ---- The execution result is: 1,1 Successfully inserted into database table
        rollback tran stanstation1
    end
else 
    begin
        select 'Transaction submission'
        commit tran
    end

Conclusion:

Through the above review and practice, I have a further understanding of sql transactions. Simple summary: in fact, every executed sql is implemented by transaction. In practice, we usually use display transaction to process business, but in the process of using transaction, we must combine corresponding strategies to ensure the consistency of transaction execution results.

Today I'll write about this. Tomorrow I'll summarize the implementation of distributed transactions. This is also an important module. Especially in today's large-scale systems, distributed transactions work well when databases are divided into tables.

xact_abort