A brief analysis of MYSQL transaction processing and application _mysql

Transaction processing in various management systems have a wide range of applications, such as personnel management system, many synchronous database operations need to use transactions. For example, in a personnel management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailboxes, articles and so on, so that these database operation statements constitute a transaction!
Deleted SQL statement
Delete from userinfo where ~ ~ ~
Delete from Mail where ~ ~
Delete from article where~~
~~
If there is no transaction processing, in the process of your deletion, the assumption is wrong, only to execute the first sentence, then its consequences are unimaginable!
But with transactions. If the deletion is wrong, you can cancel the deletion as long as you rollback (in fact, as long as you do not commit you do not actually perform the delete operation)
Generally speaking, in the business class application, must consider the 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, then we can create a table that supports transactional processing to try it out.
MySQL Transaction processing Function!
I've always thought MySQL doesn't support transactions, so it's always a hassle to work with data from multiple data tables (I had to write it to a text file, Write to the database when the system is reloaded to prevent errors) ~ Today we found that the MySQL database has been supporting transactional functionality since 4.1, and it is said that 5.0 will introduce stored procedures ^_^
First of all, a brief introduction to business! A transaction is an executing unit of a DBMS. It consists of a limited number of database operation sequences. But not any database operation sequence can become a transaction. In general, transactions are required to meet 4 conditions (ACID)
Atomicity (autmic): Business in the execution, to do "either not to do, or all to do!" "is to say that the part of the transaction is not allowed to execute. Even if the transaction can not be completed because of the failure, in rollback to eliminate the impact on the database!
Consistency (consistency): Transaction operations should cause the database to transition from a consistent state to another consistent state! Take online shopping For example, you only let the goods out of the library, and let the goods into the customer's shopping basket can constitute a business!
Isolation (Isolation): If more than one transaction executes concurrently, it should be performed as if it were executed independently of each transaction!
Persistence (Durability): A successful implementation of the transaction to the database is a lasting effect, even if the database should be faulty, should be able to recover!
There are two main ways to handle MySQL transactions.
1, with Begin,rollback,commit to achieve
Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Confirmation
2, directly with the set to change the MySQL automatic submission mode
MySQL default is automatically submitted, that is, you submit a query, it directly executed! We can pass
Set autocommit=0 prohibit automatic submission
Set autocommit=1 turn on automatic submission
To implement transaction processing.
But note that when you use set autocommit=0, all of your later SQL will be transacted, until you end with a commit or rollback, and notice that when you end the transaction, you start a new transaction! The first method will only present as a transaction!
The first method of personal recommendation!
Only InnoDB and BDB types of data tables in MySQL can support transaction processing! Other types are not supported! Remember ）
MYSQL5.0 winxp under test Pass ~
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>
*************************************************************************************************************** ****

Copy Code code as follows:

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;
}
}

************************************************

Copy Code code as follows:

<?php
$handler =mysql_connect ("localhost", "root", "");
mysql_select_db ("task");
mysql_query ("Set autocommit=0");//set to not commit automatically because MySQL executes immediately by default
mysql_query ("Begin")//Start transaction definition
if (!mysql_query ("INSERT into trans (ID) VALUES (' 2 ')")
{
mysql_query ("Roolback");/to determine rollback when execution fails
}
if (!mysql_query ("INSERT into trans (ID) VALUES (' 4 ')")
{
mysql_query ("Roolback")//Judgment execution failure rollback
}
mysql_query ("COMMIT");/execute transaction
Mysql_close ($handler);
?>