Mysql transaction processing and mysql transaction

Mysql transaction processing and mysql transaction

Transaction processing is widely used in various management systems. For example, in the personnel management system, many synchronous database operations require transaction processing. 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 constitute a transaction!
Deleted SQL statement
Deletefrom userinfo where ~~~
Delete from mail where ~~
Delete fromarticle where ~~
~~
If no transaction is processed, if an error occurs during the deletion process and only the first sentence is executed, the consequences are unimaginable!
But it is processed by transactions. 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, transaction processing must be taken into account in business-level applications!



View inodb Information
Shell>/usr/local/mysql-u root-p
Mysql> showvariables 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.05sec)
If so, we can create a table that supports transaction processing.



MYSQL transaction processing function!

Author: Feifengxlq Email: feifengxlq@sohu.com
I have always thought that MYSQL does not support transaction processing, so it is always troublesome to process data from multiple data tables (I have to write it into a text file, write data to the database only when the system is reloaded to prevent errors )~ Today, we found that MYSQL Databases have supported the transaction function since MYSQL 4.1. It is said that MYSQL 5.0 will introduce the Stored Procedure pai_^.
Let's briefly introduce the transaction! A transaction is the unit of execution of a DBMS. It consists of a finite sequence of database operations. But not any database operation sequence can become a transaction. Generally, transactions must meet four conditions (ACID)
Atomicity (Autmic): when a transaction is executed, "Do not do it, or do it all !", That is to say, the Department is not allowed to perform tasks separately. Even if the transaction cannot be completed due to a fault, the impact on the database should be eliminated during rollback!
Consistency: the transaction operation should change the database 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 to form a transaction!
Isolation: if multiple transactions are executed concurrently, it should be the same as the independent execution of each transaction!
Durability: A successfully executed transaction has a lasting effect on the database. Even if the database fails or fails, the transaction can be recovered!

There are two main methods to process MYSQL transactions.
1. Use begin, rollback, and commit to implement
Start a transaction
Rollback transaction rollback
Commit transaction validation
2. directly use set to change the mysql automatic submission Mode
MYSQL is automatically submitted by default, that is, when you submit a QUERY, It will be executed directly! We can use
Set autocommit = 0 disable automatic submission
Set autocommit = 1 enable automatic submission
To process the transaction.
Note that when setautocommit = 0 is used, all your SQL statements will be processed as transactions until you confirm with commit or roll back, note that a new transaction is also started when you end the transaction! In the first method, only the current transaction is used!
The first method is recommended!
In MYSQL, only INNODB and BDB Data Tables support transaction processing! Other types are not supported! (Remember !)

Next time, let's talk about how to lock and unlock MYSQL Data Tables!

MYSQL5.0 WINXP passed the test ~ Pai_^

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, 0 rows 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"); // It is set to not automatically submitted because MYSQL runs immediately by default.
Mysql_query ("BEGIN"); // start transaction Definition
If (! Mysql_query ("insertinto trans (id) values ('2 ')"))
{
Mysql_query ("ROOLBACK"); // determines whether to roll back When execution fails.
}
If (! Mysql_query ("insertinto 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 );
?>