How to get id after insert data

[eluser]WanWizard[/eluser]
It shouldn't happen that two processes that insert a record in the same table get the same id.

The database engine makes sure all inserts with auto_increment columns are atomic, you can't have two records with the same id (you probably have that field as primary index, so the second insert would abort with a duplicate key error anyway).

The insert_id field is a property of the connection, even if you work with pooled connections only one process is ever using a connection at any given time.

Offcourse, when you think you can outsmart the database engine and try to use your own logic to determine the insert id (like suggested above), you will have to use multiple queries, which severly increases that chance that another process inserts the record you're process thinks it's inserting. To do this safe, you would need a write lock on the table during your transaction, something to avoid at all costs if you value the performance of your application.

[eluser]juanvillegas[/eluser]
How a write lock would fix this issue? Suppose:

->write user (by user #1)
->write user (by user #2)
->insert_id request (by user #1)

[eluser]Wayne Smallman[/eluser]
[quote author="juanvillegas" date="1284256344"]Well, a better aproach would be using synchronized methods and concurrency, but i'm not sure PHO (and codeigniter) support for this.[/quote]CroNIX reminded me of the resource ID, which is essentially what I was suggesting.

I'm not quite sure how I managed to forget about that, but there you go!

[eluser]WanWizard[/eluser]
@juanvillages,

You haven't read my post.

The insert_id is a connection property. When user #1 inserts a record, the insert_id property is populated by the driver for user #1's connection, and the table for which the record was inserted. When user #2 inserts a record in the same table, the same happens, but for that connection. These are two different fields, and populated directly after the insert. In other words, it is not a single value kept per table.

When you then request the insert id, the value stored in the connection is returned. This is still the value stored after user #1's insert, and is unaffected by other inserts. It will only be overwritten when user #1 inserts another record.

You don't need a lock for this. You need a lock if you manually try to do this, using 'SELECT MAX(record_id) FROM table', adding one to the result, and then insert the record. Because between the select and the insert someone else could have inserted a record.