java - Strictly auto-increment value in MySQL -

i have create mysql innodb table using strictly sequential id each element in table (row). there cannot gap in ids - each element has have different id , have sequentially assigned. concurrent users create data on table.

i have experienced mysql "auto-increment" behaviour if transaction fails, pk number not used, leaving gap. have read online complicated solutions did not convince me , other dont address problem (emulate auto-increment in mysql/innodb, setting manual increment value on synchronized mysql servers)

• i want maximise writing concurrency. cant afford having users writing on table , waiting long times.
• i might need shard table... still keeping id count.
• the sequence of elements in table not important, ids have sequential (ie, if element created before not need have lower id, gaps between ids not allowed).

the solution can think of use additional counter table keep count. create element in table empty "id" (not pk) , lock counter table, number, write on element, increase number, unlock table. think work fine has obvious bottle neck: during time of locking nobody able write id. also, single point of failure if node holding table not available. create "master-master"? replication not sure if way take risk of using out-of-date id counter (i have never used replication).

thanks.

i sorry this, allowing high concurrency achieve high performance , at same time asking strictly monotone sequence conflicting requirements.

either have single point of control/failure issues ids , makes sure there neither duplicates nor 1 skipped, or have accept chance of 1 or both of these situations.

as have stated, there attempts circumvent kind of problem, in end find need make tradeoff between speed , correctness, because allow concurrency can run split-brain situations or race-conditions.

maybe strictly monotone sequence ok each of possibly many servers/databases/tables?