Error understanding for MySQL transaction commit, rollback

First, causes

Begin or start transaction start a transaction

ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Acknowledgement

The interpretation of a transaction is as follows: A transaction consists of one or more SQL statements that act as a single unit, and if one of the statements cannot be completed, the entire unit is rolled back (undone), and all data that is affected is returned to the state before the transaction began. As a result, only all statements in the transaction are executed successfully to say that the transaction was executed successfully.

There is nothing wrong with the sentence itself, the problem is that I misunderstood it, and the question in my test was described as follows:

There are two INSERT statements in the MySQL transaction, where the second statement is wrong, and after the transaction is run, the first one is still inserted, and the code is as follows.

Creating tables: Create TABLE ' Test_tab ' (' F1 '  int (one) not null, ' F2 '  varchar (one)  DEFAULT NULL, PRIMARY KEY (' F1 ')) engine=innodb//execution transaction: START Transaction;insert into Test_tab values    (1, ' 2 '); INSERT into test_tab values    (1, ' 3 ') ; COMMIT;

(Error: This is just what I initially thought) as long as the transaction is written out, and finally committed with commit, the database will automatically determine whether these statements are fully successful, if successful, all the data inserted into the database, if there is a failure to automatically rollback to the original state! Obviously I think it's wrong.

The result of my execution of the above statement is:

[SQL] START TRANSACTION;

Rows affected: 0

Time: 0.000s

[SQL]

INSERT into Test_tab VALUES (1, ' 2 ');

Rows affected: 1

Time: 0.001s

[SQL]

INSERT into Test_tab VALUES (1, ' 3 ');

[ERR] 1062-duplicate entry ' 1 ' for key ' PRIMARY '

We look at the results to know insert INTO Test_tab VALUES (1, ' 3 '); This sentence fails because of a primary key conflict, so the following commit is not executed.

It is important to note that a transaction has already been opened and a correct INSERT statement has been executed, although it is not reflected in the database, but if a commit or begin or start is executed later in the connection Transaction (a new transaction commits the other uncommitted transactions in the link, which is equivalent to commit! You will find that you have just insert into Test_tab VALUES (1, ' 2 ') and write into the database.

So the rollback of a transaction is not so understandable, and the correct understanding should be that if all the SQL statements in the transaction are executed correctly, you will need to commit the commit yourself manually, otherwise there will be any execution error that requires you to commit a rollback, and all operations are rolled back. Rather than commit will give you automatic judgment and rollback.

Ii. Solutions

1. C + + Call Mode: (simple example)

First define a variable flag bool m_flag=true;

Then execute the transaction and SQL statements such as: (Execute for himself write function, as below)

Execute (m_sqlcon, "begin");

Execute (M_sqlcon, "INSERT into Test_tab VALUES (1, ' 2 ')");

Execute (M_sqlcon, "INSERT into Test_tab VALUES (1, ' 3 ')");

If an error occurs during execution of any of the statements, M_flag the flag to false.

You should not commit a commit at this time, but instead use a function to determine if the flag is false, or false to indicate that there is a failure in the SQL statement executed, execute rollback, otherwise the instructions are all correct and commit commits. As in the following Commit_transaction () method.

The code is roughly as follows, if you need to modify it!

1 privat void execute (MYSQL m_sqlcon, String sqlstatement) 2 {3 R = mysql_real_query (M_sqlcon, SQLStatement, (unsigned lo NG) strlen (SQLStatement));  4     if (r) 5         M_flag = false;//error Mark 6} 7 public bool Commit_transaction () 8 {9         int ret_error = 0;10         If (!m_flag) Each         {             cancel_transaction ();             return false;14         }15         else16         {             Ret_error = Mysql_commit (M_sqlcon)))             {                 cancel_transaction ();                 return true;21}22         }         return true;24}

2. Take advantage of stored procedures:

1 CREATE PROCEDURE PRO2 () 2 BEGIN 3     DECLARE t_error INTEGER; 4     DECLARE    CONTINUE HANDLER for SQLEXCEPTION SET t _error = 1; 5  6     START TRANSACTION; 7         insert INTO Test_tab values    (1, ' 2 '); 8         INSERT into test_tab values    (1, ' 3 '); 9         IF t_error = 1 THEN11             rollback;12         ELSE13             commit;14         End If;15 End

Then call PRO2 ()

This can be done directly using MySQL to decide whether he should be rolling back or submitting.

Transferred from: https://www.cnblogs.com/jaejaking/p/5342285.html

Error understanding for MySQL transaction commit, rollback