Discussion on transaction rollback problem of database transaction processing mechanism

First, the transactions in SQL

1. Concept: A thing is a mechanism, a sequence of operations that contains a set of database operations commands that either execute all or do not. A transaction is an inseparable unit of work logic. The transaction is used as the smallest control unit when concurrent operations are performed on the database system. Multi-User Login system is suitable for using transaction mechanism.
2. Properties: 4 Large properties:
   A. Atomicity: A complete operation when a transaction occurs. B. Consistency: When a transaction completes, the database must be in a consistent state.
   C. Isolation: All concurrent transactions that modify data are isolated from each other. D. Persistence: After a transaction is complete, its effect on the system is permanent.
3. Create

(1) Start transaction: BEGIN TRANSACTION

(2) COMMIT TRANSACTION: Commit Transaciton

(3) ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

4. Classification

(i) Show transactions: explicitly specify the beginning of a transaction with BEGIN transaction.

(ii) Stealth services:

Open the Stealth transaction: set implicit transcations on

When operating in implicit transaction mode, SQL Sverler automatically starts a new transaction after the transaction is committed or rolled back. Cannot describe the beginning of a transaction, only the transaction must be committed or rolled back.

Autocommit transactions: The default mode of SQL Server, which treats each individual T-SQL statement as a transaction. If executed successfully, it is automatically committed; otherwise, rollback.

by default, if an error occurs in the execution of a transaction, only the error action statement is rolled back (that is, the sentence is not executed, the rollback is counted), and the correct operation statement before or after the error is still committed.

Use TestDB

Begin TransAction
Insert into person (personid,personname)
Values (' 1 ', ' Name1 ')
Insert into person (personid,personname)
Values (' 1 ', ' Name1 ')
Insert into person (personid,personname)
Values (' 3 ', ' Name3 ')
Commit TransAction
/*
Select to have ' 1 ', ' Name1 ' and ' 3 ', ' Name3 ',
Stating that only the second sentence of the error was canceled.
*/

Method 1: Open Xact_abort for all rollbacks

use TestDB
SET xact_abort on--open
Begin TransAction
    Insert into person (personid,personname)
                 Values (' 1 ', ' Name1 ')
    Insert into person (personid,personname)
                 Values (' 1 ', ' Name1 ')
    Insert into person (personid,personname)
                 Values (' 3 ', ' Name3 ')
Commit TransAction
/*
    When SET Xact_abort is on,
    if the Execute Transact-SQL statement produces a run-time error,
    the entire transaction terminates and is rolled back.
     It is the off state by default.
*/

All Rollback Method 2: Use Try ... Catch

Use TestDB
Begin Try
Begin TransAction
Insert into person (personid,personname)
Values (' 1 ', ' Name1 ')
Insert into person (personid,personname)
Values (' 1 ', ' Name1 ')
Insert into person (personid,personname)
Values (' 3 ', ' Name3 ')
Commit TransAction
End Try
Begin Catch
Rollback TransAction
End Catch
/*
Use Trycatch to catch exceptions.
If the error generated within a TRY block causes the state of the current transaction to expire,
The transaction is categorized as a non-committed transaction.
If the error that aborts the transaction usually occurs outside the try block,
Will cause the transaction to enter a non-committed state.
Non-committed transactions can only perform read operations or ROLLBACK TRANSACTION.
The transaction cannot perform any Transact-SQL statements that could generate a write operation or COMMIT TRANSACTION.
If the transaction is classified as a non-committed transaction, the Xact_state function returns a value of 1.
*/

Rollback All Method 3: Custom error variables

Use TestDB
Declare @tranError INT--Define variables
Set @tranError =0
Begin TransAction
Insert into person (personid,personname)
Values (' 1 ', ' Name1 ')
Set @tranError = @tranError + @ @Error
Insert into person (personid,personname)
Values (' 1 ', ' Name1 ')
Set @tranError = @tranError + @ @Error
Insert into person (personid,personname)
Values (' 3 ', ' Name3 ')
Set @tranError = @tranError + @ @Error
If @tranError = 0
Commit TransAction
Else
Rollback TransAction
/*
Customize a variable to determine if an error has occurred in the end.
*/

Finally, note that if a transaction writes Begin TransAction and does not write Commit TransAction or Rollback TransAction, Then the data about the operation (perhaps the table, perhaps the column, which I have not tested ...) ) will be locked ... The solution for locking is to execute the commit TransAction or Rollback alone TransAction

Second, the transaction processing mechanism in SQLite

The use of transactions in a database guarantees the consistency and integrity of data, while also improving efficiency.

First, a transaction is a process that can treat all operations in a startup transaction as a transaction. When all of the procedures have been completed, we can determine whether the transaction is committed or rolled back, depending on whether the operation was successful. Once the transaction is committed, all data is submitted to the database at once, and if the transaction is rolled back, the operation is discarded and the data of the original table is not changed.

SQLite starts, commits, and rolls back transactions with begin, Commit, and rollback, as well as T-SQL.

Db.begintransaction (); Set up a start transaction manually

try{

Bulk processing operations

for (Collection c:colls) {

Insert (db, C);

}

db.settransactionsuccessful ();//Set transaction processing to succeed, do not set automatically rollback does not commit.

　　　No database operations are performed between Settransactionsuccessful and Endtransaction

}catch (Exception e) {

Mylog.printstacktracestring (e);

}finally{

Db.endtransaction (); Processing complete

}

Use the Sqlitedatabase BeginTransaction () method to open a transaction where the program executes to the Endtransaction () method to check whether the flag of the transaction is successful if the program executes to Endtransaction () Before calling the Settransactionsuccessful () method to set the flag for a transaction to be successful, all operations starting with BeginTransaction () will be committed, if not called settransactionsuccessful () Method rolls back the transaction.

Reference Link:

Http://www.cnblogs.com/weihengblogs/p/4281148.html

Http://www.cnblogs.com/5211314jackrose/p/5818124.html

Discussion on transaction rollback problem of database transaction processing mechanism