MySQL transaction processing and application analysis transaction processing are widely used in various management systems. for example, in the personnel management system, many database synchronization 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!
Deleted SQL statement
Delete from userinfo where ~~~
Delete from mail where ~~
Delete from article where ~~
~~
If no transaction is processed, if an error occurs during the deletion process and only the first sentence is executed, the consequences are unimaginable!
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 have not actually performed the deletion operation)
Generally, transaction processing must be taken into account in business-level applications!
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 a table that supports transaction processing.
MYSQL transaction processing function!
I have always thought that MYSQL does not support transaction processing, so it is always troublesome to process data from multiple data tables (I have to write it into a text file, write data to 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_^.
Let's 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. Generally, transactions must meet four conditions (ACID)
Atomicity (Autmic): When a transaction is executed, "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 implement
Start a transaction
Rollback transaction rollback
Commit transaction validation
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 process the transaction.
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 !)
MYSQL5.0 WINXP passed the test ~
Mysql> use test;
Database changed
Mysql> 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> commit;
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>
**************************************** **************************************** ***********************************
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;
}
}
**************************************** ********
$ Handler = mysql_connect ("localhost", "root ","");
Mysql_select_db ("task ");
Mysql_query ("set autocommit = 0"); // SET to not automatically submit, because MYSQL runs immediately by default.
Mysql_query ("BEGIN"); // start transaction definition
If (! Mysql_query ("insert into trans (id) values ('2 ')"))
{
Mysql_query ("ROOLBACK"); // determines whether to roll back when execution fails.
}
If (! Mysql_query ("insert into trans (id) values ('4 ')"))
{
Mysql_query ("ROOLBACK"); // determines whether the execution fails to be rolled back.
}
Mysql_query ("COMMIT"); // executes the transaction
Mysql_close ($ handler );
?>
