Database "insert_id()" probable wrong result issue...

[eluser]fouadami[/eluser]
While MySQL documents always mention that LAST_INSERT_ID() which is used in db->insert_id() method of Codeigniter is secure even without using transactions and lock/unlock statements, they always say the results are reliable for one user only, but what if I use it on the web and too many clients connect to MySQL as one specific user? Does it still work properly even if threading halt frequency is high?

Thanks a lot

[eluser]drewbee[/eluser]
Yes -- It is based on the last insert id of the last query ran of the current connection to the database. The only time I could potentially forsee issues is if you close the query connection, then open a new one. Not sure why you would do this though.

[eluser]Phil Sturgeon[/eluser]
What if one person manages to insert a query in the 0.0034 seconds between the insert and the call of the insert_id()? Hopefully we can presume that process time is the same for every request and therefore its impossible... right?

[eluser]fouadami[/eluser]
Well that's what I mean, while MySQL doesn't lock the table and both users are using the same user name to connect to the database, if we assume the clock time of the CPU and the OS queue and the lack of transaction, is it possible to get the wrong result when we call db->insert_id()?

[eluser]drewbee[/eluser]
I am sorry. I do not think I am explaining this good enough.

The connection opened up is relative to the current user. Each user holds on there own personal connection to the database. So when insert_id() is returned, it is relative to that user only. User1 cannot get User2's insert_id() without bridging connections (which is unlikely). You could be more worried if you use mysql_pconnect, where users share connections though. I don't know how it affects this part of things.

[eluser]fouadami[/eluser]
Thnx drewbee, I did understand what you had said before, I was just curious to know if User1 and User2 are different in MySQL point of view although they both use the same user/pass to connect to the database. I need to close each connection as soon as the results are returned to reduce the amount of open connection so I need to work a little bit more on the issue... Thanks a lot again

[eluser]Michael Wales[/eluser]
Technically, yes it's possible. Realistically, probably not.

I definitely wouldn't go about closing connections as soon as you are done returning results though. If you pconnect disabled the connection will be automatically closed as soon as script execution has been completed.

[eluser]fouadami[/eluser]
I was just a little bit worry about persistent connection excess in open connections, but I think I'd better choose pconnection... ;-)