A Brief Analysis of MySQL transaction Processing and Application

Transaction processing is widely used in a variety of management systems, such as personnel management system, many synchronous database operations mostly need transaction processing. For example, in the personnel management system, you delete a person, you need to delete the basic information of the person, also want to delete the information related to the person, such as mailbox, article 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, if you only execute the first sentence during the deletion process, assuming an error, then the consequences are unimaginable!
But with transactions. If the delete is wrong, you can cancel the delete as long as rollback (actually as long as you don't have commit you don't actually perform the delete)
In general, in business applications, transaction processing must be considered!
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 this is the case, then we can try creating a table that supports transaction processing.
MYSQL's transaction handling capabilities!
1 since I think MYSQL don't support transaction processing, so in dealing with multiple data table data, 1 straight is very troublesome, I am forced to the written text files, in the system to reload when written to the database in case of error) ~ today found MYSQL database from 4.1 began to support transaction function, it is said that 5.0 will introduce ^_^ stored procedures
First, a brief introduction to 1 transactions! Transactions are DBMS execution units. It consists of a finite sequence of database operations. But not any sequence of database operations can be a transaction. In general, transactions must meet four conditions (ACID)
Atomicity (Autmic) : Transaction execution, the "do it all or nothing!" That is to say, the ministry of Commerce is not allowed to separate the implementation. Even if a transaction cannot complete because of a failure, eliminate the impact on the database at rollback!
1 cause (Consistency) : the transaction should cause the database to change from one cause to another! Take online shopping for example. You can only form a transaction if you let the goods out of the warehouse and into the customer's shopping basket.
Isolation (Isolation) : If multiple transactions execute concurrently, execute as if each transaction executes separately!
Persistence (Durability) : a successful transaction is persistent to the database, even if the database should fail, should be able to recover!
There are two main approaches to transaction processing for MYSQL.
1. begin,rollback,commit
begin starts a transaction
rollback transactions are rolled back
commit transaction confirmation
2. Directly use set to change the automatic submission mode of mysql
MYSQL is automatically committed by default, that is, you submit 1 QUERY, it is executed directly! We can go through
set autocommit=0 disables automatic submission
set autocommit=1 Enable automatic submission
To implement transaction processing.
Note that when you use set autocommit=0, all of your SQL will be used as a transaction until you confirm with commit or end with rollback. Note that when you end this transaction a new transaction is started! Use method 1 to treat the current as a transaction only!
I personally recommend using method 1!
Only INNODB and BDB tables can support transaction processing in MYSQL! Other types are not supported! (Remember!)
MYSQL5.0 WINXP test passed ~
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;
}
}

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

<?php
$handler=mysql_connect("localhost","root","");
mysql_select_db("task");
mysql_query("SET AUTOCOMMIT=0");// Set to not commit automatically because MYSQL Immediate execution by default 
mysql_query("BEGIN");// Start the transaction definition 
if(!mysql_query("insert into trans (id) values('2')"))
{
mysql_query("ROOLBACK");// Determines rollback when execution fails 
}
if(!mysql_query("insert into trans (id) values('4')"))
{
mysql_query("ROOLBACK");// Determines failed rollback 
}
mysql_query("COMMIT");// Perform transactions 
mysql_close($handler);
?>