MySQL transaction processing function

TransactionsProcessing is widely used in various management systems, such as the personnel management system. Many database operations need to be synchronized.TransactionsProcessing. 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 constituteTransactions!
Deleted SQL statement
Delete from userinfo where ~~~
Delete from mail where ~~
Delete from article where ~~
~~
If noTransactionsProcessing: In the process of deletion, if an error occurs and only the first sentence is executed, the consequences are unimaginable!
HoweverTransactionsProcessing. 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, you must considerTransactionsProcessed!

 

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 inSet(0.05 sec)
If so, we can create a supportTransactionsTry the processed table.

 

MySQL transaction processing function!

Author: feifengxlq Email: feifengxlq@sohu.com
I always thoughtMySQLNot SupportedTransactionsProcessing, so it is always troublesome to process data from multiple data tables (I have to write it into a text file and write it into the database only when the system is reloaded to prevent errors )~ Found todayMySQLThe database has been supported since 4.1.TransactionsFunction, said 5.0The stored procedure pai_^ will be introduced.
A Brief IntroductionTransactionsRight!TransactionsThe Execution Unit of the DBMS. It consists of a finite sequence of database operations. But not any database operation sequence can becomeTransactions. Generally,TransactionsYes. Four conditions (acid) must be met)
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!

MySQLOfTransactionsThere are two main methods for processing.
1. Use begin, rollback, and commit to implement
StartTransactions
RollbackTransactionsRollback
CommitTransactionsConfirm
2. Direct useSetTo changeMySQLAutomatic submission mode
MySQLIt is automatically submitted by default, that is, you submit a query and it will be executed directly! We can use
Set Autocommit=0Automatic submission prohibited
Set Autocommit= 1 enable automatic submission
To achieveTransactions.
But when you useSet Autocommit=0In the future, all your SQL statements will be usedTransactionsProcessing, until you use commit to confirm or rollback to end, note that when you end thisTransactionsAt the same time, a newTransactions! In the first method, only the currentTransactions!
The first method is recommended!
MySQLOnly InnoDB and bdb data tables are supported.TransactionsProcessing! Other types are not supported! (Remember !)

Let's talk about it next time.MySQLThe data table is locked and unlocked!

Mysql5.0WINXP passed the test ~ Pai_^

Mysql> use test;
Database changed
Mysql> Create Table 'dbtest '(
-> ID int (4)
->) Type = InnoDB;
Query OK,0Rows affected, 1 warning (0.05 sec)

Mysql> select * From dbtest
->;
EmptySet(0.01 sec)

Mysql> begin;
Query OK,0Rows affected (0.00Sec)

Mysql> insert into dbtest value (5 );
Query OK, 1 row affected (0.00Sec)

Mysql> insert into dbtest value (6 );
Query OK, 1 row affected (0.00Sec)

Mysql> commit;
Query OK,0Rows affected (0.00Sec)

Mysql> select * From dbtest;
+ ------ +
| ID |
+ ------ +
| 5 |
| 6 |
+ ------ +
2 rows inSet(0.00Sec)

Mysql> begin;
Query OK,0Rows affected (0.00Sec)

Mysql> insert into dbtest values (7 );
Query OK, 1 row affected (0.00Sec)

Mysql> rollback;
Query OK,0Rows affected (0.00Sec)

Mysql> select * From dbtest;
+ ------ +
| ID |
+ ------ +
| 5 |
| 6 |
+ ------ +
2 rows inSet(0.00Sec)

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"); // 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 the 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 );
?>