confirmation on multithreading and insert_id()

[eluser]Nick_2010[/eluser]
Dear CI/PHP experts - I have read a number of posts in the forum and did some code browsing, following is what I have ascertained on the question of - "How does CI handle concurrent requests and if insert_id() could lead to wrong results, unless queried *immediately* (whatever that means) "

1. PHP and by that extension CI is single threaded. So no matter how many cores the CPU has and no matter how many threads you configure your apache instance for - One and only one execution context will be active at any given time in CI. In other words one request processing at a given time (assuming running one instance of CI, not running like an NGINX). - TRUE/FALSE?

2. CI does not use (or need) any database connection pooling. It creates a connection and holds on to it unless it times out. It does not explicitly closes a database connection even if pconnect is false (looked at DB_driver.php) - TRUE/FALSE?

3. So at any given time only one request processing will be active and will be tied to the one database connection that CI uses. If there is a simulatenous request then it will have to wait till the previous request is finished - TRUE/FALSE?

4. So assuming MySQL - only one "client" is active at any given time. TRUE/FALSE?

5. So when that HTTP request results in an insert into DB into a table with auto-increment and we call $this->db->insert_id() then we will get the right ID everytime. TRUE/FALSE?

6. Now for the sake of argument if only my CI application is using the database and if we add a sleep() function call for say 15 seconds after insert() but before insert_id() code then even in a highly concurrent environement (say 1 request per second) we will still have the right ID returned. The new 15 requests that were called while our lone thread was sleeping were all blocked. - TRUE/FALSE?

Thanks in advance for looking at it. This will really help me understanding how it works.

[eluser]pickupman[/eluser]
In MySQL, CI supports transactions. The table needs to be InnoDB and not MyISAM. You can read more in the user guide ([url="http://ellislab.com/codeigniter/user-guide/database/transactions.html"]Transactions[/url]).

1. If you have shell access to server use the apache benchmark test (ab). You can do load testing to see the performnce you will have with php/ci. My laptop will do about 34 requests/sec. I am sure a quad core will handle much more than that.

2. CI will close DB connection @ line 1107 in DB_driver.php if set. Some shared environments will not allow persistent connections. FALSE

3. TRUE

4.

5. FALSE (partially). On a high volume site, if a insert were processed at the exact same time, it may be possible to return the wrong value.

6. insert_id() could be called at any time I believe. It simply returns the ID of the last inserted record. Who knows when it was processed unless you are using transactions. Transactions are similar to the situation you are describing. It's basically the DB saying to the line of queries to wait until I have finished up this query (insert) before I start another one. If I can't complete the query completely, we will pretend it never happened.

[eluser]WanWizard[/eluser]
@pickupman, I don't fully agree with you.

1. PHP itself is, but your webserver isn't. So at any given time, multiple PHP processes, and therefore multiple instances of CI, can be active.
2. FALSE.
3. FALSE. There can be as many connections active in parallel as you have configured for your database engine. Two PHP processes access the database concurrently.
4. is therefore FALSE.
5. TRUE, but not because of your reasoning. insert id uniqueness is guaranteed by the database itself, by means of an internal locking mechanism on the ID counter.
If two processes insert a record at exactly the same time, the database engine will ensure they both get a unique insert id.
6.TRUE. But again not because of your reasoning. When you run an INSERT query, the result, which includes the insert id, is returned to the calling process (in this case the PHP database driver). It sits there until you run another INSERT query, or until you close the connection. MySQL also keeps track of the last insert id server side, per connection, so you can use LAST_INSERT_ID() in your queries.

Connection pooling just means that a pool of already established connections is available. When your process connects, one of the free connections from the pool is returned. Your process will be the sole user of that connection, when your process is finished the connection is returned to the pool.

[eluser]Nick_2010[/eluser]
@wanwizard - I think you nailed it.
(a) 1 Webserver can spawn many PHP processes
(b) 1 PHP process = 1 CI instance
© 1 CI instance = 1 DB Connection
(d) MySQL ties insert_id() to LAST_INSERT_ID
(e) MySQL gurantees LAST_INSERT_ID value to be safe on a per connection basis

So therefore taking all this together calling insert_id() is time-wise safe, in other words it doesnt matter after how much time you invoke insert_id() after insert, as long as you do not insert anything else in the database in your code before calling insert_id() you will get the predictable value.
Also it doesn't matter how many PHP/CI instances are active using the same database at the same time, inserting thousands of records, because of © and (e) above, you will always get the correct value.

Thanks all

[eluser]Rolly1971[/eluser]
@wanwizard, @Nick_2010,

could not have said it any better myself. wanwizards description is great , nailed it square right on the head. Nick_2010's description is the same thing wanwizard said however his is way more simplistically written.