PHPmysql and mysqli transaction instructions

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

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

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. The US server and the Hong Kong server effectively separate 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 the comments.

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.