Understanding of mysql transaction commit and rollback errors, mysql transaction

Understanding of mysql transaction commit and rollback errors, mysql transaction

I. Cause

Begin or start transaction to START a TRANSACTION

Rollback transaction rollback
Commit transaction validation

The transaction is interpreted as follows: a transaction is composed of one or more SQL statements as a separate unit. If one of the statements cannot be completed, the entire unit will be rolled back (UNDO ), all affected data will be returned to the status before the start of the transaction. Therefore, the transaction can be successfully executed only when all statements in the transaction are successfully executed.

There is no problem with this sentence. The problem is that I understood it incorrectly. The problem in my test is described as follows:

There are two insert statements in the mysql transaction. The second statement is incorrect. After the transaction is run, the first statement is still inserted. The Code is as follows.

// Create table: create table 'test _ tab' ('f1' int (11) not null, 'F2' varchar (11) default null, primary key ('f1 ') ENGINE = InnoDB // execute the TRANSACTION: start transaction; insert into test_tab VALUES (1, '2'); insert into test_tab VALUES (1, '3'); COMMIT;

(Error: This is what I initially thought)At first, I thought that as long as the transaction is written and finally committed with commit, the database will automatically determine whether all these statements are successfully executed. If yes, all the data will be inserted into the database, if one fails, the system automatically rolls back to the original state! Obviously, I think it is wrong.

The result after I run the preceding statement is:

[SQL] START TRANSACTION;

Affected rows: 0

Time: 0.000 s

[SQL]

Insert into test_tab VALUES (1, '2 ');

Affected rows: 1

Time: 0.001 s

[SQL]

Insert into test_tab VALUES (1, '3 ');

[Err] 1062-Duplicate entry '1' for key 'primary'

We can see that the insert into test_tab VALUES (1, '3') statement fails because of a primary key conflict, so the following commit statement is not executed.

Note that a transaction has been started and a correct insert statement has been executed, although it is not reflected in the database, however, if a commit, begin, or start transaction is executed in the connection later (a new transaction commits other uncommitted transactions in the link, which is equivalent to commit !) You will find that you have written the insert into test_tab VALUES (1, '2'); INTO the database.

 

Therefore, the transaction rollback is not so understandable. The correct understanding should be that if all SQL statements in the transaction are correctly executed, You need to manually submit the commit; otherwise, any execution error occurs, you need to submit a rollback by yourself. In this case, all operations will be rolled back, rather than the commit will automatically judge and roll back.

 

Solution 2

First define a variable flag bool m_flag = true;

Execute the transaction and SQL statement as follows: (execute is a self-written function)

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 in any statement during execution, set m_flag to false.

In this case, you should not submit the commit statement. Instead, you should use a function to determine whether the flag is false. If the flag is false, the system will execute rollback if any of the SQL statements executed fail. Otherwise, all the statements are correct, execute commit. The following commit_transaction () method.

 

The code is roughly as follows, which needs to be modified if used!

1 privat void execute (MYSQL m_sqlCon, string sqlStatement) 2 {3 r = mysql_real_query (m_sqlCon, sqlStatement, (unsigned long) strlen (sqlStatement); 4 if (r) 5 m_flag = false; // indicates 6} 7 public bool commit_transaction () 8 {9 int ret_error = 0; 10 if (! M_flag) 11 {12 cancel_transaction (); 13 return false; 14} 15 else16 {17 if (! (Ret_error = mysql_commit (m_sqlCon) 18 {19 cancel_transaction (); 20 return true; 21} 22} 23 return true; 24}

 

2. Using 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         10         IF t_error = 1 THEN11             ROLLBACK;12         ELSE13             COMMIT;14         END IF;15 END

 

CALL PRO2 ()

Mysql can be used to decide whether to roll back or submit the job.