MYSQL -- Transaction Processing

MYSQL-transaction processing is widely used in various management systems. For example, in the personnel management system, many synchronous database operations require transaction processing. For example, in the personnel management system, if you delete a person, you need to delete the basic information of the person and the information related to the person, such as the mailbox and articles, these database operation statements constitute a transaction! Delete from userinfo where ~~~ Delete from mail where ~~ Delete from article where ~~~~ If www.2cto.com has no transaction processing, it is hard to imagine the consequence of executing the first sentence when you delete the transaction, assuming that an error occurs! But it is processed by transactions. If a deletion error occurs, you only need to roll back to cancel the deletion operation (in fact, as long as you do not have a commit, you do not actually perform the deletion operation). Generally, in business applications, all must consider transaction processing! View inodb information shell>/usr/local/mysql-u root-p mysql> show variables like "have _ %" the system will prompt: + ------------------ + ------- + | Variable_name | Value | + ------------------ + ------- + | have_bdb | YES | have_crypt | YES | have_innodb | YES | have_isam | YES | have_raid | YES | | have_symlink | YES | have_openssl | NO | have_query_cache | YES | + ------------------ + ------- + 8 rows in set (0.05 sec) if so, we can create Create a table that supports transaction processing. MYSQL transaction processing function! Author: Feifengxlq Email: feifengxlq@sohu.com I have always thought MYSQL does not support transaction processing, so when processing data from multiple data tables, it's always troublesome (I have to write it into a text file and write it into the database only when the system is reloaded to prevent errors )~ Today, we found that MYSQL Databases have supported the transaction function since MYSQL 4.1. It is said that MYSQL 5.0 will introduce the Stored Procedure pai_^ to briefly introduce the transaction! A transaction is the unit of execution of a DBMS. It consists of a finite sequence of database operations. But not any database operation sequence can become a transaction. In general, a transaction must meet four conditions (ACID) atomicity (Autmic): the transaction is being executed. To do this, "Do not do it, or do it all !", That is to say, the Department is not allowed to perform tasks separately. Even if the transaction cannot be completed due to a fault, the impact on the database should be eliminated during rollback! Consistency: the transaction operation should change the database 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 to form a transaction! Isolation: if multiple transactions are executed concurrently, it should be the same as the independent execution of each transaction! Durability: A successfully executed transaction has a lasting effect on the database. Even if the database fails or fails, the transaction can be recovered! There are two main methods to process MYSQL transactions. 1. Use begin, rollback, and commit to start a transaction rollback transaction to roll back the commit transaction confirmation. 2. directly use set to change the mysql automatic commit mode. MYSQL automatically commits by default, that is, if you submit a QUERY, It will be executed directly! We can use set autocommit = 0 to disable automatic commit. set autocommit = 1 to enable automatic commit to process transactions. Note that when you use set autocommit = 0, all your SQL statements will be processed as transactions until you use commit to confirm or roll back, note that a new transaction is also started when you end the transaction! In the first method, only the current transaction is used! The first method is recommended! In MYSQL, only INNODB and BDB Data Tables support transaction processing! Other types are not supported! (Remember !) Next time, let's talk about how to lock and unlock MYSQL Data Tables! MYSQL5.0 WINXP passed the test ~ Performance_^ mysql> use test; Database changedmysql> create table 'dbtest' (-> id int (4)->) TYPE = INNODB; Query OK, 0 rows affected, 1 warning (0.05 sec) mysql> select * from dbtest->; Empty set (0.01 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into dbtest value (5); Query OK, 1 row affected (0.00 sec) mysql> insert into dbtest value (6); Query OK, 1 row affected (0.00 sec) mysql> comm It; Query OK, 0 rows affected (0.00 sec) mysql> select * from dbtest; + ------ + | id | + ------ + | 5 | 6 | + ------ + 2 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into dbtest values (7); Query OK, 1 row affected (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from dbtest; + ------ + | id | + ------ + | 5 | 6 | + ------ + 2 rows in set (0.00 sec) Mysql> ************************************** * ********* [PHP] function Tran ($ SQL) {$ judge = 1; mysql_query ('begin'); foreach ($ SQL as $ v) {if (! Mysql_query ($ v) {$ judge = 0 ;}} if ($ judge = 0) {mysql_query ('rollback'); return false ;} elseif ($ judge = 1) {mysql_query ('commit '); return true ;}} [/PHP] ********************************** * ************ <? Php $ handler = mysql_connect ("localhost", "root", ""); mysql_select_db ("task"); mysql_query ("set autocommit = 0 "); // if it is set to not automatically submitted, because MYSQL executes mysql_query ("BEGIN") by default; // starts the transaction definition if (! Mysql_query ("insert into trans (id) values ('2')") {mysql_query ("ROOLBACK"); // determines if the execution fails to be rolled back} if (! Mysql_query ("insert into trans (id) values ('4')") {mysql_query ("ROOLBACK "); // judge execution failure rollback} mysql_query ("COMMIT"); // execute the transaction mysql_close ($ handler);?>