PHP MySQL and mysqli transactions

The official description of the three main APIs that PHP connects to the MySQL database server is as follows:

http://php.net/manual/zh/mysqli.overview.php

PHP MySQL and mysqli transactions

In PHP, MYSQLI has already encapsulated the operation of MySQL transactions well. The following example:

$sql 1 = "Update User set scorecount = Scorecount +10 where id= ' 123456 '"; $sql 2 = "Update Scoredetail  Set Fscore = 300 where id= ' 123456 '; $sql 3 = "INSERT INTO  scoredetail Id,score" values (' 123456 ', "); $mysqli = new mysqli (' localhost ') , ' root ', ' ', ' db_lib2test '); $mysqli->autocommit (false); Start Transaction $mysqli->query ($sql 1), $mysqli->query ($sql 2), if (! $mysqli->errno) {$mysqli->commit (); Echo ' OK ';} else {echo ' err '; $mysqli->rollback ();}

Here, we then use the PHP MySQL series function to perform the transaction.

$sql 1 = "Update User set scorecount = Scorecount +10 where id= ' 123456 '"; $sql 2 = "Update Scoredetail  Set Fscore = 300 where id= ' 123456 ' "; $sql 3 =" INSERT INTO  scoredetail Id,score) VALUES (' 123456 ', ') '; $conn = mysql_connect (' LocalHost ', ' root ', '); mysql_select_db (' db_lib2test '); mysql_query (' Start transaction ');//mysql_query (' SET Autocommit=0 '); mysql_query ($sql 1); mysql_query ($sql 2); if (Mysql_errno ()) {mysql_query (' rollback '); Echo ' err ';} else {mysql_query (' commit '); Echo ' OK ';} mysql_query (' SET autocommit=1 ');//mysql_query ($sql 3);

It is important to note that

1. MyISAM: Transactions are not supported for read-only programs to improve performance
2. InnoDB: Supports acid transactions, row-level locks, concurrency
3. Berkeley DB: Support transactions

It is also important to note that the default behavior of MySQL is to execute a COMMIT statement after each SQL statement executes, effectively independently of each statement as a single transaction.

But often, we need to execute multiple SQL statements when using transactions. This requires us to manually set the MySQL Autocommit property to 0, which defaults to 1.

Also, open a transaction explicitly by using the START transaction statement. As in the example above.

If not, what will be the result?

We will//mysql_query (' SET autocommit=0′) in the second code above; and//mysql_query ($sql 3); The comment is removed and then executed.

At this point, mysql_query ($sql 3) execution is not insert into the database.

If we will//mysql_query (' SET autocommit=1′); The comment of this sentence is removed, then mysql_query ($sql 3); Will execute successfully.

Typically a commit or ROLLBACK statement executes when a transaction is completed, but some DDL statements, and so on, implicitly trigger a commit.

such as 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 another example to see.

$sql 1 = ' CREATE TABLE scoredetail_new (id int) '; $sql 2 = ' Rename table Scoredetail to Scoredetail_bak '; $sql 3  = ' Rename T Able scoredetail_new to Scoredetail '; $mysqli = new mysqli (' localhost ', ' root ', ' ', ' db_lib2test '); $mysqli Autocommit (false);//Start things $mysqli->query ($sql 1), $mysqli->query ($sql 2); $mysqli->query ($sql 3); if (!$ Mysqli->errno) {$mysqli->commit (); Echo ' OK ';} else {echo ' err '; $mysqli->rollback ();}

In the above example, if the $SQL2 execution error occurs, $sql 1 will still be executed. Why is it?

Because rename executes, MySQL defaults to commit, and then executes rename.

PHP MySQL and mysqli transactions