Php processes mysql transactions

Www.beijibear.com? Aid463 transactions must meet four conditions (ACID): atomicity (Autmic), Consistency (Consistency), Isolation (Isolation), and Durability (Durability) atomicity (Autmic ): the transaction is being executed. "Do not do it, or do it all ."

Http://www.beijibear.com /? Aid = 463 transactions must meet four conditions (ACID): atomicity (Autmic), Consistency (Consistency), Isolation (Isolation), Durability (Autmic ): the transaction is being executed. "Do not do it, or do it all ."

Http://www.beijibear.com /? Aid = 463.

TransactionsIt must meet four conditions (ACID): atomicity (Autmic), Consistency (Consistency), Isolation (Isolation), Durability (Durability)

Atomicity (Autmic ):TransactionsIn execution, "Do not do it, or do it all !", That is to say, do not allowTransactionsPartially executed. Even if the fault causesTransactionsCannot be completed. The impact on the database should also be eliminated during rollback!

Consistency ):TransactionsThe operation should make the database change from a consistent state to another consistent state! Take online shopping for example. You only need to let the goods go out of stock and let the goods enter the shopping basket of the customer.Transactions!

Isolation: if multipleTransactionsConcurrent execution should be similarTransactionsSame for independent execution!

Durability: a successful executionTransactionsThe role of the database is persistent. Even if the database fails or fails, it should be able to be restored!

MYSQLTransactionsProcessingThere are two main methods.
1. Use begin, rollback, and commit to implement
StartTransactions
RollbackTransactionsRollback
CommitTransactionsConfirm
Rollback and commit cannot be used in parallel. When you use them at the same time, only the previous one is valid, and the latter one is invalid. That is, you can execute commit or execute rollback.
2. directly use set to change the mysql automatic submission Mode
MYSQL is automatically submitted by default, that is, when you submit a QUERY, It will be executed directly! We can use
Set autocommit = 0 disable automatic submission
Set autocommit = 1 enable automatic submission
To achieveTransactionsOfProcessing.

Note that when you use set autocommit = 0, all your SQL statements will be usedTransactionsProcessingUntil you end with commit confirmation or rollback. Note that when you end thisTransactionsAt the same time, a newTransactions! In the first method, only the currentTransactions!

In MYSQL, only INNODB and BDB data tables are supported.TransactionsProcessing! Other types are not supported!
* **: The default MYSQL database engine is MyISAM, which is not supported.Transactions! If you want to make MYSQL supportTransactions, You can manually modify it:
The method is as follows: 1. Modify the c: \ appserv \ mysql \ my. ini file, find skip-InnoDB, add # in front, and save the file.
2. Enter services. msc in the running state to restart the mysql service.
3. in phpmyadmin, mysql-> show engines; (or execute mysql-> show variables like 'have _ % ';). If InnoDB is YES, InnoDB is supported.
It also indicates supportTransactionsTransaction.
4. When creating a table, you can select the InnoDB Engine for the Storage Engine. For a previously created table, you can use mysql-> alter table table_name type = InnoDB;
Or mysql-> alter table table_name engine = InnoDB; to change the data table engine to supportTransactions.

Instance 1:

 
 

  
  
$ Conn = mysql_connect ('localhost', 'root', 'root') or die ("data connection Error !!! ");
  
  
Mysql_select_db ('test', $ conn );
  
  
Mysql_query ("set names 'gbk'"); // use GBK Chinese encoding;
  
  
// StartTransactions 
  
  
Mysql_query ("BEGIN"); // or mysql_query ("start transaction ");
  
  
$ SQL = "INSERT INTO 'user' ('id', 'username', 'sex') VALUES (NULL, 'test1', '0 ')";
  
  
$ Sql2 = "insert into 'user' ('did', 'username', 'sex') VALUES (NULL, 'test1', '0 ')"; // I intentionally wrote an error.
  
  
$ Res = mysql_query ($ SQL );
  
  
$ Res1 = mysql_query ($ sql2 );
  
  
If ($ res & $ res1 ){
  
  
Mysql_query ("COMMIT ");
  
  
Echo 'submission successful. ';
  
  
} Else {
  
  
Mysql_query ("ROLLBACK ");
  
  
Echo 'data rollback. ';
  
  
}
  
  
Mysql_query ("END ");
 
 

Example 2:

 
 

  
  
Mysql_query ("set autocommit = 0"); // sets that mysql is not automatically submitted and must be submitted using the commit statement.
  
  
$ SQL = "INSERT INTO 'user' ('id', 'username', 'sex') VALUES (NULL, 'test1', '0 ')";
  
  
$ Sql2 = "insert into 'user' ('did', 'username', 'sex') VALUES (NULL, 'test1', '0 ')"; // I intentionally wrote an error.
  
  
$ Res = mysql_query ($ SQL );
  
  
$ Res1 = mysql_query ($ sql2 );
  
  
If ($ res & $ res1 ){
  
  
Mysql_query ("COMMIT ");
  
  
Echo 'submission successful. ';
  
  
} Else {
  
  
Mysql_query ("ROLLBACK ");
  
  
Echo 'data rollback. ';
  
  
}
  
  
Mysql_query ("END ");//TransactionsProcessingDo not forget mysql_query ("set autocommit = 1"); Submit automatically
 
 

Not SupportedTransactionsMyISAM engine database can use the table lock Method

// MyISAM & InnoDB support,
/*
Lock tables can LOCK the table used for the current thread. If the table is locked by other threads, blocking occurs until all the locks can be obtained.
Unlock tables can release any lock maintained by the current thread. When the thread publishes another lock tables, or when the connection to the server is closed, all TABLES locked by the current thread are implicitly unlocked.
*/

 
 

  
  
Mysql_query ("lock tables 'user' WRITE"); // LOCK the 'USER' table
  
  
$ SQL = "INSERT INTO 'user' ('id', 'username', 'sex') VALUES (NULL, 'test1', '0 ')";
  
  
$ Res = mysql_query ($ SQL );
  
  
If ($ res ){
  
  
Echo 'submission successful .! ';
  
  
} Else {
  
  
Echo 'failed! ';
  
  
}
  
  
Mysql_query ("unlock tables"); // UNLOCK