准备好的语句不能在 php 的事务中使用吗？

Question

我在 LAPP 环境（linux apache postgresql php）上工作，我只是想了解如何在事务中使用准备好的语句（如果可能的话）。

我希望代码能比文字更好地解释：

示例 1，简单事务：

BEGIN;
INSERT INTO requests (user_id, description, date) VALUES ('4', 'This dont worth anything', NOW());
UPDATE users SET num_requests = (num_requests + 1) WHERE id = '4';
--something gone wrong, cancel the transaction
ROLLBACK;
UPDATE users SET last_activity = NOW() WHERE id = '4'
COMMIT;

在上面的示例中，如果我理解正确的事务，数据库中唯一的影响将是最后一个活动的更新...是吗？

如果我尝试在 php 中使用该事务（同时使用 PDO 或 pg_ 方法），代码应该如下所示（示例 2）：

/* skip the connection */
pg_query($pgConnection, "BEGIN");
pg_query($pgConnection, "INSERT INTO requests (user_id, description, date) VALUES ('$id_user', 'This dont worth anything', NOW())");
pg_query($pgConnection, "UPDATE users SET num_requests = (num_requests + 1) WHERE id = '$id_user'");
//something gone wrong, cancel the transaction
pg_query($pgConnection, "ROLLBACK");
pg_query($pgConnection, "UPDATE users SET last_activity = NOW() WHERE id = '$id_user'");
pg_query($pgConnection, "COMMIT");

并且效果很好。 也许很难看，但似乎可以工作（建议总是受欢迎的）

无论如何，当我尝试将示例2与准备好的语句结合起来时，我的问题就出现了（我知道在示例2中使用准备好的语句不是很有用）

示例 3：

/* skip the connection */
pg_prepare($pgConnection, 'insert_try', "INSERT INTO requests (user_id, description, date) VALUES ('$1', '$2', $3)");
pg_query($pgConnection, "BEGIN");
pg_execute($pgConnection, 'insert_try', array($user_id, 'This dont worth anything', date("Y-m-d")));
/* and so on ...*/

嗯，示例 3 根本不起作用，如果事务需要回滚，则准备好的语句将有效。

那么，准备好的语句不能在事务中使用，还是我采取了错误的方式？

编辑：

尝试使用 PDO 后，我到达了这一点：

<?php
$dbh = new PDO('pgsql:host=127.0.0.1;dbname=test', 'myuser', 'xxxxxx');

$rollback = false;

$dbh->beginTransaction();

//create the prepared statements
$insert_order = $dbh->prepare('INSERT INTO h_orders (id, id_customer, date, code) VALUES (?, ?, ?, ?)');
$insert_items = $dbh->prepare('INSERT INTO h_items (id, id_order, descr, price) VALUES (?, ?, ?, ?)');
$delete_order = $dbh->prepare('DELETE FROM p_orders WHERE id = ?');

//move the orders from p_orders to h_orders (history)
$qeOrders = $dbh->query("SELECT id, id_customer, date, code FROM p_orders LIMIT 1");
while($rayOrder = $qeOrders->fetch(PDO::FETCH_ASSOC)){
    //h_orders already contain a row with id 293
    //lets make the query fail
    $insert_order->execute(array('293', $rayOrder['id_customer'], $rayOrder['date'], $rayOrder['code'])) OR var_dump($dbh->errorInfo());
    //this is the real execute
    //$insert_order->execute(array($rayOrder['id'], $rayOrder['id_customer'], $rayOrder['date'], $rayOrder['code'])) OR die(damnIt('insert_order'));
    //for each order, i move the items too
    $qeItems = $dbh->query("SELECT id, id_order, descr, price FROM p_items WHERE id_order = '" . $rayOrder['id'] . "'") OR var_dump($dbh->errorInfo());
    while($rayItem = $qeItems->fetch(PDO::FETCH_ASSOC)){
        $insert_items->execute(array($rayItem['id'], $rayItem['id_order'], $rayItem['descr'], $rayItem['price'])) OR var_dump($dbh->errorInfo());
    }
    //if everything is ok, delete the order from p_orders
    $delete_order->execute(array($rayOrder['id'])) OR var_dump($dbh->errorInfo());
}
//in here i'll use a bool var to see if anythings gone wrong and i need to rollback,
//or all good and commit
$dbh->rollBack();
//$dbh->commit();
?>

