Detailed explanation of the steps to use PDO transactions in PHP

This time I will bring you a detailed explanation of the steps for using PDO transactions in PHP. What are the precautions for using PDO transactions in PHP? Here are practical cases, let’s take a look.

Summary:

Treat multiple sql operations (addition, deletion, modification) as one operation unit, either all succeed or all fail.

No transaction processing is required for a single piece of data

The table being operated must be an innoDB type table (supporting transactions)

MySQL commonly used table types: MyISAM (non-transactional) addition, deletion and modification Fast speed, high security of InnodB (transactional)

Change the type of 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();//开启事务处理
//PDO预处理以及执行语句...
$m->commit();//提交事务
}catch(PDOException $e){
$m->rollBack();//事务回滚
//相关错误处理
}

Example:

$m = new PDO($dsn,$user,$pwd);
$m->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
try{
$m->beginTransaction();//开启事务处理
$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 "提交成功！";
}catch(PDOException $e){
$m->rollBack();//回滚
die("提交失败！");
}

Supplementary: Come again An example analysis of mysql transaction processing and rollback operation using PDO

Overview:

Transaction (transaction) is composed of query and/or A sequence of update statements. Use begin, start transaction
to start a transaction, rollback to roll back the transaction, and commit to commit the transaction. After starting a transaction, there can be several SQL queries or update statements. After each SQL
is submitted for execution, there should also be statements to determine whether it is executed correctly to determine whether to roll back in the next step. If all are executed correctly, Finally commit the transaction.
Once the transaction is rolled back, the database will remain in the state before the transaction was started. Just like if an edited file is exited without saving, the original appearance of the file will naturally be retained.
So, a transaction can be regarded as an atomic operation, and the SQL in the transaction will either be executed entirely or not at all.

MYSQL transaction processing steps for PDO in PHP:

①.Turn off automatic submission

②.Enable transaction processing

③ .If there is an exception, it will automaticallythrow an exceptionprompt and then rollback

④.Turn on automatic submission

Note:

mysql Only this InnoDB driver supports transaction processing, and the default MyIsAM driver does not support it.

Example:

<?php
try{
//最后是关闭自动提交
$pdo=new pdo("mysql:host=localhost;dbname=mydb","root","root", array(PDO::ATTR_AUTOCOMMIT=>0));
//这个是通过设置属性方法进行关闭自动提交和上面的功能一样
//$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
//开启异常处理
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
echo "数据库连接失败：".$e->getMessage();
exit;
}
/*
* 事务处理
*
* 张三从李四那里买了一台 2000 元的电脑
* 从张三帐号中扣出 2000元
* 向李四账号中加入 2000元
* 从商品表中减少一台电脑
* MyIsAM InnoDB
*/
try{
$pdo->beginTransaction();//开启事务处理
$price=500;
$sql="update zhanghao set price=price-{$price} where id=1";
$affected_rows=$pdo->exec($sql);
if(!$affected_rows)
throw new PDOException("张三转出失败");//那个错误抛出异常
$sql="update zhanghao set price=price+{$price} where id=3";
$affected_rows=$pdo->exec($sql);
if(!$affected_rows)
throw new PDOException("向李四转入失败");
echo "交易成功！";
$pdo->commit();//交易成功就提交
}catch(PDOException $e){
echo $e->getMessage();
$pdo->rollback();
}
//自动提交，如果最后不自动提交，转账是不成功的
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,1);
//设置错误报告模式 ERRMODE_SILENT ERRMODE_WARNING

I believe you have read the case in this article After mastering the method, please pay attention to other related articles on the php Chinese website for more exciting content!

Recommended reading:

Detailed explanation of the steps of using curl to copy IP and refer in php

Detailed explanation of the steps of implementing message queue in PHP MySQL

The above is the detailed content of Detailed explanation of the steps to use PDO transactions in PHP. For more information, please follow other related articles on the PHP Chinese website!