PHPmysql and mysqli transaction instructions _ PHP Tutorial

Source: Internet
Author: User
Tags mysql functions
Instructions for Use of PHPmysql and mysqli transactions. First, mysqli connections are permanent connections, while mysql does not. What does it mean? Every time mysql is used for the second time, a new process is re-opened. while mysqli is the first, mysqli is a permanent connection, while mysql is a non-permanent connection. What does it mean? Mysql will re-open a new process whenever it is used for the second time, while mysqli only uses the same process, which can greatly reduce the pressure on the server.

Mysqli encapsulates some advanced operations, such as transactions, and many available methods in the database operation process.

A lot of applications are mysqli transactions.

For example:

The code is as follows:


$ Mysqli = new mysqli ('localhost', 'root', '', 'DB _ Lib2Test ');
$ Mysqli-> autocommit (false); // start transaction
$ Mysqli-> query ($ sql1 );
$ Mysqli-> query ($ sql2 );
If (! $ Mysqli-> errno ){
$ Mysqli-> commit ();
Echo 'OK ';
} Else {
Echo 'err ';
$ Mysqli-> rollback ();
}


In PHP, mysqli has well encapsulated mysql transaction-related operations. Example:

The code is as follows:

$ Sql1 = "update User set ScoreCount = ScoreCount + 10 where ID = '000000 '";
$ Sql2 = "update ScoreDetail set FScore = 300 where ID = '000000 '";
$ Sql3 = "insert into ScoreDetail ID, Score) values ('000000', 60 )";

$ Mysqli = new mysqli ('localhost', 'root', '', 'DB _ Lib2Test ');
$ Mysqli-> autocommit (false); // start the transaction
$ Mysqli-> query ($ sql1 );
$ Mysqli-> query ($ sql2 );
If (! $ Mysqli-> errno ){
$ Mysqli-> commit ();
Echo 'OK ';
} Else {
Echo 'err ';
$ Mysqli-> rollback ();
}

Here, we use php mysql functions to execute transactions.

The code is as follows:

$ Sql1 = "update User set ScoreCount = ScoreCount + 10 where ID = '000000 '";
$ Sql2 = "update ScoreDetail set FScore = 300 where ID = '000000 '";
$ Sql3 = "insert into ScoreDetail ID, Score) values ('000000', 60 )";

$ Conn = mysql_connect ('localhost', 'root ','');
Mysql_select_db ('Db _ Lib2Test ');
Mysql_query ('Start transaction ');
// Mysql_query ('set autocommit = 0 ');

Mysql_query ($ sql1 );
Mysql_query ($ sql2 );
If (mysql_errno ()){
Mysql_query ('rollback ');
Echo 'err ';
} Else {
Mysql_query ('commit ');
Echo 'OK ';
}

// Mysql_query ('set autocommit = 1 ');
// Mysql_query ($ sql3 );


Note that,

MyISAM: transactions are not supported and used by Read-only programs to improve performance.
InnoDB: supports ACID transactions, row-level locks, and concurrency.
Berkeley DB: supports transactions.
Note that the default behavior of MySQL is to execute a COMMIT statement after each SQL statement is executed, thus effectively separating each statement into a transaction.

However, we often need to execute multiple SQL statements when using transactions. In this case, we need to manually set the autocommit attribute of MySQL to 0, and the default value is 1.

At the same time, use the start transaction statement to explicitly open a TRANSACTION. The preceding example is as follows.

If this is not done, what will happen?

In the second code above, we will remove the comments of // mysql_query ('set autocommit = 0'); and // mysql_query ($ sql3); and then execute.

At this time, mysql_query ($ sql3) execution will not be inserted into the database.

If we delete the/mysql_query ('set autocommit = 1'); comment in this sentence, mysql_query ($ sql3); will be executed successfully.

A transaction is usually completed only when a COMMIT or ROLLBACK statement is executed, but some DDL statements will implicitly trigger COMMIT.

For example, the following statement

ALTER FUNCTION
ALTER PROCEDURE
ALTER TABLE
BEGIN
CREATE DATABASE
CREATE FUNCTION
CREATE INDEX
CREATE PROCEDURE
CREATE TABLE
DROP DATABASE
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP TABLE
UNLOCK TABLES
LOAD MASTER DATA
LOCK TABLES
RENAME TABLE
TRUNCATE TABLE
Set autocommit = 1
START TRANSACTION

Let's take an example.

The code is as follows:

$ Sql1 = 'create table ScoreDetail_new (id int )';
$ Sql2 = 'Rename table ScoreDetail to ScoreDetail_bak ';
$ Sql3 = 'Rename table ScoreDetail_new to ScoreDetail ';

$ Mysqli = new mysqli ('localhost', 'root', '', 'DB _ Lib2Test ');
$ Mysqli-> autocommit (false); // start transaction
$ Mysqli-> query ($ sql1 );
$ Mysqli-> query ($ sql2 );
$ Mysqli-> query ($ sql3 );
If (! $ Mysqli-> errno ){
$ Mysqli-> commit ();
Echo 'OK ';
} Else {
Echo 'err ';
$ Mysqli-> rollback ();
}

In the preceding example, if $ sql2 fails to be executed, $ sql1 will still be executed. Why?

Because when the rename is executed, mysql will first execute commit and then rename by default.

Note:

In MYSQL, only INNODB and BDB data tables support transaction processing! Other types are not supported!

* **: The default MYSQL database 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:/appserv/mysql/my. ini file, locate the skip-InnoDB file, add # to the front, and save the file.

2. enter services. msc in the running state to restart the mysql service.

3. in phpmyadmin, mysql-> show engines; (or execute mysql-> show variables like 'have _ % ';). if InnoDB is YES, InnoDB is supported.
This means that transaction is supported.

Mysql and mysql are non-permanent connections. What does it mean? Mysql will re-open a new process whenever it is used for the second time, while mysqli will...

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.