mysql sequence primary-key innodb auto-increment

MySQL auto_increment innodb_autoinc_lock_mode = 2 but still populating in sequence sometimes

I have the MySQL my.cnf file settings of innodb_autoinc_lock_mode = 2 but looking at a table, the primary key is sometimes not following that rule. I mostly have:

etc... which is fine.

But in some records I have:

etc...

Should the increment by 2 always be followed or is it more of a suggestion?

We have a cluster environment, which is why he have the increment by 2 set. Trying to avoid possible key collisions.

We are needing to follow some guidelines on invoicing and prove a gap-less sequence in invoice numbering. This is causing a problem with the sequence being out of whack, since we are using the primary key id as the invoice number.

is setting innodb_autoinc_lock_mode = 1 a bad idea in a clustered environment?

Looking for the best suggestion on how to achieve the gap-less invoice numbering.

Solution

You are asking too much from AUTO_INCREMENT. It guarantees a unique number. Period; Full stop.

auto_increment_increment being greater than 1 is the technique first used by "Master-Master" replication to prevent dups. Later various "cluster" solutions took the same tack. Because it works.

I noticed the occasional odd number in your list. One Master is doing even numbers, the other is doing odd numbers, but inserting less frequently. Any attempt to avoid the gaps would significantly slow down the processing. For 99.9% of users, this is more important than worrying about the gaps.

I'm not through lecturing. There are several cases where gaps will occur. Obviously, a DELETE will leave a gap. But these will sometimes leave new gaps in their wake: INSERT IGNORE, IODKU, REPLACE, ROLLBACK. Similarly, you cannot trust that the values will be monotonically increasing.

innodb_autoinc_lock_mode might help in some cases but not others. It was added when the developers discovered a slightly faster way to do AI, but again at the expense of predictability of values.

OK, you want consecutive invoice numbers thet will never be deleted, etc. Your best bet is to have a 'service' (API) that gets the "next" invoice number. Think of a table with one row of one column. And write a Stored Procedure that increments that number and returns it to the client. Then the client uses that number wherever it needs an invoice number.

You can make the numbers consecutive based on the time the invoice number is requested. It may be quite difficult to make them consecutive based on when the invoice is inserted. As an "invoice", you should probably ever delete them, but instead have some kind of "deleted" flag to handle errors, cancellation, etc.