0

I was expecting to use AUTO_INCREMENT index on MySQL database but found this answer: https://dba.stackexchange.com/a/306810/103306

A word of warning, here. The values generated in auto-increment columns guarantee to be unique, nothing more. These values do not guarantee to be contiguous (running serially, one after the other) or even always-increasing. You should not rely on these values in any way other than they are unique from one another. This kind of begs the question "What are you're hoping to achieve with this activity?"

I'm moving some things to PostgreSQL, so checking what would an option there too. Found serial type: https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

I understand there may be gaps in values and it may not be exact 1,2,3,4...n but i don't care about gaps. All i need to know is - which row was inserted 1st, which was after.

On PostgreSQL there is another popular answer which suggest to use "your own data type instead serial" instead of serial, why? It links to docs about serial type which has no such suggestion.

So the question is - how to make auto increment column which would identify inserts order in MySQL and PostgreSQL?

Using AWS RDS Aurora Serverless V2 Mysql v8.0.23 and PostgreSQL v14.3

6
  • Avoid parallel inserts (table lock, insertion timestamp).
    – Akina
    Jul 10, 2022 at 6:37
  • @Akina Avoiding the issue is not resolving the issue. Insertion timestamp do give hope, but not confidence and app running on hope is not trustworthy. If database has internal mechanism to resolve the issue, i want to know about such and use it. Jul 10, 2022 at 6:46
  • 1
    The relative rows posession in the table is a term which not exists in relational database. When you need in sefinite ordering then you use ORDER BY. When you need in relative posession then you create/store a data which sets this before-after posession, and insertion timestamp is the most obvious criteria. Autoincrement aim is uniqueness only, and it solves.
    – Akina
    Jul 10, 2022 at 8:13
  • @Akina so what you are saying auto increment on SQL and serial index on postgresql will not provide confidence of order? What would? Transactional insert with retry mechanism? Jul 10, 2022 at 9:39
  • 1
    Is replication involved? That is one situation where the ids may show up out of oreder.
    – Rick James
    Jul 10, 2022 at 17:47

2 Answers 2

3

In the presence of two or more concurrent sessions the only value that reflects the insert order is the corresponding timestamp. Even if there's only ever one session doing the insert, a sequence (auto-increment, serial, whatever the name) is simply a proxy to the actual order determinant, which is still the insert timestamp. So, use the timestamp.

4
  • But, what if two clients have the same timestamp?
    – Rick James
    Jul 10, 2022 at 17:49
  • Check Rick's post in this same thread. dba.stackexchange.com/a/314241/103306 For some reason I was expecting from RDS something what is impossible, just like on any other multi-writer database. Either IDs must come from single threaded single authority which makes IDs, or it will at best be eventually consistent. Because of that I will use timestamps, just not in seconds but in milliseconds. While seconds often overlap and ms will have way less overlaps. True consistency would introduce lag & complexity more then resolves in my case. Thanks for both of you :) Jul 10, 2022 at 18:18
  • 1
    @LukasLiesis: a timestamp has a resolution of microseconds.
    – user1822
    Jul 10, 2022 at 19:01
  • en.wikipedia.org/wiki/Snowflake_ID
    – mustaccio
    Jul 10, 2022 at 21:12
1

All INSERTs must happen on a single device. That device must issue the sequence numbers.

Or at least have a single device "issuing ids". Then hand the ids to those who need to do the Inserts, etc.

In other words, it is impossible to have ordering without having a single source for ids. That single source must use only a single thread (or have sufficient locking to simulate such) for doling out ids.

Note that when getting an id, you must not proceed to do other tasks before COMMITting the transaction. Get the id in a transaction unto itself. If you must later "rollback", then you must accept that you will have a gap in the ids.

Sure, there is such a thing as "eventual consistency", such as NDB's way have handling world-wide consistency. But that won't work for your task unless you are willing to let all the servers check in and not complain.

Having all clients sync their clocks with NNTS does not work because of network lag. Again, you can do "eventual consistency", but can't trust the results immediately

1
  • that's good point, i actually do have in one instance global RDS with multi-writers, which of course can support only eventual consistency. I guess will just save timestamps in ms and work around it. Jul 10, 2022 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.