c - MySQL atomic sequence number generator -

c - MySQL atomic sequence number generator -

my programme require generate unique transaction id through mysql due multiple machine environment.

i using next mysql function, google review not atomic think is.

delimiter $$ create definer=`` function `getnexttxid`(dummy int) returns int(11) deterministic begin declare txid bigint(20); set txid = (select next_txid txid_seq limit 1 update); if txid > 15000000 set txid = 0; end if; update txid_seq set next_txid = txid + 500 limit 1; homecoming txid; end

i using last_insert_id, new requirement reset after 15m number imposed. cannot reproduce race status 2 of 100 processes same transaction number (in batch of 500, if application used 500, again).

question:

does function atomic any other way of doing correctly?

table : myisam storage engine : myisam auto commit : true

edit: using mysql c api. in advance apply.

it's not atomic, because you're reading , writing separately. should same operation atomically, while still returning value last_insert_id():

update txid_seq set next_txid = last_insert_id((next_txid * (next_txid <= 15000000)) + 500) limit 1;

mysql c