1

With MySQL/PDO, I believe PDO::lastInsertID() returns the ID of the last inserted object for the connection. I have my PDO object contained in a singleton object that any method can call so there's only one database connection per HTTP request to the server.

class Database {
    public $db;
    private static $dsn;
    private static $instance;

    private function __construct()
    {
        $this->db = new PDO( self::$dsn );
    }

    public function __destruct()
    {
        $this->db = null;
    }

    public static function getInstance()
    {
        if ( !isset( self::$instance ) ) {
            $object = __CLASS__;
            self::$instance = new $object;
        }

        return self::$instance;
    }
}

$database = Database::getInstance();
$handle = $database->db->prepare(); // etc etc etc

Correct me if I'm wrong but I believe each HTTP request to Apache spawns a separate PHP instance, so I don't think that singleton would be shared between those PHP instances and since there's no concurrency in the code, I think it should be happening linearly. So I don't think this case could happen:

  1. Method A inserts a row with Query A
  2. Method B inserts a row with Query B
  3. Method A asks for the last inserted ID expecting Query A's result, getting Query B's instead
  4. Method B asks for the last inserted ID expecting and getting Query B's result

Already read these, no answer found:

PDO Last Insert ID always the right one? http://php.net/manual/en/pdo.lastinsertid.php

Community
  • 1
  • 1
anjunatl
  • 1,027
  • 2
  • 11
  • 24
  • Nothing searched talks about the possible shared usage with the singleton pattern. – anjunatl Nov 01 '13 at 05:27
  • "..since there's no concurrency in the code, I think it should be happening linearly" - *why*? Especially when from before: "I don't think that singleton would be shared between those PHP instances".. Now, the trick is to realize that their *may* (or may not) be multiple processes/servers and you *may* (or may not) execute subsequent requests upon the same process. – user2864740 Nov 01 '13 at 05:28
  • Basically the calls out to the Database object would happen one after another in each http request/php instance. Definitely aiming for the database connection to be shared, just asking if there's any chance two queries could have the interaction shown above if each insert is immediately followed by a check for the last inserted ID. – anjunatl Nov 01 '13 at 05:32
  • At the *query level* it will be accurate. But as soon as PHP *ends the transaction* then the the last-inserted ID may be old (it will never be for the "other" insert, however). Transactions (e.g. a serialized transaction) is used to prevent these sort of conflict across multiple statements from different connections. – user2864740 Nov 01 '13 at 05:33
  • I think the core question may be if a singleton object is shared between http requests on a web server. If it's not then I think it'll be fine. – anjunatl Nov 01 '13 at 05:34
  • No. It is not guaranteed. That is why I quoted you - "I don't think that singleton would be shared between those PHP instances" ;-) Also, depending upon how the PHP is hosted, the singleton may last shorter than is desired (e.g. per each request) or may be "randomly" recycled. – user2864740 Nov 01 '13 at 05:34
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/40353/discussion-between-anjunatl-and-user2864740) – anjunatl Nov 01 '13 at 05:37

1 Answers1

2

If you indeed have your code checkered in a single script like in your example - getting insert id for query A after running query B - there surely would be inconsistency.

Just get your insert id the very next row after insert, and you will have not a single problem with it.

HTTP and singleton has nothing to do here.

Here is a rule of thumb for such phobias: consider if your case is unique or not. For the inconsistent autoincrement consider these premises:

  • such inconsistency surely would be a disaster
  • singleton is a popular pattern
  • HTTP is quite a popular protocol too
  • you are not a sole programmer who are using these technologies

and you can make a conclusion: as long as you cannot find any evidence on such a disaster - most likely it never existed.

Sincerely yours, col. Common Sense.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • I see what you mean about the popular pattern/disaster angle. No, the code isn't explicitly checkered like in the example. The example is the case of two http requests happening at close to the same time that both end up doing database calls. If the singleton object is shared between the http requests then the checkering would happen on the same connection. The code runs the query then immediately checks for the last inserted ID, so I believe you're correct, shouldn't have a problem with it. – anjunatl Nov 01 '13 at 05:45
  • if your question is about singleton shared between HTTP connections, it's better to ask this very one, not some related one. anyway, it is answered already. – Your Common Sense Nov 01 '13 at 05:47
  • 1
    True that. It just took asking this to realize. Appreciate your time & the input. – anjunatl Nov 01 '13 at 05:49
  • I like this latter comment. It occurs to me exactly the same way :) Yet I am tempted to patronize a bit more: for this singleton dilemma it would be great experience to just verify it. I always recommend to test your assumptions by running the code rather than asking other people. They may have no clue most of time. While running the code with your hands you will see that there is no way to share any non-scalar value between PHP instatnces and that's essential limitation. – Your Common Sense Nov 01 '13 at 05:53