MySQL transaction processing

Knowledge Points:

What is transaction processing?

When a database table is designed in a tree-like structure, we add, delete, and change a table, which may require the same operation on another table, and in order to ensure that multiple SQL can succeed at the same time, use MySQL transaction processing.

Note: Only additions and deletions to the operation can be rolled back, alter and other operations are not feasible!

Transaction characteristics:

1. atomicity: All SQL execution must succeed or roll back to pre-processing state

2. Consistency: Ensure that the database is successfully committed after it has changed state correctly.

3. Isolation: Make transactional operations independent and transparent to each other.

4. Persistence: Ensure that the result of the committed transaction or the effect of the system is still present in the event of a failure.

Currently, there are two kinds of engines for MySQL-supported transactions, and basically everyone is using the InnoDB engine, and note that all the tables that transact the SQL operations must be INNODB engines.

Keyword 1:commit, when transaction processing is successful, the transaction SQL is submitted

Keyword 2:rollback, when there is an error in one of the SQL executions, the rollback occurs, which rolls back all the database tables that were manipulated to the previous state.

Keyword 3:start trasaction, opening transaction function

Keyword 4:autocommit, auto-commit, if 0 is turned off, if 1 is turned on, so it needs to be assigned a value of 0 (off), after use, and then assign a value of 1 (re-open), general MySQL default is turned on

PHP executes the transaction code:

1 $con=mysql_connect(' localhost ', ' root ', ' root ');2 mysql_select_db("Test",$con);3 mysql_query("Set Names UTF8");4 mysql_query(' START TRANSACTION ') or die(Mysql_error());//turn on transaction functionality5 $sql 1= "INSERT into student (Name,age) VALUES (' Xiaoming ', ' 16 ')";6 $sql 2= "INSERT into score (sid,computer,english) VALUES (2,90,98)";7 if(!mysql_query($sql 1)){8     mysql_query("ROLLBACK");//SQL execution fails and the table data is rolled back to its previous state9     Echo' 1 ';Ten } One if(!mysql_query($sql 2)){ A     mysql_query("ROLLBACK");//SQL execution fails and the table data is rolled back to its previous state -     Echo' 2 '; - } the  - mysql_query(' COMMIT ') or die(Mysql_error());//Execution Transactions

SQL statements are deliberately written incorrectly, the results are not submitted, transaction processing is feasible

Another: Start transaction equals set autocommit = 0

MySQL transaction processing