> although it would seem to be so by design, however design is only as robust as
It only seems so if you assume auto-increment is used to populate the order_id and that it always increases with time. That latter assumption is quite unsafe:
* Systems could have been merged with a bulk import of old orders into this one from elsewhere (assuming order_id is a surrogate key and there is a separate order code or such that is used to identify the orders externally).
* In fact, a simple insert of several records in the same statement will not necessarily get auto-increment values in the order you expect (in practise they usually do - but the DB engines do not guarantee this, it is an accident of other factors in their design rather than a defined behaviour).
* Because of optimisations for concurrency in the way auto-increment is handled, it is possible that long-running transactions could cause ordering discrepancies. In theory at least, in practise unless you've explicitly opted out of ACID-preserving locking semantics for those transactions I suspect these protections will stop this un-ordering happening by blocking the concurrency. This sort of issue is why you occasionally see unexpected gaps in auto-increment values.
* I have seen an example where an incrementing signed-int ID was getting too close to MAXINT for comfort, and as a temporary measure ahead of changing that ID to be a longer type the increment was reset to restart at MININT and head back towards 0 from there! This was with a 16-bit integer (I'm old enough to have been around when it was common to use them to save space, where we generally default to 32-bit these days) but the same could happen to larger types.
It only seems so if you assume auto-increment is used to populate the order_id and that it always increases with time. That latter assumption is quite unsafe:
* Systems could have been merged with a bulk import of old orders into this one from elsewhere (assuming order_id is a surrogate key and there is a separate order code or such that is used to identify the orders externally).
* In fact, a simple insert of several records in the same statement will not necessarily get auto-increment values in the order you expect (in practise they usually do - but the DB engines do not guarantee this, it is an accident of other factors in their design rather than a defined behaviour).
* Because of optimisations for concurrency in the way auto-increment is handled, it is possible that long-running transactions could cause ordering discrepancies. In theory at least, in practise unless you've explicitly opted out of ACID-preserving locking semantics for those transactions I suspect these protections will stop this un-ordering happening by blocking the concurrency. This sort of issue is why you occasionally see unexpected gaps in auto-increment values.
* I have seen an example where an incrementing signed-int ID was getting too close to MAXINT for comfort, and as a temporary measure ahead of changing that ID to be a longer type the increment was reset to restart at MININT and head back towards 0 from there! This was with a 16-bit integer (I'm old enough to have been around when it was common to use them to save space, where we generally default to 32-bit these days) but the same could happen to larger types.