Avoid incrementing primary key if insert is not successful?-CodePudding

I have a 'locations' table as follows:

Using an API, if I send a valid record, the table will start normally with index 1. If I send something that won't be accepted because of the table's constraints (invalid foreign key, empty field, etc), naturally the table doesn't take it, and nothing is inserted. However, if I send another valid record, instead of id 2, it will be indexed with 3, even tho the table only has one record and nothing has been deleted. Is there a simple way to avoid this?

Thanks!

CodePudding user response：

Consider this scenario: you start a transaction and do an INSERT. The INSERT succeeds, allocating id 2. But a subsequent query in the same transaction fails, and you have to roll back, including the successful INSERT.

In the meantime, another session also does its own insert, allocating id 3. That transaction commits. Now you have a row existing in the table with id 3, but no row with id 2.

How would you like to handle this?

a) Block all concurrent inserts until you COMMIT a transaction. This is necessary to ensure only consecutive values are used.

b) Implement auto-increment as a growing list of unused id values. How much space should the list be allowed to grow to?

The fact is that it's normal for auto-increment to allow "gaps" or unused values. If the INSERT fails, or the transaction is rolled back, or you DELETE a row later, that leaves gaps in the sequence of consecutive id values.

This is a necessary compromise for the sake of efficiency. Auto-increment values must be unique, but they are not required to be consecutive. They are not row numbers. If you need to guarantee id values are consecutive, you must sacrifice other things in the system, e.g. concurrent INSERTs. And what do you do if you DELETE a row, do you expect to renumber all following rows?

Some people want to compact their id values because they're worried they will run out. I see your primary key is a BIGINT UNSIGNED.

https://mariadb.com/kb/en/bigint/ says:

The unsigned range is 0 to 18446744073709551615.

Keep in mind that if you were to start the auto-increment at 0 and increment it 1 million times per second, a BIGINT UNSIGNED will last 18,446,744,073,709 seconds, which is more than 584,542 years.