"22" mysqli transaction processing

Fundamentals of transaction processing transactions

1. If you do not open a transaction, execute a SQL and immediately persist the data. Visible: The default MySQL execution of SQL statements is automatically submitted!
2. If the transaction is turned on, the auto-commit feature is turned off, and the commit is changed to automatic commit

The auto-commit feature is stored in a variable called autocommit on the server. You can complete the modification (set autocommit=0;) by using the Set variable name = variable value, and the auto-commit feature is turned off, and you need to perform a commit manually to persist the data.

Characteristics of the transaction (acid)

• Atomicity (atomicity)-either succeeds or fails. If it succeeds, it must be fully applied to the database, and if it fails, it has no effect on the database.
• Consistency (consistency)-means that a transaction is in a consistent state before and after execution
• Isolation (lsolation)-the isolation relationship between multiple transactions
• Persistence (durability)-once submitted it is impossible to be rolled back

Operation of the transaction

1. Rollback (rollback)
2. Commit (Commit)
3. Start a transaction (begin, start transaction)

The Grammar of Things

• Open transaction (start transation;)
• Do save point (savepoint A;)
• Perform actions
• Rollback (rollback to A;)
• Commit (Commit)

Mysqli manipulating data (object-oriented style)

<?PHP//Database: CREATE TABLE account (ID int primary key,balance float);//Bank Transfer    $mysqli=NewMysqli ("localhost", ' root ', ' ', ' test ')); if($mysqli-connect_error) {        Echo $mysqli-Connect_error; }    //set the submission to False    $mysqli->autocommit (FALSE); $sql 1= "Update account22 set balance=balance+2 where id= ' 1 ';"; $sql 2= "Update account1 set balance=balance-2 where id= ' 2 ';"; $a=$mysqli->query ($sql 1); $b=$mysqli->query ($sql 2); //determine if the statement is true    if(!$a|| !$b) {        //One of the unsuccessful rollback is not committed        Echo"Failed rollback".$mysqli-error; $mysqli-rollback (); }Else{        //all success is submitted        EchoSuccess; $mysqli-commit (); }    $mysqli-close ();?>

The problem encountered in learning this knowledge point: The code is not a problem, in the execution of the transaction, rollback does not take effect.

Cause: My database and tables are not InnoDB and BDB types (only InnoDB and BDB types of data tables in MySQL can support transactional processing!) Other types are not supported! ）

Workaround: Modify the database and table to the InnoDB type. Modification Method Reference: http://aiezu.com/article/83.html

Benefits of pre-processing

• High efficiency and fast execution speed
• High security to prevent SQL injection

Example:

preprocessing DML statements

1<?PHP2     $mysqli=NewMysqli ("127.0.0.1", "Root", "" "," Test "));3     if(!$mysqli-connect_error) {4         Echo $mysqli-Connect_error;5     }6 //preprocessing and binding7     $sql= "INSERT into user1 (name,password,email,age) VALUES (?,?,?,?);";8     //Prepare SQL statement ready to execute9     $sql _stmt=$mysqli->prepare ($sql) or die($mysqli-error);Ten     //parameter Binding One //bind_param: Binding a variable as a parameter to a preprocessing statement A //The type and order of the place to correspond -     $sql _stmt->bind_param (' SSSI ',$name,$Password,$Email,$age); - //set parameters and perform the     $name= ' Sunwukong '; -     $Password= ' 123 '; -     $Email= ' [email protected] '; -     $age= ' 200 '; +     //execute the prepared SQL -     $b=$sql _stmt-execute (); + //Add a second A     $name= ' Zhubajie '; at     $Password= ' 123 '; -     $Email= ' [email protected] '; -     $age= ' 200 '; -     $b=$sql _stmt-execute (); - //determine if insert succeeded -     if(!$b) { in         Echo"Operation failed."$sql _stmt-error; -}Else{ to         Echo"Operation succeeded"; +     } -     //Release the     $mysqli-close (); *?>

Preprocessing DQL statements

1<?PHP2     $mysqli=NewMysqli ("127.0.0.1", "Root", "" "," Test "));3     if(!$mysqli-connect_error) {4         Echo $mysqli-Connect_error;5     }6     //1. Preprocessing and binding7 //2. Parameter Settings8 //3. Execution9     $sql= "SELECT name from User1 where Id>?";Ten     $stmt=$mysqli->prepare ($sql) or die($mysqli-error); One     $id=3; A     $stmt->bind_param ("I",$id); -     //Bind_result: Binding a variable to a prepared statement for the result store -     $stmt->bind_result ($name); the     $stmt-execute (); -      while($stmt-Fetch ()) { -         Echo $name." <br/> "; -     }

$stmt->free_result ();
$stmt->close ();

19

Problems encountered in the study

1. (Preprocessing DQL statements) always prompt when executing statements "Fatal Error:call to a member function Bind_param () ' On ' Boolean ' D:\wamp64\www\shiwu2.php on ' line ", always in the bind parameter where to find the problem.

Cause: The SQL statement was incorrectly written. Solution: Judge whether a statement executes an error and prints an error message when executing SQL

2. (Preprocessing DQL statement) the parameters of line Nineth and line 12th correspond to-name

"22" mysqli transaction processing