2

MySQL has LAST_INSERT_ID(); function which gets the last insert.

But this is not safe: If i run some query then get LAST_INSERT_ID() and between the two another query is executed I can get the wrong id.

This can happen in multiple threads using the same connection, or using pconnect (persistend connection for multiple users)

Is there safe method for getting the ID that i want 100% ?

Thanks

Mehravish Temkar
  • 4,275
  • 3
  • 25
  • 44
  • 1
    Answer: Unless you have multiple threads from the same connection doing the inserts, you should not have anything to worry about. – Tim Biegeleisen Oct 05 '18 at 05:17
  • Yes this is my question, i have multiple process with the same connection, in the duplicate there is no answer for multiple threads –  Oct 05 '18 at 05:17
  • Done, thanks you can edit if its not good –  Oct 05 '18 at 05:21
  • I don't have an answer for you. I was thinking that obtaining a write lock might work, but a write lock only would block other sessions, not multiple threads sharing the same connection. Maybe you should try to avoid this use case from happening. – Tim Biegeleisen Oct 05 '18 at 05:31
  • You need to implement mutual exclusion in your application to prevent them from interfering. Or each thread should use its own connection. – Barmar Oct 05 '18 at 05:31
  • There's nothing built into MySQL to solve this, it has to be solved in the application code. – Barmar Oct 05 '18 at 05:32
  • This is bad, each mysql user should implement own mutual exlusion why they dont do it to avoid each mysql users to write own complex algorithms? –  Oct 05 '18 at 05:33
  • And how will I solve it with application code ? How will I know if the id is correct? –  Oct 05 '18 at 05:33

2 Answers2

1

Store procedure may help in the case: 

Create table test (id int AUTO_INCREMENT primary key, name varchar(50) )

Store procedure as:

delimiter $$
DROP PROCEDURE IF EXISTS InserData$$
CREATE PROCEDURE InserData(IN _Name VARCHAR(50))
BEGIN
    START TRANSACTION;
    INSERT INTO test(name) VALUES (_Name);
    SELECT LAST_INSERT_ID() AS InsertID;
    COMMIT;
END

Call the stored procedure using

 CALL InserData('TESTER')

Give it a try as we have transaction statement but it can't ensure the value in multi threaded environment.

The link Mysql thread safety of last_insert_id explain it will work based on per connection model.

balakrishnan
  • 383
  • 4
  • 12
-2

Is using SELECT Max(ID) FROM table safer than using SELECT last_insert_id(), where they run as 2 separate queries? According to your question the table must have a primary key. So you can get last record from MAX(ID)

LDS
  • 354
  • 3
  • 9
  • This doesnt solve the issue. I insert row with id = 5, and before i get select max(id) someone insert id=6 and I get the row with id=6 which will be incorrect –  Oct 05 '18 at 06:54
  • 1
    You should not answer with a question – Dan M. CISSOKHO Apr 18 '19 at 21:28