Three transaction modes of SQL Server

Explicit transactions
Explicit transaction is a type of transaction in which the designer clearly defines the start and end of the transaction.
You can use BEGIN transaction, commit transaction, commit work, rollback transaction, rollback work, and other statements to define explicit transactions.

 

Create   Procedure Sp_test ( @ ID   Varchar ( 50 ))
As  
Begin   Tran   -- Start transaction
Update TB Set Cdate = Getdate () Where Tbid = @ ID  
If   @ Error   > 0   -- If any error occurs
Begin  
Rollback   Tran   -- Transaction Restoration
Return  
End  
Declare   @ PID   Varchar ( 50 ), @ Tbqty   Int  
Declare Cur_1 Cursor   For   Select PID, Qty From Tbdetail Where Tbid = @ ID   -- Declare a cursor data transaction
Open Cur_1 -- Open cursor
Fetch   Next   From Cur_1 Into   @ PID , @ Tbqty   -- Get two field values from the cursor to the variable
While   @ Fetch_status   =   0   -- Process items in a loop
Begin  
Update Ta Set Ty = Ty - @ Tbqty   Where PID = @ PID  
Fetch   Next   From Cur_1 Into   @ PID , @ Tbqty  
If   @ Error   > 0 -If an error occurs
Begin
Rollback   Tran   -- Transaction Restoration
Close Cur_1 -- Close and release a cursor
Deallocate Cur_1
Break  
End  
End  
Close Cur_1
Deallocate Cur_1
Commit   Tran   -- Transaction commit

 

 

Automatically submit transactions
This is the default mode of SQL Server. All unstated transactions are considered as automatically committed transactions. However
Is to take only one operation as the transaction scope, such as an update or delete. When the transaction is completed, each individual
Both T-SQL statements are committed or restored due to an error.

Implicit transactions
Implicit transaction mode through the set implicit_transactions on statement of the API function or T-SQL
Set to open. The next statement automatically starts a new transaction. When the transaction is completed, the next T-SQL Language
To start a new transaction.

Set Implicit_transactions On  
 
Insert   Into TA (AA, BB) Values ( ' 11 ' , ' 22 ' )
Commit   Transaction  
Go  

 

AboveProgramIndicates that the "Set implicit_transactions on" interface is used to start the implicit transaction, and then
The next program creates a transaction, adds a record to the category data table using an insert statement, and then uses commit
Transaction: Submit the transaction. If a program exists after the commit, a new transaction is started. In short, the implicit transaction
This means that the SQL server system automatically creates new transactions after all the commits and restores.