1

I read some threads here about PDO::lastInsertId() and its safety. It returns last inserted ID from current connection (so it's safe for multiuser app while there is only one connection per user/script run).

I'm just wondering if there is a possibility to get invalid ID if there is only one DB connection per one long script (lots of SQL requests) in multicore server system? The question is more likely to be theoretical.

I think PHP script run is linear but maybe I'm wrong.

Til
  • 5,150
  • 13
  • 26
  • 34
TSGR
  • 35
  • 2
  • 1
    PHP is not multithreaded. What do you mean? Are you facing any problem? – Dharman Jul 14 '19 at 10:27
  • @Dharman This is more about the supported databases in PDO than about PHP. The question talks about multi**core** and you talk about multi**threaded**, those are not the same. In almost all case the OS will abstract the cores for any process running. It basically doesn't matter if the hardware has one core or multiple cores, the OS will handle the processes the same. So, the amount of cores is irrelevant to PDO. – KIKO Software Jul 14 '19 at 10:44
  • @Dharman I'm updating my PDO wrapper class and I need to use lastInsertId. At first I was wondering wheather it is safe with multiuser app, and I found an answer here, that says, it is safe per connection. My app creates one connection per user (for many requests) so I was just curious about any possible problems (is it safe with many requests per one connection?). If one script run is linear and not somehow magically multithreaded, it tends to be safe. – TSGR Jul 14 '19 at 10:55

1 Answers1

0

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.)

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I'm confused. I've heard about multi-threaded PHP but I had always assumed you needed some unofficial third-party library written in C, such as [pthreads](https://github.com/krakjoe/pthreads). Is it physically possible with regular PHP to have two PHP proccesses sharing the same database session simultaneously (vs reusing a persistent connection started by a previous but already dead process)? – Álvaro González Jul 14 '19 at 15:26
  • Multiple threads, please see my edited answer. Share a connection among multiple threads at your peril. Persistent connections, see my edited answer. When you wrote *two PHP processes* I believe you meant *two PHP threads.* – O. Jones Jul 15 '19 at 10:45