Is LAST_INSERT_ID mysql user specific?

[bjsteyn]

Just got a query i'm inserting records into a table the an auto counter primary key lets say its called ID.

After i insert a record i fetch the new unique ID created with SELECT last_insert_id() FROM mytable to build my audit trail and know what record to audit. That is my unique key.

What i want to know what if 2 users add a record at the same time by any chance, will the select last_insert_id fetch the right record id, by user.

Just need to make sure that won't cause a problem in the future even tho the chances of that happening is slim.

 

[Minty]

As far as I'm aware access doesn't have an last_insert_id() function, so I would agree that it's a MySQL specific thing.

If you use a DAO you can use the following

Private Sub Test192674914368()
    Dim lastID As Long
    With CurrentDb
        With .CreateQueryDef("", _
            "INSERT INTO tblSchoolWorkingDays " & _
                "( CALENDAR_DATE ) " & _
            "VALUES " & _
                "( [prm0] )")
            .Parameters(0) = Date
            .Execute dbFailOnError
            .Close
        End With
        lastID = .OpenRecordset("SELECT @@IDENTITY").Fields(0)
    End With
End Sub

Blatantly stolen from here https://www.access-programmers.co.UK/forums...amp;postcount=8

It's important that you use the same database object to get the ID, in the example posted above it uses CurrentDb.

 

[theDBguy]

Just got a query i'm inserting records into a table the an auto counter primary key lets say its called ID.

After i insert a record i fetch the new unique ID created with SELECT last_insert_id() FROM mytable to build my audit trail and know what record to audit. That is my unique key.

What i want to know what if 2 users add a record at the same time by any chance, will the select last_insert_id fetch the right record id, by user.

Just need to make sure that won't cause a problem in the future even tho the chances of that happening is slim.

Hi. I think it would, but it's just a guess. If MySQL is anything like Access, then fetching the last inserted record should be "connection" specific. And since each user would be using a separate connection, I am guessing they won't run into each other. Still, it's just a guess though...