MySQL transaction processing

In the management system of transactions have a wide range of applications, so that our personnel management system, most of the synchronous database operation is very necessary to use transaction processing. For example, in a 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. These database operation statements form a transaction!

Delete theSQLStatement
Deletefrom userinfo where ~ ~ ~
Delete from Mail where ~ ~
Delete Fromarticle where~~
~~
If there is no transaction processing. In the process of your deletion, if there is an error, just run the first sentence, then the consequences are unimaginable!
but with transaction processing. If you delete an error, you just need torollbackis able to cancel the delete operation (in fact, you just don't haveCommityou are not actually running the delete operation)

generally speaking. In business-grade applications, you must consider transaction processing!



ViewInodbInformation
Shell>/usr/local/mysql-u Root-p
Mysql> showvariables 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.05SEC)
Assuming this is the case, then we can create a table that supports transaction processing to try it out.



MYSQL Transaction processing Function!

feifengxlq email:[email protected]
I've always thoughtMYSQLtransaction processing is not supported, so it is always cumbersome to work with data from multiple data tables (I have to write it to a text file and write to the database when the system is loaded again to prevent errors) ~ Found todayMYSQLDatabase from4.1Start supporting transactional functionality. is said5.0will introduce stored procedures^_^
let's start with a brief introduction to business. Transaction isDBMSto run the unit. It is made up of a limited number of database operation sequences.

But not the random database sequence of operations can become a transaction.

In general, transactions are required to meet4Conditions (ACID)
atomicity (autmic): The transaction is operational in nature. To do "either don't do it or do it all." "is to say that you do not agree with the transaction part to run. Even if the transaction cannot be completed due to a failure, therollbackalso need to eliminate the impact on the database!

Consistency ( Consistency ): A transactional operation should make the database transition from a consistent state to a consistent state!

Take online shopping For example, you just have to let the merchandise out of the library. And let the goods into the customer's shopping basket talents constitute business.
Isolation ( Isolation ): Assume that multiple transactions run concurrently. Should run as separate transactions!

Persistence (Durability): A successfully running transaction has a lasting effect on the database. Even if the database should fail in error, it should be recoverable!

MYSQL There are two main ways of dealing with transactions.
1 , withBegin,rollback,committo achieve
Begin Start a transaction
Rollback Transaction Rollback
Commit Transaction Confirmation
2 , direct useSetto changeMySQL's own proactive commit mode
MYSQL the default is to commit voluntarily. Which means you submit aQUERY, it will run directly!

We are able to pass
Set autocommit=0 prohibit yourself from submitting voluntarily
Set autocommit=1 Open your own unsolicited submission
to implement transaction processing.
but notice when you usesetautocommit=0the time. All of your futureSQLwill be handled as transactions. Until you useCommitConfirm orrollbackend, note that when you end this transaction, you also open a new transaction!

Press the first method to just take the current as a transaction!
The first method of personal recommendation!
MYSQL only has INNODB and BDB types of data tables to support transaction processing. Other types are not supported! Remember

）

next time you are freeMYSQLthe data sheet lock and Unlock!

MYSQL5.0 WINXP Test through ~^_^

Mysql>use test;
Database changed
mysql> CREATE TABLE ' dbtest ' (
ID Int (4)
) Type=innodb;
Query OK, 0 rowsaffected, 1 Warning (0.05 sec)

Mysql> SELECT * from Dbtest
;
Empty Set (0.01 sec)

Mysql> begin;
Queryok, 0 rows Affected (0.00 sec)

mysql> INSERT INTO Dbtestvalue (5);
Query OK, 1 row Affected (0.00 sec)

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

Mysql> commit;
Query OK, 0 rows affected (0.00SEC)

Mysql> select * from Dbtest;
+------+
| ID |
+------+
| 5 |
| 6 |
+------+
2 rows in Set (0.00 sec)

Mysql> begin;
Query OK, 0 rows affected (0.00SEC)

mysql> INSERT into dbtest values (7);
Query OK, 1row Affected (0.00 sec)

mysql> rollback;
Query OK, 0rows Affected (0.00 sec)

mysql> SELECT * Fromdbtest;
+------+
| ID |
+------+
| 5 |
| 6|
+------+
2 rows in Set (0.00SEC)

Mysql>

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

[PHP]
Functiontran ($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 ("setautocommit=0");//set to not commit themselves voluntarily, due toMYSQLrun now by default
mysql_query ("BEGIN");// start a transaction definition
if (!mysql_query ("Insertinto trans (ID) VALUES (' 2 ')"))
{
mysql_query ("Roolback");// infer rollback When a run fails
}
if (!mysql_query ("Insertinto trans (ID) VALUES (' 4 ')"))
{
mysql_query ("Roolback");// Infer run-time failure rollback
}
mysql_query ("COMMIT");// Running Transactions
Mysql_close ($handler);
?>

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

MySQL transaction processing