mysql--Transaction Processing

Transaction processing in various management systems have a wide range of applications, such as personnel management system, many synchronous database operations need to use the transaction processing. For example, in the 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 mailbox, articles and so on, so that these database operation statements constitute a transaction!
SQL statement to delete
Delete from userinfo where ~ ~ ~
Delete from Mail where ~ ~
Delete from article where~~
~~
If there is no transaction, in the process of your deletion, assuming that the error, only executed the first sentence, then the consequences are unimaginable!
But with transaction processing. If you delete an error, you can cancel the delete operation as long as you rollback (in fact, if you do not have a commit, you do not actually perform the delete operation)

In general, in business-grade applications, you must consider transaction processing!



View INODB Information
Shell>/usr/local/mysql-u Root-p
Mysql> Show variables like "have_%"
The system prompts you to:
+------------------+-------+
| 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 transaction processing to try it out.



MySQL's transaction processing function!

FEIFENGXLQ Email:[email protected]
I've always thought that MySQL doesn't support transactions, so it's always cumbersome to work with data from multiple data tables (I have to write it to a text file, Write the database to prevent errors when the system reloads) ~ Today, the MySQL database was found to support transactional functionality from 4.1, and it is said that 5.0 will introduce stored procedures ^_^
Let's start with a brief introduction to the business! A transaction is the execution unit of a DBMS. It is made up of a limited number of database operation sequences. But not any database operation sequence can be a transaction. In general, transactions are required to meet 4 conditions (ACID)
Atomicity (autmic): The transaction is in execution, to do "either do it or do it all!" ", which means that the transactional part is not allowed to execute. Even if the transaction can not be completed because of the failure, also eliminate the impact on the database when rollback!
Consistency (consistency): Transactional operations should allow the database to transition from one consistent state to another consistent state! Take online shopping, you only have to let the goods out of the library, and let the goods into the customer's shopping basket to constitute a business!
Isolation (Isolation): If more than one transaction executes concurrently, it should be done independently of each transaction!
Persistence (Durability): A successful execution of a transaction has a lasting effect on the database and should be recoverable even if the database should fail in error!

There are two main ways to handle MySQL transactions.
1, with Begin,rollback,commit to achieve
Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Acknowledgement
2, directly with set to change the MySQL automatic submission mode
MySQL is automatically submitted by default, that is, you submit a query, it is executed directly! We can pass
Set autocommit=0 prohibit auto-commit
Set autocommit=1 turn on auto-commit
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 commit confirmation or rollback, and notice that when you end the transaction, you start a new transaction! Press the first method to only present the current as a transaction!
The first method of personal recommendation!
Only InnoDB and BDB types of data tables in MySQL can support transactional processing! Other types are not supported! Remember ）

Next time you are free to say MySQL data table locking and unlocking!

MYSQL5.0 winxp under test through ~ ^_^

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>

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

[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 auto-commit because MySQL defaults to execute immediately
mysql_query ("Begin");//Start Transaction definition
if (!mysql_query ("INSERT into trans (ID) VALUES (' 2 ')")
{
mysql_query ("Roolback");//Determine rollback when execution fails
}
if (!mysql_query ("INSERT into trans (ID) VALUES (' 4 ')")
{
mysql_query ("Roolback");//Judgment execution failed rollback
}
mysql_query ("COMMIT");//Perform Transaction
Mysql_close ($handler);
?>

mysql--Transaction Processing