使用事务时是否可靠 PDO lastInsertId() [英] Reliable or not PDO lastInsertId() when using transactions

问题描述

我使用 PDO 事务

try {
    DB::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    DB::$db->beginTransaction();

    $db->prepare( insert query );
    $db->execute();

    $last_insert_id = $db->lastInsertId();

    ...
    ...

此脚本需要多个并发请求.

Multiple concurrent requests are expected on this script.

问题:lastInsertId() 是否可能为实际插入行的用户返回错误值?

Question: is it possible that lastInsertId() return incorrect value for the user, who actually inserted the row?

(不正确的值"是指:其他用户插入的 ID).

(by "incorrect value" i mean: id that is inserted by some other user).

推荐答案

你很安全.您获得的 ID 将是正确的.

You're safe. The ID you get will be the correct one.

PDO 的 lastInsertId(以及 mysql 的 last_insert_id，在这种情况下，您的 PDO 将调用委托给它)基于 每个连接提供最后一个自动生成的 ID.

PDO's lastInsertId (and mysql's last_insert_id to which your PDO delegates the call in this case) gives the last autogenerated ID on a per-connection basis.

来自 mysql 的文档:

生成的 ID 以每个连接为基础在服务器中维护.这意味着函数返回给给定客户端的值是为该客户端影响 AUTO_INCREMENT 列的最新语句生成的第一个 AUTO_INCREMENT 值.此值不会受到其他客户端的影响，即使它们生成自己的 AUTO_INCREMENT 值.此行为可确保每个客户端都可以检索自己的 ID，而无需关心其他客户端的活动，也无需锁定或事务.

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

并发连接不会损害返回 id 的完整性.正如您在评论中提到的，交易与此无关.只是不要忘记提交！

Concurrent connections will not compromise the integrity of the returned id. And as you mention in your comment, transactions have no bearing on this. Just don't forget to commit!

我还要提一下，如果您在同一个连接上运行多个语句，并且您的 execute 方法抛出未正确处理的异常，那么 lastInsertId 可能会返回最后一次成功插入的 id 的可能性.联系.但它永远无法从其他用户的查询中返回 ID.

I'll also mention there is the unlikely possibility, if you run multiple statements on the same connection, and if your execute method throws an exception which isnt handled correctly, that lastInsertId could return the id of the last successful insert on that connection. But it can never return an ID from another user's query.

这篇关于使用事务时是否可靠 PDO lastInsertId()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！