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?