Question 412450 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql atomic increment/read to generate unique IDs

I have a table in MySQL 8.0 with an ID and name column, e.g.:

ID  | name
----+------
7   | name1
12  | name2
13  | name3

I’d like to generate a new ID atomically and assign it to the row with name=name2. I was hoping to do something like:

UPDATE table SET id=MAX(id)+1 WHERE name='name2'

but that’s not valid. I read in the MySQL docs that perhaps I can do it with a multi-table UPDATE but it looked complicated and I’m not sure the whole statement (i.e. the part that computes the MAX and the part that updates the id field) would be atomic anyway.

I also need to retrieve the newly generated ID. Specifically I’m calling all of this from Python, so the result of whatever statement or transaction I run should include the newly generated ID so I can use it subsequently in the Python code. I don’t want to update the table and then issue a separate SELECT statement to query for the new ID.

Is there a simple way to do this in an atomic fashion, so that many such processes can run simultaneously while keeping the generated IDs unique?

Note that I don’t think auto-increment is applicable here. I don’t want the newly generated IDs on new (i.e. inserted) rows. I want the generate a new ID and apply it to an existing row.

There are several other questions (e.g. this one) about how to make the above UPDATE statement work with MAX() in it. Please note that this question is not the same at all – I need an atomic way to generate a new unique ID and return it to my code. It’s not clear whether that UPDATE statement would even be atomic in the form given by the other answers, and I also don’t think its result can be returned to the code. I simply wrote out the UPDATE since it was my first attempt at doing this atomically (e.g. in mongo db something like this is possible).

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    To solve this I ended up adding a second table with a single auto-increment ID field.

    To obtain a new ID that's guaranteed to be unique I execute INSERT INTO second_table VALUES() and then look at the value of LAST_INSERT_ID() (via cursor.lastrowid in my case since I'm using Python) to read the new ID.

    After this I perform a ROLLBACK to avoid accumulating useless rows in the second table (note that it keeps its auto-increment state after ROLLBACK but not after TRUNCATE TABLE!). I looked into it a bit and think that ROLLBACK might (somewhat counter-intuitively at first) be more expensive than committing the transaction when using InnoDB. In my case that was acceptable but for the most performant solution a COMMIT might actually be preferable.

    I think the accepted answer is a more direct solution to my question as phrased, but I'm sharing this in case it's helpful to anyone. I think this solution is more efficient in terms of locking so in highly concurrent environments it may be preferable.


  2. 0

    Use a transaction to make assigning the ID and retrieving it atomic:

    START TRANSACTION;

UPDATE table AS t1
CROSS JOIN (
    SELECT MAX(ID) AS maxid
    FROM table
) AS t2
SET t1.id = maxid+1
WHERE t1.name = 'name2';

SELECT id
FROM table
WHERE t1.name = 'name2';

COMMIT;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search