Parallel INSERT operations possible conflicts using "$this->db->insert_id()"?

[eluser]mindprojects[/eluser]
Hy guys,
i would like to know if there could be a problem,when two different users are running parallel INSERT operations at the same time and then i'm going to get the last inserted ID of each single user using $this->db->insert_id().
Is it possible to get the wrong ID?

Thanks

[eluser]TheFuzzy0ne[/eluser]
No. The ID returned is the ID of the row you just inserted. It doesn't matter if a million rows are being inserted during that time. The ID of the row you inserted will still be the same, and that's what will be returned.

[eluser]mindprojects[/eluser]
Are you sure to avoid collisions,or i need to use transactions?

[eluser]abmcr[/eluser]
lock the table

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

ciao!

[eluser]TheFuzzy0ne[/eluser]
Yes, I'm absolutely positive. Transactions are used when you need to do several updates or inserts that depend on one another. If one query fails, the entire transaction does.

[eluser]mindprojects[/eluser]
All right,what i usually do is:

$this->db->insert(....)

$id = $this->db->insert_id();


How can you be so sure about having no conflicts between an ID mismatch when multiple users are doing this operation?There is a technical explanation about this?
Is CI locking my db?

Thanks

[eluser]TheFuzzy0ne[/eluser]
OK. Imagine this...

You drive to a car park, park your car, and then go to the ticket machine to purchase a ticket for the time your car is going to be there. How can you be sure you're not getting someone else's ticket? Quite simply, because each transaction is handled individually. The ticket is printed there and then, and is unique to you.

When the row is inserted, an ID is given to it. The ID will never change unless you change it manually.

An insert is only a single action. A transaction in database terms consists of more than one action, that depend on each other in some way.

I think I know what you're getting at, and the answer to that is also simple. When you make a connection to the database, the connection is given a unique ID by the database. That means that any connection-specific data (such as insert_id()), is specific to that particular connection ID. Once the connection is finished, the connection specific data is also done. It's a lot like a PHP session, only the session is cleared as soon as the connection is closed.

Hope this helps.

[eluser]kgill[/eluser]
I think it needs to be asked, are you really really really sure??? Heh, you've got my respect man.

[eluser]mindprojects[/eluser]
What if the connection is permanent?
You make a transaction,the database give an ID and you get it from the application with db_insert_id().
I think its the same idea of parking a car.
You park a car,and should get the ticket with the number of your place.
Someone is parking a car at the same time and take the ticket before you,getting your park number....

Is it possible? Why not?

[eluser]TheFuzzy0ne[/eluser]
There's no such thing as a "permanent" connection. Persistent, yes, but with a persistent connection db_insert_id() will still return the ID of the last row inserted via that connection ID. If you insert 10 rows, you will only get the ID of the last row that was inserted by that connection.

If another process was inserting rows at the same time, db_insert_id() would return the ID of the last row that was inserted by that connection. Database connections are isolated from one another.

Remember, db_insert_id() only returns the ID of the last row inserted with that connection, it is unaware of any other database connections. The purpose of db_insert_id() is to get the ID of a row that's just been inserted. It would be useless if, on a busy site, it started handing out insert IDs for other rows. I use it every day, and millions of other developers do too, and they don't have any problems with it.