php - How to select unique value randomly from a table in heavy load server -

i have single-column mysql database table ids (id integer primary key autoincrement) store pre-generated unique ids in ascending order. in order random id table use query:

select id ids order rand() limit 1; 

and wondering how ensure id got never used again. see 2 options. 1 delete id table , other add column tracking use of id:

delete ids id=?; //where id 1 got previous query 

or

select id ids used=0 order rand() limit 1;  update ids set used=1 id=?; //where used new column 0 default value 

there slight problem both of these. if server load heavy 2 queries random id might return same id before gets removed list (or disabled used column).

would transaction help?

wrapping select , update in transaction work. if want avoid transaction race condition between selecting item , marking unusable, can run update first. you'll need way each of processes identify owner of row between claiming , deletion. example, assume ids schema is

• id (integer)
• owner (string)

have each process pick uuid (or else suitably unique) , run following:

1. update ids set owner = $process_id owner null order rand() limit 1
2. select id ids owner = $process_id
3. delete ids id = $selected_id (or otherwise mark used)

step 1 atomically claims row process no other process can claim it. step 2 pulls out claimed id. step 3 removes id available set good. if step 3 doesn't delete row, marks used, make sure clear owner process won't select again later.