How Safe Is PHP Pdo Function: Lastinsertid

How safe is Php PDO function: lastInsertId?

Perfectly safe. There is no race condition. It only returns the last inserted Id from the pdo object that made the insert.

Reliable or not PDO lastInsertId() when using transactions

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

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.

From mysql's documentation:

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.

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!

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.

Is it safe to get ID through lastInsertId()?

Yes it is safe. It returns the last insert id for the connection.

Is PDO::lastInsertId() in multithread single connection safe?

PDO itself is not thread safe. You must provide your own thread safety if you use PDO connections from a threaded application.

The best, and in my opinion the only maintainable, way to do this is to make your connections thread-private.

If you try to use one connection from more than one thread, your MySQL server will probably throw Packet Out of Order errors.

The Last Insert ID functionality ensures multiple connections to MySQL get their own ID values even if multiple connections do insert operations to the same table.

For a typical php web application, using a multicore server allows it to handle more web-browser requests. A multicore server doesn’t make the php programs multithreaded. Each php program, to handle each web request, allocates is own PDO connections. As you put it, each php script run is “linear”. The multiple cores allow multiple scripts to run at the same time, but independently.

Last Insert ID is designed to be safe for that scenario.

Under some circumstances a php program may leave the MySQL connection open when it's done so another php program may use it. This is is called a persistent connection or connection pooling. It helps performance when a web site has many users connecting to it. The generic term for a reusable connection is "serially reusable resource.*

Some php programs may use threads. In this case the program must avoid allowing more than one thread to use the same connection at the same time, or get the dreaded Packet Out of Order errors.

(Virtually all machines have multiple cores.)

How does PDO's LastInsertId actually work?

The exact implementation depends on the driver. I can only describe how it works for MySQL because that's what I'm familiar with.

As we described in the answers to How does MySqlCommand.LastInsertedId work? question:

If MySQL successfully executes a query requested by a client, then MySQL sends an OK_Packet as a response.

In the payload of the OK_Packet MySQL includes the last inserted id (see documentation linked above):

Type        | Name           | Description
------------|----------------|-----------------------------------
int<1>      | header         | [00] or [fe] the OK packet header 
int<lenenc> | affected_rows  | affected rows 
int<lenenc> | last_insert_id | last insert-id
...

On the server no select last_insert_id() is executed to populate this value into the OK_packet. The driver retrieves the last inserted id from the packet and PDO in turn retrieves the value from the driver.

PHP PDO lastInsertId() function confusion

It will just give you the insert ID from the last insert prior to making the call which generates an auto-increment value. It could be that each insert will generate one, but it will only be the last one executed prior to this call.

Is PDO::lastInsertId reliable with very rapid inserts?

yes, of course, no worries about that, but you've to make sure to ask the lastInsertId just after the insert query. No other query should be executed on that connection in the meantime, each PHP process must have a separate connection.

Also if you think that a table is going to have hundreds or thousands of insert per second, consider to not use indexes or use the minimum amount of indexes. In case of MySQL favour MyISAM tables.

PDO Last Insert ID always the right one?

No, this situation is impossible.
Method $db->lastInsertId() returns last inserted id for this DB conection. In other page will be another connection and another last inserted id.

---

Related Topics