MySQL Bugs: #103136: why can’t you provide table level innodb_autoinc_lock

Bug #103136	why can’t you provide table level innodb_autoinc_lock_mode
Submitted:	28 Mar 2021 21:31	Modified:	29 Apr 2021 12:40
Reporter:	Eshwar More	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I might want some table to have incremental id to be the order of insertion of row. may be to use that incremental id to keep track of how many rows i have synced. on the other hand i might not care about incremental id to be in the order of insertion in a table and want concurrent transactions. so this variable should be table specific. applying it at server level is stupidity.

How to repeat:
it’s a feature request. a basic common sense feature 

Suggested fix:
make innodb_autoinc_lock_mode configurable at table level

Hi Mr. More,

Thank you for your feature request.

However, in order to process your feature request, that feature has to be defined in far greater detail.

Right now, as our Reference Manual clearly points out, what you are asking is not feasible with InnoDB SE, because of the high concurrency, many different isolation levels and multi-row inserts under the conditions of record locks. InnoDB SE attempts to reserver the auto-increment value, or a set of values, but due to record locks of the various types, in the conditions of concurrent operations, some values are not accessible or available. Also, how would this feature work when InnoDB SE does not operate under table locks, page locks, but instead under the conditions of record locks and MVCC.

Thanks in advance for your feedback.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".