Examples of PDO Transaction Operations in PHP

This article illustrates the PDO transaction operation in PHP. Share it for your reference, as follows:

Summary:

Multiple sql operations (addition, deletion and modification) are taken as one operation unit, which either succeeds or fails.

Single piece of data does not need transaction processing

The table to be manipulated must be of type innoDB (transaction supported)

Common table types of MySQL: MyISAM (non-transactional) has fast speed of addition and deletion, and InnodB (transactional) has high security

Change the type of the table to innoDB type

mysql> alter table stu engine=innodb;

Use:

Added on the basis of PDO preprocessing, in the following format:

try{
$m->beginTransaction();// Open a transaction 
//PDO Preprocessing and execution of statements ...
$m->commit();// Commit transaction 
}catch(PDOException $e){
$m->rollBack();// Transaction rollback 
// Related error handling 
}

Example:

$m = new PDO($dsn,$user,$pwd);
$m->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
try{
$m->beginTransaction();// Open a transaction 
$stmt=$m->prepare("insert into stu(name,sex,age,classid)values(?,?,?,?)");
$data=array(
array("user1",1,22,"lamp76"),
array("user2",1,20,"lamp76"),
array("user3",0,22,"lamp76")
);
foreach($data as $v){
$stmt->execute($v);
echo $m->lastInsertId();
}
$m->commit();
echo " Submission succeeded! ";
}catch(PDOException $e){
$m->rollBack();// Rollback 
die(" Submission failed! ");
}

Add: Another example analysis of php transaction processing and rollback operation using PDO in php

Overview:

A transaction (transaction) is a sequence of query and/or update statements. Using begin, start, transaction
Start a transaction, rollback rolls back the transaction, and commit commits the transaction. After starting 1 transaction, there can be several SQL queries or update statements, each SQL
After committing execution, there should also be a statement to judge whether it is executed correctly, so as to determine whether the next step is rolled back, and if it is executed correctly, the transaction will be committed finally.
Once the transaction is rolled back, the database remains in the state before starting the transaction. Just like an edited file exits without saving it, it naturally keeps the original file.
Therefore, a transaction can be treated as an atomic operation, and SQL in a transaction is either executed completely or not executed at all.

MYSQL transaction steps for PDO in PHP:

① Turn off automatic submission

② Open transaction processing

③ If there is an exception, it will automatically throw an exception prompt and roll back

④ Turn on automatic submission

Note:

mysql Only this InnoDB driver supports transactions, and the default MyIsAM driver does not.

Example:

<?php
try{
// Finally, turn off automatic submission 
$pdo=new pdo("mysql:host=localhost;dbname=mydb","root","root", array(PDO::ATTR_AUTOCOMMIT=>0));
// This is by setting the property method to turn off automatic submission and the above functions 1 Sample 
//$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
// Turn on exception handling 
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
echo " Database connection failed: ".$e->getMessage();
exit;
}
/*
*  Transaction processing 
*
*  Zhang 3 Congli 4 I bought it there 1 Taiwan  2000  Yuan computer 
*  From Zhang 3 Deducted from the account  2000 Yuan 
*  Xiang Li 4 Add to the account  2000 Yuan 
*  Reduce from the commodity list 1 Computer 
* MyIsAM InnoDB
*/
try{
$pdo->beginTransaction();// Open a transaction 
$price=500;
$sql="update zhanghao set price=price-{$price} where id=1";
$affected_rows=$pdo->exec($sql);
if(!$affected_rows)
throw new PDOException(" Zhang 3 Rollout failed ");// That error throws an exception 
$sql="update zhanghao set price=price+{$price} where id=3";
$affected_rows=$pdo->exec($sql);
if(!$affected_rows)
throw new PDOException(" Xiang Li 4 Transfer failure ");
echo " The deal was successful! ";
$pdo->commit();// Submit on successful transaction 
}catch(PDOException $e){
echo $e->getMessage();
$pdo->rollback();
}
// Automatic submission, if the final automatic submission is not, the transfer is unsuccessful 
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,1);
// Setting Error Reporting Mode  ERRMODE_SILENT ERRMODE_WARNING

For more readers interested in PHP related contents, please check the topics on this site: "Summary of PHP Database Operation Skills Based on pdo", "Summary of php+Oracle Database Programming Skills", "Encyclopedia of PHP+MongoDB Database Operation Skills", "Introduction to php Object-Oriented Programming", "Usage Summary of php String (string)", "Introduction to php+mysql Database Operation Skills" and "Summary of php Common Database Operation Skills"

I hope this article is helpful to everyone's PHP programming.