上面的代码失败并显示以下输出：

数组(3) { [0]=>; 字符串(5)“00000”[1]=> int(7) [2]==> string(62)“错误：重复的键违反了唯一约束“id_h_orders””}

数组(3) { [0]=>; 字符串(5)“25P02”[1]=> int(7) [2]==> string(87)“错误：当前事务被中止，命令被忽略，直到事务块结束”}

致命错误：在第 23 行 /srv/www/test-db/test-db-pgsql-08.php 中的非对象上调用成员函数 fetch()

那么，似乎当第一次执行失败时（id 293 的那个）事务会自动中止... PDO 会自动回滚还是其他什么？

我的目标是完成第一个大 while 循环，最后使用 bool var 作为标志，决定是否回滚或提交事务。

Answer 1

对于 PostgreSQL，如果任何语句在事务期间产生服务器错误，则该事务将被标记为已中止。 这并不意味着它实际上已经回滚了——只是你几乎无法做任何事情除了将其回滚。 我假设 PDO 不会自动发出回滚，它会等待您调用“回滚”方法。

为了实现我认为你想要的，你可以使用保存点。 您可以回滚到保存点并继续事务，而不是回滚整个事务。 我将给出一个在 psql 中使用它的示例：

srh@srh@[local] =# begin;
BEGIN
srh@srh@[local] *=# insert into t values(9,6,1,true);
INSERT 0 1
srh@srh@[local] *=# savepoint xyzzy;
SAVEPOINT
srh@srh@[local] *=# insert into t values(9,6,2,true);
ERROR:  duplicate key value violates unique constraint "t_pkey"
srh@srh@[local] !=# insert into t values(10,6,2,true);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
srh@srh@[local] !=# rollback to savepoint xyzzy;
ROLLBACK
srh@srh@[local] *=# insert into t values(10,6,2,true);
INSERT 0 1
srh@srh@[local] *=# commit;
COMMIT
srh@srh@[local] =# 

因此，在这个示例中，t 的第一列是主键。 我尝试将 id 为 9 的两行插入到 t 中，并且得到了唯一性约束。 我不能只是用正确的值重做插入，因为现在任何语句都会收到“当前事务已中止...”错误。 但我可以执行“回滚到保存点”，这使我回到执行“保存点”时的状态（“xyzzy”是保存点的名称）。 然后我可以发出正确的插入命令并最终提交事务（这会提交两个插入）。

因此，在您的情况下，我怀疑您需要做的是在 UPDATE 语句之前创建一个保存点：如果它给出错误，请执行“回滚到保存点”并设置您的标志。 您需要为保存点生成唯一的名称：例如使用计数器。

我不完全确定我明白你为什么要做这一切。 您确定要在知道要回滚事务后立即停止处理吗？ 或者循环中是否还需要进行其他一些处理？

Answer 2

您应该使用

pdo_obj->beginTransaction()
pdo_obj->commit()
pdo_obj->prepare()

此外，您在第一个示例的末尾有一个随机提交。

begin
// do all your stuff
// check for errors through interface
commit OR not

pg_query($pgConnection, "ROLLBACK"); // end of tx(1)
// start new transaction after last rollback = tx(2)
pg_query($pgConnection, "UPDATE users SET last_activity = NOW() WHERE id = '$id_user'");
// commit tx(2) or don't here
// this isn't needed pg_query($pgConnection, "COMMIT");

如果您没有提交，并且需要手动调整内容，请使用另一个事务。 准备查询（如果我记得的话）是事务的一部分，因为它可能会失败。 您实际上不能只是手动获取 SQL 语句并将其转换为查询。 PDO 接口具有抽象是有原因的。 :)

https://www.php.net/pdo <-- PHP 的可靠示例/ Postgre 使用 PDO

祝你好运