3 ms·

> we don’t just want a monotonically increasing sequence Be careful about treating sequences as monotonic. For transactions in progress at the same time, the o

> we don’t just want a monotonically increasing sequence Be careful about treating sequences as monotonic. For transactions in progress at the same time, the order of sequence values might not be consistent with the order of transaction commits and the logical order of serializable transactions. One example where that could cause problems is if you filter a change stream using the last seen id. For such an approach an out-of-order id would lead to missed events.

lawrjone 5y ago
Yep, this is particularly relevant when building pagination. If you use the primary key, which itself is sortable and based on a sequence, you might be surprised when you skip over rows that were yet to be committed because the IDs won't respect transaction commit order.
peterejhamilton 5y ago
Super interesting, and that makes a lot of sense! Luckily our _internal_ IDs don't rely on sequences at all, and for ordering I'd always use a field specifically for that purpose like `created_at` timestamps etc (vs inferring ordering from IDs). Best if IDs just remain references! This could have been another interesting bug though, in a way glad we hit this one instead and that it's now fixed so we don't hit this one in future!
- wongarsu 5y ago
  Chances are that your created_at timestamps reflect transaction start, not transaction commit. That would leave you open to issues as described by GP as well
- CodesInChaos 5y ago
  Timestamps have monotonicity issues as well: 1. If you create it in the application, clocks need to be synced well enough between all application servers. If you create it in the database, this shouldn't be an issue. 2. The transaction completes some time after the the timestamp was created, and that time can vary between concurrent transactions. This is a fundamental problem. The MAX + 1 approach should guarantee strict monotonicity, but might lead to scalability issues for highly contented counters.

lawrjone 5y ago

Yep, this is particularly relevant when building pagination. If you use the primary key, which itself is sortable and based on a sequence, you might be surprised when you skip over rows that were yet to be committed because the IDs won't respect transaction commit order.

peterejhamilton 5y ago

Super interesting, and that makes a lot of sense! Luckily our _internal_ IDs don't rely on sequences at all, and for ordering I'd always use a field specifically for that purpose like `created_at` timestamps etc (vs inferring ordering from IDs). Best if IDs just remain references! This could have been another interesting bug though, in a way glad we hit this one instead and that it's now fixed so we don't hit this one in future!

wongarsu 5y ago
Chances are that your created_at timestamps reflect transaction start, not transaction commit. That would leave you open to issues as described by GP as well
CodesInChaos 5y ago
Timestamps have monotonicity issues as well: 1. If you create it in the application, clocks need to be synced well enough between all application servers. If you create it in the database, this shouldn't be an issue. 2. The transaction completes some time after the the timestamp was created, and that time can vary between concurrent transactions. This is a fundamental problem. The MAX + 1 approach should guarantee strict monotonicity, but might lead to scalability issues for highly contented counters.