The nested transaction example here is great, but it's missing a key piece of the puzzle. Commits will commit everything, I only wanted commits to actually commit when the outermost commit has been completed. This can be done in InnoDB with savepoints.
<?php
class Database extends PDO
{
protected $transactionCount = 0;
public function beginTransaction()
{
if (!$this->transactionCounter++) {
return parent::beginTransaction();
}
$this->exec('SAVEPOINT trans'.$this->transactionCounter);
return $this->transactionCounter >= 0;
}
public function commit()
{
if (!--$this->transactionCounter) {
return parent::commit();
}
return $this->transactionCounter >= 0;
}
public function rollback()
{
if (--$this->transactionCounter) {
$this->exec('ROLLBACK TO trans'.$this->transactionCounter + 1);
return true;
}
return parent::rollback();
}
}
PHP - Manual: PDO::beginTransaction
2024-11-15
PDO::beginTransaction
(PHP 5 >= 5.1.0, PHP 7, PHP 8, PECL pdo >= 0.1.0)
PDO::beginTransaction — 启动一个事务
说明
PDO::beginTransaction(): bool
关闭自动提交模式。自动提交模式被关闭的同时，通过 PDO 对象实例对数据库做出的更改直到调用 PDO::commit() 结束事务才被提交。调用 PDO::rollBack() 将回滚对数据库做出的更改并将数据库连接返回到自动提交模式。
包括 MySQL 在内的一些数据库，当发出一条类似 DROP TABLE 或 CREATE TABLE 这样的 DDL 语句时，会自动进行一个隐式地事务提交。隐式地提交将阻止你在此事务范围内回滚任何其他更改。
返回值
成功时返回 true， 或者在失败时返回 false。
范例
示例 #1 回滚一个事务
下面例子在回滚此更改前开始一个事务并发出两条修改数据库的语句。但在 MySQL 中，DROP TABLE 语句自动提交事务，使得在此事务中的任何更改都不会被回滚。
<?php
/* 开始一个事务，关闭自动提交 */
$dbh->beginTransaction();
/* 更改数据库架构及数据 */
$sth = $dbh->exec("DROP TABLE fruit");
$sth = $dbh->exec("UPDATE dessert
SET name = 'hamburger'");
/* 识别出错误并回滚更改 */
$dbh->rollBack();
/* 数据库连接现在返回到自动提交模式 */
?>
add a note
User Contributed Notes 16 notes
steve at fancyguy dot com ¶
7 years ago
bitluni ¶
9 years ago
You can generate problems with nested beginTransaction and commit calls.
example:
beginTransaction()
do imprortant stuff
call method
beginTransaction()
basic stuff 1
basic stuff 2
commit()
do most important stuff
commit()
Won't work and is dangerous since you could close your transaction too early with the nested commit().
There is no need to mess you code and pass like a bool which indicate if transaction is already running. You could just overload the beginTransaction() and commit() in your PDO wrapper like this:
<?php
class Database extends \\PDO
{
protected $transactionCounter = 0;
function beginTransaction()
{
if(!$this->transactionCounter++)
return parent::beginTransaction();
return $this->transactionCounter >= 0;
}
function commit()
{
if(!--$this->transactionCounter)
return parent::commit();
return $this->transactionCounter >= 0;
}
function rollback()
{
if($this->transactionCounter >= 0)
{
$this->transactionCounter = 0;
return parent::rollback();
}
$this->transactionCounter = 0;
return false;
}
//...
}
?>
drm at melp dot nl ¶
14 years ago
In response to "Anonymous / 20-Dec-2007 03:04"
You could also extend the PDO class and hold a private flag to check if a transaction is already started.
class MyPDO extends PDO {
protected $hasActiveTransaction = false;
function beginTransaction () {
if ( $this->hasActiveTransaction ) {
return false;
} else {
$this->hasActiveTransaction = parent::beginTransaction ();
return $this->hasActiveTransaction;
}
}
function commit () {
parent::commit ();
$this->hasActiveTransaction = false;
}
function rollback () {
parent::rollback ();
$this->hasActiveTransaction = false;
}
}
kesler dot alwin at gmail dot com ¶
6 years ago
please fix in answer #116669:
$this->exec('ROLLBACK TO trans'.$this->transactionCounter + 1);
with
$this->exec('ROLLBACK TO trans'.($this->transactionCounter + 1));
rjohnson at intepro dot us ¶
13 years ago
If you are using PDO::SQLITE and need to support a high level of concurrency with locking, try preparing your statements prior to calling beginTransaction() and you may also need to call closeCursor() on SELECT statements to prevent the driver from thinking that there are open transactions.
Here's an example (Windows, PHP version 5.2.8). We test this by opening 2 browser tabs to this script and running them at the same time. If we put the beginTransaction before the prepare, the second browser tab would hit the catch block and the commit would throw another PDOException indicating that transactions were still open.
<?php
$conn = new PDO('sqlite:C:\path\to\file.sqlite');
$stmt = $conn->prepare('INSERT INTO my_table(my_id, my_value) VALUES(?, ?)');
$waiting = true; // Set a loop condition to test for
while($waiting) {
try {
$conn->beginTransaction();
for($i=0; $i < 10; $i++) {
$stmt->bindValue(1, $i, PDO::PARAM_INT);
$stmt->bindValue(2, 'TEST', PDO::PARAM_STR);
$stmt->execute();
sleep(1);
}
$conn->commit();
$waiting = false;
} catch(PDOException $e) {
if(stripos($e->getMessage(), 'DATABASE IS LOCKED') !== false) {
// This should be specific to SQLite, sleep for 0.25 seconds
// and try again. We do have to commit the open transaction first though
$conn->commit();
usleep(250000);
} else {
$conn->rollBack();
throw $e;
}
}
}
?>
ludwig dot green at gmail dot com ¶
12 years ago
be aware that you also can not use TRUNCATE TABLE as this statement will trigger a commit just like CREATE TABLE or DROP TABLE
it is best to only use SELECT, UPDATE and DELETE within a transaction, all other statements may cause commits thus breaking the atomicity of your transactions and their ability to rollback
obviously you can use DELETE FROM <table> instead of TRUNCATE TABLE but be aware that there are differences between both statements, for example TRUNCATE resets the auto_increment value while DELETE does not.
Sbastien ¶
9 months ago
A way to use transaction and prepared statement to speed-up bulk INSERTs :
<?php
// ...
$insert = $pdo->prepare('INSERT INTO table (c1, c2, c3) VALUES (?, ?, ?)');
$bulk = 3_000; // To adjust according to your data/system
$rows = 0;
$pdo->beginTransaction();
while ($entry = fgetcsv($fp)) {
$insert->execute($entry);
if (++$rows % $bulk === 0) {
$pdo->commit();
$pdo->beginTransaction();
}
}
if ($pdo->inTransaction()) { // Remaining rows insertion
$pdo->commit();
}
geompse at gmail dot com ¶
12 years ago
With Oracle, any structure statement will do an implicit commit.
So : ALTER TABLE "my_table" DROP COLUMN "my_column";
Can't be rolled back !
Hope this will save time for others
eddi13 ¶
7 years ago
after TRUNCATE TABLE `table` just as DELETE FROM `table`, so if whole table was deleted, aborts the transaction. And the rollback will not be passible.
cristian at crishk dot com ¶
5 years ago
OK I'm finding a solution for "NESTED" transactions in MySQL, and as you know in the MySQL documentation says that it's not possible to have transactions within transactions. I was trying to use the Database class propossed here in http://php.net/manual/en/pdo.begintransaction.php but unfortunately that's wrong for many things related to the control flow that I have been solved with the following code (LOOK THE EXAMPLE AT THE END, CarOwner)
<?php
class TransactionController extends \\PDO {
public static $warn_rollback_was_thrown = false;
public static $transaction_rollbacked = false;
public function __construct()
{
parent :: __construct( ... connection info ... );
}
public static $nest = 0;
public function reset()
{
TransactionController :: $transaction_rollbacked = false;
TransactionController :: $warn_rollback_was_thrown = false;
TransactionController :: $nest = 0;
}
function beginTransaction()
{
$result = null;
if (TransactionController :: $nest == 0) {
$this->reset();
$result = $this->beginTransaction();
}
TransactionController :: $nest++;
return $result;
}
public function commit()
{
$result = null;
if (TransactionController :: $nest == 0 &&
!TransactionController :: $transaction_rollbacked &&
!TransactionController :: $warn_rollback_was_thrown) {
$result = parent :: commit();
}
TransactionController :: $nest--;
return $result;
}
public function rollback()
{
$result = null;
if (TransactionController :: $nest >= 0) {
if (TransactionController :: $nest == 0) {
$result = parent :: rollback();
TransactionController :: $transaction_rollbacked = true;
}
else {
TransactionController :: $warn_rollback_was_thrown = true;
}
}
TransactionController :: $nest--;
return $result;
}
public function transactionFailed()
{
return TransactionController :: $warn_rollback_was_thrown === true;
}
// to force rollback you can only do it from $nest = 0
public function forceRollback()
{
if (TransactionController :: $nest === 0) {
throws new \PDOException();
}
}
}
?>
Steel Brain ¶
7 years ago
The example is misleading, Typically data definition language clauses (DDL) will trigger the database engine to automatically commit. It means that if you drop a table, that query will be executed regardless of the transaction.
Ref-Mysql:
http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html
dbeecher at tekops dot com ¶
13 years ago
// If you need to set an ISOLATION level or LOCK MODE it needs to be done BEFORE you make the BeginTransaction() call...
//
// **note** you should always check result codes on operations and do error handling. This sample code
// assumes all the calls work so that the order of operations is accurate and easy to see
//
// THIS IS using the PECL PDO::INFORMIX module, running on fedora core 6, php 5.2.4
//
// This is the correct way to address an informix -243 error (could not position within table) when there
// is no ISAM error indicating a table corruption. A -243 can happen (if the table/indexes, etc., are ok)
// if a row is locked. The code below sets the LOCK MODE to wait 2 minutes (120 seconds) before
// giving up. In this example you get READ COMMITTED rows, if you don't need read committed
// but just need to get whatever data is there (ignoring locked rows, etc.) instead of
// "SET LOCK MODE TO WAIT 120" you could "SET ISOLATION TO DIRTY READ".
//
// In informix you *must* manage how you do reads because it is very easy to trigger a
// lock table overflow (which downs the instance) if you have lots of rows, are using joins
// and have many updates happening.
//
// e.g.,
$sql= "SELECT FIRST 50 * FROM mytable WHERE mystuff=1 ORDER BY myid"; /* define SQL query */
try /* create an exception handler */
{
$dbh = new PDO("informix:host=......");
if ($dbh) /* did we connect? */
{
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->query("SET LOCK MODE TO WAIT 120")
# ----------------
# open transaction cursor
# ----------------
if ( $dbh->beginTransaction() ) # explicitly open cursor
{
try /* open exception handler */
{
$stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT))
{
$data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\t" . $row[3] . "\t" . $row[4] . "\t" . $row[5] . "\t" . $row[6] . "\t" . $row[7] . "\n" . $row[8] ;
//print $data;
print_r($row);
};
$stmt = null;
}
catch (PDOException $e)
{
print "Query Failed!\n\n";
print "DBA FAIL:" . $e->getMessage();
};
$dbh->rollback(); # abort any changes (ie. $dbh->commit()
$dbh = null; # close connection
}
else
{
# we should never get here, it should go to the exception handler
print "Unable to establish connection...\n\n";
};
};
}
catch (Exception $e)
{
$dbh->rollback();
echo "Failed: " . $e->getMessage();
};
arth dot inbox at mail dot ru ¶
8 years ago
Strange behavior with pdo_firebird driver:
<?php
$dbh = new PDO ('','','',array( PDO :: ATTR_PERSISTENT=>true));
$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION );
$dbh -> exec ( "select 1;" ); //should be autocommitted
try
{
//$dbh->commit/rollback here fixes the issue;
$dbh -> beginTransaction (); // <- fails "with there is already an active transaction"
$dbh -> exec ( "select 1;" );
$dbh -> commit ();
}
catch ( Exception $e )
{
$dbh -> rollBack ();
}
?>
Anonymous ¶
14 years ago
beginTransaction will through a PDOException if you execute it while a PDO transaction is already active. Additionally the PDO engine doesn't seem to provide any way of determining if there is a transaction "in flight" so if you might be calling a function from within another function that starts a transaction you'll have to wrap the beginTransaction () call in a try .. catch block.
mtjo62 at gmail dot com ¶
8 years ago
For the PDO driver for the Firebird server, you have to explicitly disable autocommit to initiate the transaction as well as explicitly enable autocommit to commit it.
$dbh->setAttribute( PDO::ATTR_AUTOCOMMIT, 0 );
$dbh->beginTransaction();
/** Query block */
$dbh->commit();
$dbh->setAttribute( PDO::ATTR_AUTOCOMMIT, 1 );
sasi dot viragelet at gmail dot com ¶
5 years ago
When transaction gets your enemy:
try {
$pdo->beginTransaction();
// ... whatever db actions you have and then:
$sql_insert = 'INSERT INTO a (a_id, a_name, a_date)
VALUES (:a_id, :a_name CURRENT_TIMESTAMP)';
$stmt = $pdo->prepare($sql_insert);
$stmt->bindValue(':_id', $id);
$stmt->bindValue(':a_name', $name);
$stmt->execute();
$pdo->commit();
} catch (\PDOException $e) {
$pdo->rollBack();
http_response_code(500);
header("Content-type: text/plain");
echo $e;
exit();
}
Notice the sytax error? There's a missing comma that just slips through every gate of surveillance:
"...UES (:a_id, :a_name CURR..." :: between the to be binded value and current timestamp command. The missing comma will not generate any error no syntax error, no database error. Though this record will never or partially make it. This will just silently fail. The story gets rough if you build your other dependencies on the transactions unmovable foot-stones of trust.
Just imagine you have some basic records in your transaction that you really wanna ensure ends up correctly in your db. And these records have a foreign constraint relation with another record. (That of course if you'd include in the transaction would throw you a big red error(–for who don't get it: transaction will first attempt to write everything into a temporary place, where it will get checked before everything gets into its permanent place, so constraints cannot be checked for something that don't exist yet.)) So you have a constraint that's based on the trust PDO will throw an error if something gets screwed, so what, you'll just place that insertion after the commit was successful. (Without any doublecheck.)
What happens you will get some partial records in your database without a relation that just might be important. You will that way be able to build up very sophisticated strange behaviours in your application. So pay attention!
IT
PHP
编程语言
Linux
开发编程
科技
Elasticsearch
HTML/CSS/XML
网络
JAVA
NoSQL
数据库
面试
C/C++
Golang
Git
算法
操作系统
正则表达式
Redis
互联网
MySql
JavaScript
运维
软件
国际
架构设计
Mac OS
TCP/IP
Excel
Vim
Windows
Socket
Oracle
VR
MongoDB
Python
运营
MemCache
商业
硬件
电子
娱乐
设计
nginx
摄影
游戏
WordPress
HTTP
团建
数码电器
广告
--
php7 安装fileinfo扩展
preg_split — 通过一个正则表达式分隔字符串
php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题
[PHP] inet_pton/inet_ntop IP地址转换函数
array_multisort() 多字段排序
PHPMailer设置utf8 PHPMailer字符集CharSet
ADORecordSet对象
adodb手册
PHP 8.1
[鸟哥]PHP_INT_MIN 和 -9223372036854775808
opcache预加载
Composer的Packagist资源
mysql面试题
[鸟哥]PHP FFI详解 - 一种全新的PHP扩展方式
PHP7添加redis扩展
Packagist / Composer 中国全量镜像
PHP获取指定函数定义在哪个文件中及行号
PHP mkdir()写出来的权限与mode值不符合
adodb连接mysql多个数据库的问题
常用的php ADODB使用方法集锦