Description:
What puzzles me is that not once is a "step" or "offset" for auto_increment mentioned. In one particular case, such as sharding, it is necessary to increment the pri key using "steps" ... such as 2, 4, etc, based on the number of shards that are being implemented. I could see this being a great benefit, but yet, despite all these years it's been done through work arounds. In order for developers to have an easier task of inserting data to a sharded environment it would be beneficial to have. In order to prevent errors and reduplicated data it would benefit the DBA as well. Instead we're resorted - as it seems - to using triggers, which aren't "re-useable" as in the case of stored procs or functions. This wouldn't be such a limitation if functions could call dynamic SQL but once again we're stuck with limited functionality and maximum room for error. It could be prevented with the simple feature of having a - non-global - auto_increment_offset value available on a per table basis... /end rant/
How to repeat:
setup 2 servers with identical tables with auto_increment on pri key.
insert data in alternating fashion into the tables.
Check for duplicate keys (all should be duplicated)
Suggested fix:
implement a per-table auto_increment_offset or auto_increment_step variable of sorts. This way auto-increments across shards can be handled simply and elegantly without changes to developers code or style and without the redundant error prone method of writing/copying triggers ... less overhead as well...
© 2024, Oracle Corporation and/or its affiliates