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.
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