Mysql – does last_insert_id() returns correct result when writes from multiple sessions

auto-incrementMySQLprimary-key

I have a table in which there is a combination of 4 columns that will act as a unique key.

Instead of using a composite primary key, I want to keep an id column as the primary key. This field is on auto_increment. The insert to the database are done through a C interface. When a row is inserted, I want to know the id it was assigned. LAST_INSERT_ID() can be used to retrieve that, but there will be multiple sessions inserting in the table. Will LAST_INSERT_ID() return the correct value in that case?

I have gone through this post: Is MySql's LAST_INSERT_ID() function guaranteed to be correct?
It says that id of first inserted row is returned when multiple rows are inserted.

When multiple sessions write to the same table but a single session writes only one row at a time, is it possible to retrieve the correct value of the id generated? Will the Isolation Level also matter?

Best Answer

Here's what the MySQL 5.5 documentation says:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

Unless you fall into the other caveats noted in the question you link to, the last_insert_id() function will return what you expect, and will not be affected by other sessions.

Related Solutions

MySQL: Why is auto_increment limited to just primary keys

Why would you want to have an auto_increment column that is not the primary key?

If you want a column to be an auto_increment, by definition, you are not storing meaningful data in that column. The only case where storing non-meaningful information makes sense is the special case that you want to have a synthetic primary key. In that case, the lack of information is a benefit because there is no risk that someone will ever come along in the future and want to change the data because some attribute of some entity changed.

Having multiple auto_increment columns in the same table seems even odder. The two columns would have the same data-- they're being generated by the same algorithm and being populated at the same time after all. I suppose you could come up with an implementation where it is possible for them to be slightly out of sync if there were enough concurrent sessions. But I can't imagine how that would ever be useful in an application.

Mysql – How are auto_increment keys handled in INSERT (SELECT * FROM…)

You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator.

If you try to insert a value of NULL or 0 or DEFAULT, or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator.

So, it's fine to INSERT INTO table1 SELECT * FROM table2 (by the way, you don't need the parentheses). This means that the id values in table2 will be copied verbatim, and table1 will not generate new values.

If you want table1 to generate new values, you can't do SELECT *. Either you use null or 0 for the id column:

INSERT INTO table1 SELECT 0, col1, col2, col3, ... FROM table2;

Or else you omit the column from both the INSERT statement's column list and the SELECT statement's select-list:

-- No id in either case:
INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3, ... FROM table2;

Before you ask, there is no syntax in SQL for "select * except for one column". You have to spell out the full list of column names you want to insert.

Best Answer

Related Solutions

MySQL: Why is auto_increment limited to just primary keys

Mysql – How are auto_increment keys handled in INSERT (SELECT * FROM…)

Related Question