MySQL 5 atomic id generator

Question

My logic need to know the ID of a table before INSERT (setting the id manually while inserting). Because the logic using this ID is on more than one server, letting mysql create an id might be a good idea but the function I wrote is not atomic so calling it under high load will return some identic, not unique numbers:

CREATE FUNCTION `generate_document_log_id`() RETURNS BIGINT(16)
BEGIN
    DECLARE R_ID BIGINT(16);
    UPDATE document_log_sequence SET id = id + 1;
    SELECT id INTO R_ID FROM document_log_sequence;
    RETURN R_ID;
END

I'm using a table "document_log_sequence" with one column "id" to store the last ID and increment it for each new ID. Maybe the solution is far from this but I've no more ideas.

I forgotten to explain: There's only one database-server and n number of clients. The function above is not (kind of) thread safe.

That's kind of a bad design. You should have used a compound key of auto_increment-ed id and server_id. `PRIMARY KEY(id, server_id)` would let you use auto incremented integers, all you have to specify is server_id based on what server is doing the current INSERT operation. — Michael J.V., Jun 14 '11 at 13:42
Yes, that might be true. Maybe it's a good idea to implement some business-logic in the database that way. But and that's the key fact, I need to know the ID (primary_key) before the INSERT is done. — Christof Aenderl, Jun 16 '11 at 11:05

Bohemian · Answer 1 · 2011-06-14T22:23:48.040

1

FYI, if you had only one database server, this would give you the value of the next id to be inserted:

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_NAME = 'my_table'
AND TABLE_SCHEMA = 'my_schema';

(EDITED - I misunderstood the question)

edited Jun 14 '11 at 22:23

answered Jun 14 '11 at 13:22

Bohemian

412,405
93
575
722

The table is not using auto_increment primary-key because the ID must be generated manually: - generate ID - write the id to some documents - much later, store with the id And that from many threads and different servers. – Christof Aenderl Jun 14 '11 at 13:38

Bohemian · Accepted Answer · 2011-06-14T22:58:58.677

You could use a strategy of interleaved ids

Each server is numbered 0 to n and adds their server number to a sequence that steps by n. One implementation would be to have an AUTO_INCREMENT table for ids generation then use that ID in a function like this:

CREATE TABLE ID_CREATOR (ID INT AUTO_INCREMENT PRIMARY KEY);

Assuming there are 3 servers and this server is server number 1, then this function would give a unique value to each server:

CREATE FUNCTION generate_document_log_id() RETURNS INT
BEGIN
    DECLARE NUMBER_OF_SERVERS INT DEFAULT 3; -- Or read from some table
    DECLARE THIS_SERVER_ID INT DEFAULT 1; -- Or read from some table. Must be less than NUMBER_OF_SERVERS
    INSERT INTO ID_CREATOR VALUES (); -- This creates the next id
    RETURN LAST_INSERT_ID() * NUMBER_OF_SERVERS + THIS_SERVER_ID;  -- Unique numbers that interleave each other
END

nice idea! I think it works for me if I set the THIS_SERVER_ID from client-side: `CREATE FUNCTION generate_document_log_id(c INT) RETURNS INT BEGIN DECLARE NUMBER_OF_SERVERS INT DEFAULT 3; INSERT INTO ID_CREATOR VALUES (); RETURN LAST_INSERT_ID() * NUMBER_OF_SERVERS + c; END` — Christof Aenderl, Jun 16 '11 at 11:10

Roberto Novakosky · Answer 3 · 2013-03-28T16:27:04.463

There are some options

1) You can use one UUID, a key with 16 bytes (128 bits); this strategy works among n server-databases too:

select uuid(); (of course.. you will need a field CHAR(32) to storage your UUID in Plain Text; MySQL dont have fields os type GUIID(128 bits) for a while)

I costume to use a stored function, this is one of my versions:

BEGIN
DECLARE str_uuid varchar(32);
DECLARE cur1 CURSOR FOR select replace (o.oid, '-', '') from (select lower(UUID()) as oid) as o;
open cur1;
fetch cur1 into str_uuid;
close cur1;
return str_uuid;
END

2) You can use INT or BIGINTs but this strategy only works on the same database-server:

Consider a table with the fields my_last_id, dt_hr_id (my_last_id is AUTOINCREMENT) Starts one isolated connection and so do

INSERT INTO YourTable (dt_hr_id) values (NOW());
SELECT LAS_INSERT_ID(); (SELECT in this case always will works because happens inside owner connection)

Closes your isolated connection;

This is possible to do inside Stored Procedures/Stored Functions but you need to know that MySQL doesn't support multiple transactions in the same connection, so you need to do it inside an isolated connection.

MySQL 5 atomic id generator

3 Answers3