We would like to have the following capability, where users can make modifications to a specific table (create or update rows), while users can request all the changes made after a specific change, receives the changes in order, so last change can be used for a subsequent call.
Basically we want to mimic a MessageQueue's change notification but instead of message-push, via a pull-based service endpoint.
We also have a technical requirement, to use Azure SQL for DB, and a webserver to handle user requests (REST API). This is how we defined the problem so far:
- Each row has a ChangeID property, which is globally monotonically increasing, whenever a change is made for the row (insert, update)
- To ensure ever-increasing property, we would use a SEQUENCE to generate ChangeIDs
- Having no gaps in used ChangeIDs is NOT a requirement
- Each Update and Insert happens in transactions, along with changing the ChangeID property
The general usage would be read-heavy (90% read, 10% write). We'd like to have a solution which is scalable and has good performance: a read/write concurrency model as parallel as possible, while we also expect rollbacks for transactions.
Is it ok to use SEQUENCE here? SEQUENCEs are not part of transactions
The worst problem, that we want to avoid is that a client does not get notified about a change. E.g.:
- T0: Client GET Changes (from: 5):
- Recieves ChangeIDs: 6, 8, 12
- Client updates its ChangeCursor to 12
- T1: Server finishes updating, table is updated with ChangeIDs: 9, 15, 16
- T2: Client GET Changes (from: 12):
- Receives ChangeIDs: 15, 16
- Error, client will never be notified of ChangeID 9