My question is pretty simple but answer might be tricky.
I'm in PHP and I want to manage manually a unique ID for my objects. What is tricky is to manage atomicity. I dont want that 2 elements get the same ID.
"Elements" are grouped in "Groups". In each group I want elements ID starting from 1 and grow incrementally for each insert in that group.
My first solution is to have a "lastID" column in the table "Groups" :
CREATE TABLE groups ( id INT AUTO_INCREMENT, lastId INT )
CREATE TABLE elements ( myId INT, multiple values ...)
In order to avoid many elements with the same ID, I have to update lastId and select it in an atomic SQL Query.
After that, one retrieved, I have a unique ID that can't be picked again and I can insert my element.
My question is how to solve the bold part ? My database is MySQL with MyISAM engine so there is no transaction support.
UPDATE groups
SET lastId = lastId + 1
WHERE id = 42
SELECT lastId
FROM groups
WHERE id = 42
Is there something more atomic than these 2 requests ?
Thanks