There are two main methods to process MYSQL transactions:

Source: Internet
Author: User
Tags mysql tutorial

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 automatic submission mode of the mysql tutorial.
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.
When you use set autocommit = 0, all your SQL statements will be processed as transactions until you use commit to confirm 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 commonly used!

In MYSQL, only INNODB and BDB Data Tables support transaction processing! Other types are not supported! The default MYSQL database tutorial engine is MyISAM, which does not support transactions! If you want MYSQL to support transactions, You can manually modify them:
The method is as follows:
1. Modify the c: apps tutorial ervmysqlmy. ini file, find skip-InnoDB, add # in front, and save the file.
2. Enter services. msc in the running state to restart the mysql service.
3. in the myadmin tutorial of php, mysql-> show engines; (or execute mysql-> show variables like 'have _ % ';) and check whether InnoDB is YES, that is, the database supports InnoDB. This means that transaction is supported.
4. When creating a table, you can select the InnoDB Engine for the Storage Engine. For a previously created table, you can use mysql-> alter table table_name type = InnoDB;
Or mysql-> alter table table_name engine = InnoDB; to change the data table engine to support transactions. </P>
SET? FOREIGN_KEY_CHECKS = 0;

-? ----------

-? Table? Structure? For? Open_article

-? ----------

CREATE? TABLE? 'Open _ article '? (

'Nid '? Int (11 )? NOT? NULL? Auto_increment,

'Norder '? Int (11 )? Default? '0 ′,

'Stopic '? Varchar (100 )? Default? NULL,

'Scontent '? Varchar (255 )? Default? NULL,

'Ddatetime '? Timestamp? NULL? Default? CURRENT_TIMESTAMP? On? Update? CURRENT_TIMESTAMP,

PRIMARY? KEY? ('Nid ')

)? ENGINE = InnoDB? DEFAULT? CHARSET = utf8;

 

<? PHP

Header ("Content-Type: text/html; charset = UTF-8 ″);

 

$ Host? =? "Localhost ";

$ User? =? "Root ";

$ Password? =? "Mypassword ";

$ Db? =? "Test_store_proc ";

$ Dblink? =? Mysql_connect ($ host ,? $ User ,? $ Password )? Or? Die ("Can't? Connect? To? Mysql ");

Mysql_select_db ($ db ,? $ Dblink );

Mysql_query ("SET? NAMES? UTF8 ″);

 

/*? Create a transaction? */?

Mysql_query ('start? Transaction ')? Or? Exit (mysql_error ());

 

// 1st insert statement:

$ SQL? =? "Insert? Into? Open_article? (NOrder ,? STopic ,? SContent )? Values? (0 ,? 'News? Main? Topic-1', 'information content-1 ′)";

If (! Mysql_query ($ SQL ))

{

Echo? $ SQL ?. ': <Br> '.? Mysql_errno ()?.? ":"?.? Mysql_error ()?.? "<Br> ";

Mysql_query ('rollback ')? Or? Exit ('rollbacking :'.? Mysql_error ());? // Judge rollback When execution fails

Exit;

}

 

// 2nd insert statement:

$ SQL? =? "Insert? Into? Open_article? (NOrder ,? STopic ,? SContent )? Values? (0 ,? 'News? Main? Topic-2', 'news content-2 ′)";

If (! Mysql_query ($ SQL ))

{

Echo? $ SQL ?. ': <Br> '.? Mysql_errno ()?.? ":"?.? Mysql_error ()?.? "<Br> ";

Mysql_query ('rollback ')? Or? Exit ('rollbacking :'.? Mysql_error ());? // Judge rollback When execution fails

Exit;

}

 

Mysql_query ('commit ')? Or? Exit (mysql_error ());? // Execute the transaction

 

Mysql_close ($ dblink );

?>

If you change 2nd SQL statements:
$ SQL = "insert into open_article (nOrder, sTopic2, sContent) values (0, 'news Main Topic-2', 'news content-2 ′)";
Because the sTopic2 field does not exist, the program will report an error and terminate. At this time, no records are inserted into the table, which means that 1st SQL statements have not taken effect and are actually rolled back by the transaction.
How can we see that the transaction is rolled back "? Because the open_article table has an automatic incremental field nId. When you re-correct 2nd SQL statements and run the program, you will find that the nId value jumped 1, indicating that the previous 1st SQL statements were indeed executed and then canceled.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.