About mysql Transaction Processing

1. Why do I need to use transactions?

A transaction is a single unit of work. If a transaction succeeds, all data modifications made in the transaction are committed,
Become a permanent component of the database. If the transaction encounters an error and must be canceled or rolled back, all data modifications are cleared.
The use of transactions provides a mechanism to prevent errors during execution and the correct SQL statements affect the database;

The transaction processing mechanism plays an important role in the process of program development. It can make the entire system more secure,
For example, when A bank handles the transfer, if the amount in account A has just been issued and Account B has not received the money, A power outage will occur,
This will cause great economic losses to banks and individuals. Transaction processing mechanism is adopted. Once an accident occurs during the transfer process
The program will be rolled back without any processing.


2. There are two main methods for MYSQL transaction processing.

1. Use begin, rollback, and commit to start a transaction rollback transaction and roll back the commit transaction confirmation.
2. directly use set to change the mysql automatic submission mode. By default, mysql is automatically submitted, that is, if you submit a query, you can directly execute it! You can use set autocommit = 0 to disable automatic commit. set autocommit = 1 to enable automatic commit to process transactions.
Note: When set autocommit = 0 is used, all your SQL statements will be processed as transactions until you confirm with commit or roll back, note that a new transaction is started when you end the transaction! In the first method, only the current transaction is used!

3. mysql supports the choice of the transaction storage engine

MYSQL only supports transaction processing for Data Tables of the INNODB and BDB types. Other data types are not supported! Transactions should all have ACID features. The so-called ACID is Atomic atomicity), Consistent consistency), Isolated isolation), Durable continuity) Written Atomicity in the first letter of the four words: the statements that constitute Transaction Processing Form A logical unit, you cannot only execute part of it. In other words, transactions are the smallest units that are inseparable. For example, in the bank transfer process, it is unreasonable to change only one account by subtracting the transfer amount from one account and adding it to another account.

Consistency: the database is consistent before and after transaction processing. That is to say, the transaction should correctly switch the system status. For example, in the bank transfer process, either the transfer amount is transferred from one account to another, or both accounts remain unchanged.

Isolation: one transaction has no impact on the processing of another transaction. That is to say, no transaction can see a transaction in an incomplete state. For example, in the bank transfer process, another transfer transaction can only be in the waiting state before the transfer transaction is committed.

Continuity: the effect of transaction processing can be permanently saved. Conversely, transactions should be able to withstand all failures, including server, process, communication, and media failures. For example, in the bank transfer process, the account status must be saved after the transfer.

4. simple transaction instance

<? Php
$ Db = mysql_connect ('localhost', 'root', '') or die ('link failed'); // link to the database
Mysql_select_db ('test'); // select a database
Mysql_query ("set autocommit = 0"); // sets that mysql does not submit automatically
Mysql_query ("begin"); // sets the start of a transaction.
// Insert data
$ SQL = "INSERT INTO 'order' ('order _ num') VALUES ('1sadfsdfafasdfasdffffffffsd1111111 ')";
$ Sql2 = 'insert INTO 'order' ('order _ num') VALUES ("1sadfsdfafasdfasdffffffffsd1111111 ")';
$ Q = mysql_query ($ SQL );
$ Q2 = mysql_query ($ sql2 );
If (! $ Q &&! Q2 ){
Mysql_query ("rollback"); // execute rollback
Echo 'execution failed, data rollback'; exit;
}
Mysql_query ("COMMIT"); // submit a transaction
Echo "successful ";
Mysql_close ($ db );

This article from the "Candlelight to heaven-IT technology road" blog, please be sure to keep this source http://wuhai.blog.51cto.com/2023916/974994