Is LAST_INSERT_ID mysql user specific? (1 Viewer)

bjsteyn

Registered User.
Local time
Today, 13:52
Joined
May 15, 2008
Messages
113
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

AWF VIP
Local time
Today, 11:52
Joined
Jul 26, 2013
Messages
10,354
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
Code:
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

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,357
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...
 

Users who are viewing this thread

Top Bottom