CODEWITHSUNDEEP
mysqlid-generation
Christof Aenderl
Christof Aenderl

Reputation: 4512

MySQL 5 atomic id generator

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.

Upvotes: 3

Views: 5263

Answers (3)

Roberto Novakosky
Roberto Novakosky

Reputation: 371

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.

Upvotes: 0

Bohemian
Bohemian

Reputation: 425083

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

Upvotes: 1

Bohemian
Bohemian

Reputation: 425083

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)

Upvotes: 1

Related Questions